17 Sep

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

18 Aug

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

12 Jun

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)

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.

( 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

3 Jun

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

25 Apr

SQL Server Managed Backup to Microsoft (Windows) Azure | Part-1

SQL Server Managed backup is a new feature introduced in SQL Server 2014, this feature is simply outstanding – you don’t have to worry about the backup strategy anymore, this feature manages and automates the backup process.

This feature uses sort of artificial intelligence – if you see at current scheduling features they just keep on taking backups at a specified time interval whereas, in this feature it checks couple of things and based on the situation it decides whether a backup needs to be initiated for a Database or not. This Feature is based on Retention period selected for backups and transaction workload on the database and it also provides Point-in-time recovery. You want to know how? Keep reading… you’ll get your answers soon.

You can enable this feature at two levels Instance as well as Database level. It doesn’t really matter if your SQL Server is running On-Premise or on Cloud. SQL Server Managed Backup to Microsoft (Windows) Azure is in-fact recommended for SQL Server running on a VM in Cloud.

The Retention period is a value which determines what backup files to keep and what needs to be deleted in the Azure Storage so that the underlying DB can be recovered to a point in time within the time frame specified. The valid retention period values are from 1 till 30 days.  The backup_finish_date of the backup is used to determine and match the time specified by the retention period settings.

Naming convention it follows for the backup and container

The backup files are named using a combination of first 40 characters of the database name, the GUID of DB without “-“& the timestamp; all these values are separated using Underscore to make it readable. The .bak and .log extensions are used for Full and Log Backups respectively. In case of Availability Group databases the Availability group database GUID is added after the first 40 characters of the DB name, you can get this GUID value from group_database_id column of Sys.databases.

The same way Backup container of (Windows) Microsoft Azure also get named using the SQL Instance name and in the case of Availability databases it gets named using GUID of Availability group.


Conditions that trigger a backup

As we discussed earlier also SQL Server managed backup doesn’t initiate a backup merely on the basis of schedule which has been fixed but takes a calculative decision whether a backup is really required for the given DB or not. It decides based on the below mentioned conditions:

A. Full backup gets triggered:

1. At the time when Managed Backup is configured on DB or when it is configured on the complete instance with default settings.

2. If the log generation since last full backup is greater than or equal to 1 GB in size.

3. If maximum time interval of one week has passed since the last full backup. (it tries to take at least one full back up every week)

4. When the log chain is broken; it checks periodically whether the log chain is intact or not by comparing first and last LSN of backup files, if it finds there is a gap it initiates a full DB Backup. There could be n number of reasons for log chain breaks, most common would be:
a. Switching recovery model to simple.
b. Taking back up through any other job\manual process.
c. Deleting some backup files
d. Accidental overwrites of backup files.
e. Truncating the log.

B. Transaction Log backup gets triggered:

1. When the Transaction Log space used is equal to or greater than 5 MB.

2. The maximum time interval of 2 hours has passed since the last log backup.

3. If no log backup history is found; for example when you enable Managed backup for the first time for the respective DB.

4. Anytime the TLog backup is lagging behind the full backup, it always tries to keep the log chain ahead of the Full backup. In simple words after every full backup it will initiate one TLog backup.


Sarabpreet Singh Anand

25 Feb

Benefits of using Windows Azure Storage

Everybody is talking about Cloud now a days, its the next big thing in the market and we are curious to know more about it. Microsoft introduced Windows Azure Storage features long time back but we are still not aware about its full potential.

Azure storage accounts provides many great features but for the sake of brevity let’s look at some really cool things you must be aware of as an end user:

  1. Nearly bottomless storage: you don’t have to worry about how much storage we have and how to manage your devices.
  2. No Provisioning is required:
  3. Relatively economical Option: It is comparatively cheaper than having on-premise storage with so many capabilities.
  4. No Device management: you don’t have to bother about device management.
  5. Geo-Redundant: Provides a capability of Geo-Redundant.
  6. Media Safety.
  7. Remote Accessibility: You can access the data anywhere.
  8. Flexible, reliable, and limitless off-site storage: no need to ship the backup tapes to Offsite location to meet Compliance and prepare for DR.

To know more about the storage accounts and its pricing, please check the below link:http://azure.microsoft.com/en-us/services/storage/



Sarabpreet Singh