In the previous post I showed you how you can setup a backup to Azure storage and also mentioned that you can add your data/logfiles to Azure storage. An important note there is that this feature does not work with the storage account key, but you’ll need to set it up with SAS (Shared Access Signature) tokens.
Generating the SAS token
The easiest way to generate the token is to use the following code sniplet, which has the following result ;
Create the credential
Use the generated T-SQL code (output from the above sniplet)…
And the credential has been set up!
Setting up a new database
Let’s use a typical create database statement. Though instead of a local drive, we’ll be using the link to the container on our storage account.
-- Create database with data and log files in Windows Azure container. CREATE DATABASE testdb ON ( NAME = testdb_dat, FILENAME = 'https://testdb.blob.core.windows.net/data/TestData.mdf' ) LOG ON ( NAME = testdb_log, FILENAME = 'https://testdb.blob.core.windows.net/data/TestLog.ldf')
And here is how it went for me…
Extending an existing database
You can also do the same on an existing database / via the gui (sql management studio.
Add the files and press okay. An important note here, if you are using the auto extend, be aware that the max “file” (blob) size is 1023GB on Azure!
And just to be sure… Yes, the data files are added to our azure storage account!
- Working with data files that are directly stored on an Azure Storage Account is possible
- This system works with SAS tokens and not with a storage account key.