The next theme in my Hybrid Cloud journey is “SQL Server data files in Azure”. So, from SQL Server 2014 (including 2016) you can put your database data files (and log files in case you thought it was only the data files) from your on premise SQL Server (or from your Azure VM) into blob storage in Azure.
Need to take breadth here … and say … for an on premise instance why on earth would you do this? Who in their right mind would do this and what would be the benefit? However I can see there might be a few benefits for Azure VMs. Anyway I looked them up on MSDN for the reasons why:
- It can make migration to Azure easier – move your data files to Azure and then it won’t feel so much a stretch to create a SQL Server VM in Azure and attach the same database files.
- Cost and storage benefits. The storage in Azure is pretty large .. so you shouldn’t run out of space. It’s also relatively cheap compared to your on premise storage so if your SAN is full and the piggy bank is empty it might offer an alternative.
- HA. This could possibly be a nice alternative. If your on premise SQL Server machine has crashed, or your Azure VM has gone walkabouts then you can just re-point them at the data files in the blob storage.
- Snapshot benefits. Especially nice for SQL Server running on Azure VMs. This allows pretty instantaneous backups and fast restores. I’ve got this down to check out on the next step in my Hybrid Cloud Journey.
High Level Steps
- Set up a Storage Account in Azure and create a Shared Access Signature (SAS) key. I had hoped that we might be able to use a storage account identity and access key as I did in the set up for the SQL Server backup to URL but it’s not listed.
- Create a Credential using the SAS key.
- Create a Database that uses the blob storage.
Code and run through
OK – this was a bit messier than I thought and I had to do it twice. My fault – I initially created a brand new Storage Account in one of Azure Resource Groups. However I created it as a Blob storage account rather than as a General storage account.
I got this error message:
Msg 5120, Level 16, State 156, Line 32 Unable to open the physical file "https://sikfinkdatafiles.blob.core.windows.net/sqldata/SandboxBlobStorageData.mdf". Operating system error 13: "13(The data is invalid.)".
Guessed it might be to do with the type of Storage Account so I created another one, this time a General Storage Account and then create a Blob Container. Once got that sorted it all worked as expected.
In the Azure Portal – Create a general storage account and create a container (folder) to hold your files.
In the screenshot below you can see that I have created a storage account called sikfinkdata
and added a blob container called sandboxdb:
In the Azure Portal – Generate a Shared Access Signature (SAS)
If you click on the Shared Access signature as outlined in the image above the following dialogue box pops up:
I give it all permissions as above and then Clicked Generate SAS
If you copy the key it looks like this:
Note: remove the ? from the beginning of the key
In SSMS – Create a credential based on the SAS Key
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://sikfinkdata.blob.core.windows.net/sandboxdb') CREATE CREDENTIAL [https://sikfinkdata.blob.core.windows.net/sandboxdb] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,SECRET = 'sv=2015-04-05&ss=bfqt&srt=sco&sp=rwdlacup&se=2016-09-03T01:40:59Z&st=2016-09-02T17:40:59Z&spr=https&sig=XXXXXXXXXXXXXXXXXXXXXXXXXX';
If you leave the ? in the key you may get an error message like this. You need to remove it.
Msg 5123, Level 16, State 10, Line 31 CREATE FILE encountered operating system error 1117(The request could not be performed because of an I/O device error.) while attempting to open or create the physical file 'https://sikfinkdata.blob.core.windows.net/sandboxdb/SandboxBlobStorageData.mdf'.
Create the database with the files on the blob storage
CREATE DATABASE Sandbox_Blob_Storage ON ( NAME = Sandbox_Blob, FILENAME = 'https://sikfinkdata.blob.core.windows.net/sandboxdb/SandboxBlobStorageData.mdf' ) LOG ON ( NAME = Sandbox_Blob_Log, FILENAME = 'https://sikfinkdata.blob.core.windows.net/sandboxdb/SandboxBlobStorageData.ldf' ) ;
You can now see the files via the Azure Portal
And the database in SSMS
If you like your management in one place you can open the Storage Account in SSMS.
And you can always look in Azure Storage Explorer which is a great tool available on Codeplex.