Category Archives: SQLTips

Screen cast of SQL Server VM Creation on Microsoft Azure

Yesterday one of my friend asked me the process to create a SQL VM on Microsoft Azure, though the process is quite simple and easy but he asked me to help him since he never worked on Azure platform before.

So this screen cast post is for those who never worked on Azure and want step by step approach to create the VM.


You can create a SQL VM Machine on Azure by selecting New menu on the lower left part of the page.



You can select image of the VM from two sources: Quick create or from Gallery (There is other way also, but let’s not complicate this post, we may discuss that later) – you need to select the image from Gallery.



You can browse from the multiple available images in the gallery, as you can see you can select SQL Server 2014 RTM image on Windows Server 2012 R2.



While creation of the Image, you can select the size of the image from the options – size means the Cores and Memory you want to allocate to this VM.









Finally the VM is ready and started – you may wish to connect to the image & start working.

Enjoy learning


Sarabpreet Singh

SQL Server data files on Azure | Configure Azure security

Before we even start discussing about this feature we must have an idea about Azure Storage accounts and how the security works in Azure.

You can access your Azure storage account linked container because you are the owner, but let’s say you want to share this access (full or Partial) for a blob/container with someone else; to do this you must configure security keys.

In Azure portal security works on two types of Keys and you can use these keys in order to fulfill your security requirements; these are:

a. Storage Access Key (Shared Storage Key)

b. Shared Access Signature Key

Storage Access Key (Shared Storage Key): This is an Admin key which provides full access on your storage account and with this key the person can do any operation on underlying container\blob.

Shared Access Signature Key: This is another key which can be used to provide granular access on the container and helps you enforce restricted permissions (read only, read\write with expiry etc.) this key is also known as SAS Key. SAS key normally looks like a URL which defines the resources you can access along with permissions and the validity of the SAS Key.

How to get these keys:


Storage Access Key (Shared Storage Key)

Azure portal provides the Shared Storage Key and you don’t need to use any other tool. All you need to do is login to your Azure Portal, select the Storage Account and select Manage Access Keys from the lower bottom of the portal. Refer the screenshots below:



You can easily copy the access key by using the copy link, marked in the image above.


Shared Access Signature Key

As of now unfortunately Azure portal doesn’t have a GUI for this, if you want to generate a SAS key you can do that either by Programming (by calling REST APIs or by using PowerShell cmdlets) or by using Freeware or third party tools. Let me show you a way to generate this key by using an awesome freeware codeplex (open source) project named “Azure Storage Explorer” which is contributed by David.

You can download the tool from here….. It’s a small 3 MB file. The new version is very intuitive and has many new features….Once the tool has been installed you must add your storage account; in order to add your account you have to provide your Storage Access Key. Refer the screenshot below:



Once the Storage account has been added you can easily navigate the Storage account and containers, select the container where you want to configure the SAS key and select Security from the upper tab.



It will popup a window with two Tabs: Access Level and Shared Access Signature. You can change the default behavior of your storage container or configure a SAS key by using the tabs respectively. Refer the screenshots below to get more clarity.

AzureStorageExplorer-AccessLevel  AzureStorageExplorer-Create_SAS_Key_sarabpreet

You can read more about the tool on David’s website….here


Sarabpreet Singh

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 Winking smile )

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)

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: “

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.

( NAME = N’monprodv121′, FILENAME = N’’)
( NAME = N’monprodv12_log’, FILENAME = N’’)

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.

db created with files on cloud - sarabpreet


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.

dbfiles on azure


Sarabpreet Singh

SQL Server Data Files in Windows Azure | Part-1

This is a brand new technology which is introduced in SQL Server 2014, having said that believe me this is not something which is new to us – we’ve been using this technology since a long time in our daily life like: Streaming music files through internet or saving all your data on One Drive.

So just like the examples mentioned above now starting from SQL Server 2014 you can take advantage of Azure & place your Data and Log files in Azure no matter if your SQL Server Instance is running on-Premise or on Windows Azure Virtual Machine.

It lets you create & place your data and log files directly on windows azure storage, by doing this you get all the advantages of Azure storage – Bottomless storage, offsite location, Geo-Redundant, No provisioning and No need to do Device Management, its decay free (Full Media Safety) and always accessible remotely from anywhere. To know more read this.


Why do you want to place your data\log files on Azure?

