SQL Server Data Files in Windows Azure | Part-2
In first part we discussed this feature and analyzed whether we should use it or not, now let’s see some of the benefits before we’ll discuss the steps to implement it.
Apart from being a wonderful & cool looking concept this technology also delivers many benefits like:
1. You get a centralized copy of data and log files which can be connected to any SQL Server Instance instantaneously and available to all geographic locations. (but yes you need Internet Connection there
)
2. In case of any eventuality you can meet all the tight SLAs without loosing your precious data, because your data and log files are intact on cloud (even if something goes wrong with the primary site of your storage account you get your files coz you can avail HA n Geo-DR remember) and on top of everything you don’t even have to restore from backups all you need is to simply attach your files to another Instance. (Having said that it is not a substitute of your backups and it is recommended to keep taking backups the way you were taking)
3. By Placing the files on Azure you get unlimited storage capacity along with built-in HA, SLA and Geo-DR.
4. The best part is you don’t have to change anything in your application, you keep on working just like you work on any other database. (except Connection string- Obviously)
5. Security is not at all compromised all your data stays encrypted at all times in Azure Moreover, you can further increase the security by configuring TDE which is fully supported and all your keys stay on your SQL instance (Which can be On-Premise or on an Azure SQL VM)
To implement this feature you need to create a credential which will provide the access to your storage account to be able to do file related operations like (read, write or delete)
CREATE CREDENTIAL [https://mondbfiles.blob.core.windows.net/sql02]
WITH IDENTITY = ‘Shared Access Signature’,
SECRET = ‘sr=c&si=SQL&sig=%2Be1Xq9637csqkRXtGT%2B81V5DkNLoN844DFR1lmv%2B4H4%3D’
for creating credential we must have a Shared Access Signature Key, you will be able to create it easily by following the steps mentioned here….. the steps mentioned here are for the latest version of Azure Storage Explorer, but at the time of writing this article I was using the old version. GUI is a little bit changed however the concept and the steps remain the same and you should be able to create the keys without any issue.
example of a SAS key: “https://mondbfiles.blob.core.windows.net/sql02?sr=c&si=SQL&sig=%2Be1Xq9637csqkRXtGT%2B81V5DkNLoN844DFR1lmv%2B4H4%3D”
The SAS key (Storage Access Signature Key) has two parts the first part (that is in Black color) is the blob endpoint name along with the container which becomes the credential name and the second part (which is in Green color) is the one which defines the SAS key granular access and both these parts gets delimited by a “?” sign (which is in Red color) .
while creating the credential you must provide the first part as credential name and second part as SECRET with identity as “Shared Access Signature” to make it work.
Once the credential has been created on the SQL instance you can now create the DB by pointing its file creating location to your Azure Storage account container.
CREATE DATABASE monprodv12
ON PRIMARY
( NAME = N’monprodv121′, FILENAME = N’https://mondbfiles.blob.core.windows.net/sql02/monprodv12.mdf’)
LOG ON
( NAME = N’monprodv12_log’, FILENAME = N’https://mondbfiles.blob.core.windows.net/sql02/monprodv12Log.ldf’)
GO
If your credential is correct and your SAS key is valid you should be able to create the DB without any problem.
Here are couple of screenshots to make it more clear.
For creating this DB, I first created a Shared Access policy & then used the same policy to create the SAS Key.
Once the SAS key was ready I used the same key to create the Credential and then the DB was created.
Once the DB was created I checked the system tables to find where the files are pointing and it was pointing to my azure storage container as expected.
Thanks,
Sarabpreet Singh