{"id":4220,"date":"2014-12-18T04:02:00","date_gmt":"2014-12-18T03:02:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/"},"modified":"2014-12-18T04:02:00","modified_gmt":"2014-12-18T03:02:00","slug":"how-to-store-sql-server-data-files-in-azure-storage","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/","title":{"rendered":"How to store SQL Server data files in Azure Storage"},"content":{"rendered":"

A new functionality, introduced with SQL Server 2014, enables to store data files from a SQL Server database in Windows Azure Storage. In this posting, I will show how it works.<\/p>\n

 <\/p>\n

\"General.png\"<\/a><\/p>\n

Accessing Azure Storage<\/h3>\n

The Azure Storage account is named \u201cdbiservices\u201d, and is composed of the \u201cdatafiles\u201d container. This container does not contain blobs yet.
\nIf you are not familiar with the Windows Azure Storage terms, you should read this Introduction to Microsoft Azure Storage<\/a>.
\nIn my example, I want to grant to share an access to my \u201cdatafiles\u201d container without having to expose my account key. I teherfore need to generate a Shared Access Signature<\/a>.<\/p>\n

Shared Access Signature Generation<\/h4>\n

For this part, I will use a Third Party tool called Azure Storage Explorer<\/a>.
\nAs soon as you have installed and launched this software, you must register you Storage Account:<\/p>\n

\"Register-Azure-Storage-Account.png\"<\/a><\/p>\n

You must enter your Storage account name and your Storage account key. The key has been erased voluntary in this example.<\/p>\n

As a Best Practice, I advise to enter your Secondary Access Key and not your Primary Access Key. Indeed, the Secondary Access Key is commonly used as a temporary key, and can be regenerated if necessary.<\/p>\n

To generate my Shared Access Signature, I have to edit the Security of my container:<\/p>\n

\"Edit-Security.png\"<\/a><\/p>\n

I select the permissions and the duration linked to my Shared Access Signature:<\/p>\n

\"Generate-Signature.png\"<\/a><\/p>\n

I generate a Shared Access Signature available one week with all permissions. The Shared Access Signature generated is an URI related to the container. I voluntary deleted partially the URI.
\nYou need to copy the URI from \u201csv=\u201d to the end.<\/p>\n

Credential Creation<\/h4>\n

I need to create a new Credential in SQL Server Management Studio:<\/p>\n

\"New-Credential.png\"<\/a><\/p>\n

\"Create-Credential.png\"<\/a><\/p>\n

The name of my Credential is the URI location of my container, and the Password is the Shared Access Signature previously created.<\/p>\n

Creating a database with Data Files in Azure Storage<\/h3>\n

I will perform two examples: first I will create a new database directly in Azure Storage, then I will migrate an existing database to Azure Storage.<\/p>\n

Hybrid database creation<\/h4>\n

I execute a script to create a database with its data files in Azure Storage:<\/p>\n

\"Create-Database-hybrid.png\"<\/a><\/p>\n

If we refresh the \u201cdatafiles\u201d container in Azure Storage explorer, we can see the datafiles previously created:<\/p>\n

\"Explore-Datafiles.png\"<\/a><\/p>\n

On-premise database creation<\/h4>\n

Now, I execute a script to create an on-premise database:<\/p>\n

\"Create-database-onpremise.png\"<\/a><\/p>\n

Then, I take the database offline:<\/p>\n

\"Take-Offline-Database.png\"<\/a><\/p>\n

I upload the data files in Azure Storage using Azure Storage Explorer tool:<\/p>\n

\"Upload-Datafiles-to-Azure.png\"<\/a><\/p>\n

Then, we need to alter the onpremisedb database to reference the datafiles moved to the Azure Storage:<\/p>\n

\"Alter-Database.png\"<\/a><\/p>\n

And now, I bring the database online:<\/p>\n

\"Bring-Online-Database.png\"<\/a><\/p>\n

But the following error occurred:<\/p>\n

\"Bring-Online-Database-Error.png\"<\/a><\/p>\n

To understand the origin of the problem, let\u2019s see the datafiles in the Azure Storage Explorer:<\/p>\n

\"Blob-Type.png\"<\/a><\/p>\n

Conclusion<\/h3>\n

This new feature offers some advantages such as high availability or easy migration.
\nBut on the other hand, you cannot use it on a existing database, which is a serious drawback.
\nFurthermore, I do not believe that this feature would be used with on-premsie SQL Server databases, due to the latency. But I think it can be used with a virtual machine running in Azure.<\/p>\n","protected":false},"excerpt":{"rendered":"

A new functionality, introduced with SQL Server 2014, enables to store data files from a SQL Server database in Windows Azure Storage. In this posting, I will show how it works.   Accessing Azure Storage The Azure Storage account is named \u201cdbiservices\u201d, and is composed of the \u201cdatafiles\u201d container. This container does not contain blobs […]<\/p>\n","protected":false},"author":14,"featured_media":4221,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[49,51,52,213],"type_dbi":[],"class_list":["post-4220","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-microsoft","tag-sql-server","tag-sql-server-2014","tag-windows-azure"],"acf":[],"yoast_head":"\nHow to store SQL Server data files in Azure Storage - dbi Blog<\/title>\n<meta name=\"description\" content=\"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to store SQL Server data files in Azure Storage\" \/>\n<meta property=\"og:description\" content=\"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-12-18T03:02:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png\" \/>\n\t<meta property=\"og:image:width\" content=\"926\" \/>\n\t<meta property=\"og:image:height\" content=\"508\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"How to store SQL Server data files in Azure Storage\",\"datePublished\":\"2014-12-18T03:02:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\"},\"wordCount\":526,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png\",\"keywords\":[\"Microsoft\",\"SQL Server\",\"SQL Server 2014\",\"Windows Azure\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\",\"name\":\"How to store SQL Server data files in Azure Storage - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png\",\"datePublished\":\"2014-12-18T03:02:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"description\":\"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png\",\"width\":926,\"height\":508},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to store SQL Server data files in Azure Storage\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to store SQL Server data files in Azure Storage - dbi Blog","description":"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/","og_locale":"en_US","og_type":"article","og_title":"How to store SQL Server data files in Azure Storage","og_description":"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/","og_site_name":"dbi Blog","article_published_time":"2014-12-18T03:02:00+00:00","og_image":[{"width":926,"height":508,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png","type":"image\/png"}],"author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"How to store SQL Server data files in Azure Storage","datePublished":"2014-12-18T03:02:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/"},"wordCount":526,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png","keywords":["Microsoft","SQL Server","SQL Server 2014","Windows Azure"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/","name":"How to store SQL Server data files in Azure Storage - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png","datePublished":"2014-12-18T03:02:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"description":"SQL Server data files in Windows Azure allows to create a database in a SQL Server running on-premise or in a virtual machine in Windows Azure with a dedicated storage location for data files in Azure Storage.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/General.png","width":926,"height":508},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-store-sql-server-data-files-in-azure-storage\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to store SQL Server data files in Azure Storage"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4220"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4220\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/4221"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4220"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}