Don’t you think this is counter intuitive to keep the files on Cloud? Yes at first even I was thinking the same. This looks odd but can be used in many circumstances. So essentially all you need to do is re-attach your (data and log) files from cloud to your SQL Instance, SQL instance can be on-premise or on Cloud.

The next question will obviously be regarding Performance…. In case you are skeptical about the performance you can host your SQL Server also on Cloud (windows Azure VM) this way your SQL Server & data\log files both will be on cloud.

This feature gives you power of a DR, even if your Data Centre goes down you can bring back the SQL instance in minutes without any special technique all you need is a SQL Instance which you can use just like your production server because all data is still intact & placed on Cloud – you just have to re-attach the files to make it working.


Is it Secure?

Obviously it is very much secure and your data is protected in many ways:

a) Windows Azure itself provides a mechanism to secure & protect your data; it’s not like a public folder which is open to all. To know more you can read this blog which will help you understand how the security works in Azure Storage and the importance of each type of key.

b) Granular Encryption encrypts the data (Cell by cell encryption) at all times & the key to decrypt is only available with SQL Server instance and never on the cloud along with data\log files.

c) If you are still not convinced you may still use TDE (transparent data encryption) to further encrypt it. It is fully supported.


Ok, I am convinced but what about my RTO & RPO?

You don’t have to worry about these two because inherently this technology take care of these two; It saves a lot of your time – you don’t have to restore the Database from a backup even if your server got crashed because your data files are intact in Cloud, you just need to re-attach them on a new Instance and your database is available right from the point where you left (you lose only uncommitted transactions) so it gives you better RPO (Recovery Point Objective) along with RTO (Recovery Time Objective)

Enough of talking now, let me know how to use this amazing feature….


Sarabpreet Singh

Lessons learnt by implementing Backup Encryption

We’ve already discussed Backup Encryption feature in detail, incase you’ve not covered those please find the link:



There are a few things you must know before you implement backup encryption in your environment, here I’ll try to list them:

1. Original Certificate which was used as an encryptor should be available at the destination server\Instance to restore an encrypted backup. Encrypted backup keeps track of the thumbprint of the certificate, if the thumbprint doesn’t match it will never allow SQL Engine to read the backup, you won’t even be able to even get the file list details from the backup file (Restore FilelistOnly option). Refer the screenshot below which shows the error:



2. We should also refrain our self from renewing or changing the certificate in any way, renewal or changing the certificate results in changing the thumbprint of the certificate therefore making the certificate useless for the backup restore.

3. The login ID being used to restore the encrypted backup must have View Definition permissions on the encryptor (Certificate\Asymmetric Key) which was used to encrypt the backup.

4. You must restore the Database Masker Key on the destination server\SQL Instance since the certificate we used was encrypted using DMK only.

5. You need not to Restore the Service Master Key, if your DMK was encrypted using a Password- that is not a must.

6. You must take a backup of all your keys\certificates and passwords and keep it on a safe location other than the source server. The best practice would be to keep a copy of these things at your DR site or Offsite.

7. The Database master key (DMK) must be explicitly opened if it was encrypted using a password.

8. Restoring a Master Key can be a resource intensive task, if the current master key is being used to encrypt many things and you are restoring a new Master Key it has to decrypt and re-encrypt everything- we must try to do it in Off-Peak time.


Happy Learning

Sarabpreet Singh

Backup Encryption | Part-3

In Part-1 we discussed the feature of backup encryption and part-2 walks us through the scripts to prepare for the pre-requisites and take an encrypted backup, In part-2 we took the backup of Repro_FGDN Database on MBTEST server.

We took the encrypted backup using Certificate as an encryptor and used AES_256 encryption algorithm.

In this part we’ll try and restore the same encrypted backup on another server – FGDNPROD.


–Before you restore the encrypted backup on the destination server, you must create the Encryptor; in part-2 we created a certificate which was used as an encryptor. The certificate was encrypted with a DMK so we must first restore the DMK before creating the certificate.
FROM FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’;


–You must open the master key before using the DMK, once the Database Master Key is open – it stays open for the complete session.
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’


–Create the certificate
FROM FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , DECRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’

–Closing the DMK
close master key


Restoring service master key on destination server is not required.

–Restore encrypted Database backup
Restore database repro_FGDN
from disk =’C:\backup\Repro_FGDN_22.bak’


