Beginning with SQL Server 2012 SP1 CU2, we’ve been able to backup SQL Server directly to the Windows Azure Blob service. This makes migrations to Azure virtual machines much easier for getting databases staged as well as actual migrations. I’ve used this method numerous times with client migrations due to its simplicity and ease of use.
In order to use backup to URL, you’ll need a few things. An active Azure subscription, a Windows Storage account, and its access key.
To get started, first log into your Azure account and click on Storage accounts. Next you will need to click add to create a new storage account.
You’ll need to provide a name for your account, select the deployment model, the kind of account, your performance level, any replication options, select to enable secure transfer or not, select which subscription to use if you have more than one, which resource group to use, your location, and whether to configure a virtual network or not. For this exercise, I am naming my account myblogdemo (all lowercase) and choosing an existing resource group, and selected East US, the rest of the settings I am taking the defaults.
Next you need to create a container and add some storage. Under storage accounts, click on the newly created account, for my demo, that would be myblogdemo. Next under blob service, click on containers. You’ll get a message stating “You don’t have any containers yet. Click ‘+ Container’ to get started.”. This is toward the top of the screen. Click the + Container to add a container. You’ll need to provide a name for the container as well as select the type of public access level. I like to set my containers to Private (no anonymous access) so that the container and blob data can be read by the Windows Azure account. I’m going to name my container blogdemo.
You should now see the container you just created. In my demo, I see blogdemo. Click on your container and then container properties.
To the right of the screen you will see the properties displayed. Under URL you will see the URL you will need to provide when using backup to URL. It will be similar to https://myblogdemo.blob.core.windows.net/blogdemo.
Next you need to create a credential on your SQL Server instance, this is located under Security. To create a credential, right click on Credentials and select New Credential. You can specify any name that you would like, your identity is your storage account name, and your password you will find in the Access keys for your storage account. You have two keys, you can select either one, simply copy and paste the value as your password and confirm and then click OK.
Now you should have a storage account, container, and a credential on your local SQL Server. You can now utilize the backup to URL feature and backup to the Windows Azure Storage service. You can demo this by backing up a database. If this is just a test and you are using a production database, I recommend using WITH COPY_ONLY so that you don’t potentially mess up a restore chain.
Your syntax would be similar to this:
When I executed the above query, it took approx. 3 minutes and 58 seconds to backup the 351MB database which was 108MB compressed.
To restore this database, you just have to create a credential on your SQL Server instance on your Azure virtual machine and your syntax would be similar to:
Leveraging backup to URL makes migrating to Azure virtual machines easy. You can stage a full restore and then just restore the needed logs during cut-over. Although there are a number of different ways to migrate to Azure, leveraging this method gives you much needed control and as you’ve just learned, it is very easy to setup and configure.
I hope you found this post helpful! And, if you want to find all of our SQLskills SQL101 blog posts – check out: SQLskills.com/help/SQL101