Move an On Premise SQL Server Database to the SQL Azure Cloud

Problem

I’ve introduced my manager to SQL Server’s cloud based platform, SQL Azure and Windows Azure Virtual Machine. He wants to test functionality with some of our applications, which would require me to move my on premise databases to the Azure Virtual Machine. What is the easiest way to accomplish this task?  See how it is done in this tip.

Solution

Most of us have heard of SQL Azure, but a lot of people have yet to adopt the “cloud” version of SQL Server. This tip will focus on how to move an on premise database to the SQL Azure and Windows Azure VM. To learn how to get started with SQL Azure visit Microsoft’s Azure site.

Most of us are used to the “Restore Database” command found by right clicking the Databases container in SQL Server Management Studio (SSMS). SQL Azure has done away with this and instead only gives you the options below:

On premise database	Azure database

This isn’t very helpful and can make you want to deactivate your Azure subscription immediately, but hold on…..there is a way.

First, you will need to create a storage account from Windows Azure. To do this, log in to Azure and click Storage, New from the left pane:

Click Quick Create, Enter a URL and Location, and click Create Storage Account:

Once the storage is created you should be able to view and manage it by clicking on the storage item on the left pane.

Click Manage Access Keys and take note of the Storage Account Name and Primary Access Key:

Next click on the storage name and click Containers, Create a Container:

Back in SSMS, right click on the database you want to move to the cloud and choose Tasks, Export Data-tier Application:

Click Save to Windows Azure and the Connect…button.

On the Connect to Windows Azure Storage box you will need to enter the Storage Account name and the Account Key that we took note of earlier:

You should now be connected to your storage account and the container that was created:

Click next to view the Summary and Finish.

During my testing I noticed that each table being exported has to have a clustered index and cannot contain extended properties.

Back in Windows Azure Management Console, you will see that the *.bacpac file was created so it’s local to the cloud now:

Back in SSMS, connect to the SQL Azure instance, right click the Databases container and select Import Data-tier Application….

Choose Import from Windows Azure and click Connect…

Enter the Storage Account name and Account key and click Connect:

Click Next. On the Database Settings screen you can change the database name and choose the edition of SQL Azure and the Maximum database size:

Click next to view the Summary and Finish:

We can now look at our Azure Instance in SSMS and see that the new database has been created:

Source Collected from MSSQLTIPS.COM