Finally after creation of the encryptor you will be able to restore the encrypted backup, refer the screenshot where I was able to restore the encrypted backup on another server named FGDNPROD.

Restore completed without service master key2


Sarabpreet Singh

Backup Encryption | Part-2

In the first part we discussed the new feature of Backup Encryption which is introduced in SQL Server 2014, the post describes the benefits, restrictions, permissions required etc.

Now let’s see this in action, the below mentioned scripts will generate Service Master Key, Database Master key and a Certificate which will act as an encryptor and post that we’ll use the encryptor to take a backup with Encryption for a DB named Repro_FGDN.


–Regenerating Service Master Key

–script to Backup Service Master key
TO FILE = ‘C:\backup\SQL2014_service_master_key.key’

–Creating Database Master Key also known as DMK

–script to backup DMK
TO FILE = ‘C:\backup\SQL_Prod_Repro_F.key’

–Creating Certificate (Encryptor) which is encrypted by DMK

–Script to Backup certificate as soon as it gets created.
TO FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , ENCRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’

–Script to backup Database using Encryption
TO DISK = N’C:\backup\Repro_FGDN_22.bak’
        , ENCRYPTION
        ALGORITHM = AES_256

This shows the backup completion from the Server named MBTEST.



This is the screenshot of the Backup folder which consists backup of all Certificates, Keys along with Encrypted backup.



Learning\Points to consider:

1. Backup the Certificate\Keys as soon as you create them, you never know when you might need to recover the database and without these encryptors you won’t be able to recover the database.

2. Certificate to be used as an Encryptor for backup encryption should be encrypted by DMK only.


Sarabpreet Singh

Backup Encryption | Part-1

We’ve been asking for this feature from a long time and it was one of the main reasons why third party tools were being used, they were minting money like anything. Thankfully Microsoft introduced this feature in SQL Server 2014, now you can encrypt the data while creating a backup at runtime and creates an encrypted backup file.

The best part is all type of storage destinations be it On-Premise or Windows Azure Storage all of them are fully supported. In addition to that you can also use this feature if you are using SQL Server Managed Backup to Windows Azure. I know what you are thinking “you are still using old version and you’ve to rely on these third party tools” The good news is, if you want you can use Backup encryption in your old SQL versions also (like SQL Server 2005, 2008 and SQL 2008 R2) but only if you plan to take backups on Azure by using SQL Server Backup to Windows Azure Tool. Read more about this feature here…



1. This helps in securing the data; SQL Server encrypts the data at the time of creating a backup – so no need to change anything on the application front.

2. You can also use Backup Encryption for TDE enabled databases.

3. Since this is also supported with SQL Server Managed backup to Windows Azure this gives an added security for off-site backups.

4. You’ve got multiple options to select the encryption algorithm to meet your requirements.

5. You can integrate encryption keys with Extended Key Management (EKM) providers.



To make use of Backup Encryption all you need to do is provide an encryptor (that could be a Certificate or Asymmetric Key) and choose an Encryption algorithm. SQL Server provides four encryption algorithm to choose from these are: AES 128, AES 192, AES 256 and Triple DES.



During restore you don’t have to specify any encryption parameters. However the certificate or the asymmetric key used to encrypt the backup file must be available on the instance you are restoring to. The user account performing the restore must have View Definition permissions on the certificate or key. If the DB you are restoring is already TDE configured, the TDE certificate should also be available on the instance you are restoring to.



You must create a Database Master Key (DMK) before creating a Certificate or Asymmetric Key. A DMK is symmetric key that is used to protect the private keys of Certificates and Asymmetric keys that are present in the database.

Once a DMK is created you can create a certificate or asymmetric key to use for backup encryption.



1. While encrypting backups using asymmetric key always remember the keys residing in the EKM provider are supported.

2. Two SQL Editions (SQL Express and SQL Server Web) do not support encryption during backup. Having said that restoring from an encrypted backup to these editions is fully supported.

3. Also note that earlier versions of SQL Server cannot read encrypted backups.

4. Appending to an existing backup set option is also not supported if you are encrypting the backup.

It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable.



To encrypt or restore from an encrypted backup View Definition permission on the certificate or asymmetric key that is used to encrypt the database backup. However Access to the TDE certificate is not required to backup or restore a TDE protected Database.

Sarabpreet Singh