Move an On-Premises SQL Server Database to the SQL Azure Cloud

By:   |   Updated: 2013-08-02   |   Comments (7)   |   Related: More > Azure

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

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:

create a storage account from Windows Azure

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

Enter a URL and Location

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:

Click Manage Access Keys

take note of the Storage Account Name and Primary Access Key

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

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:

Back in SSMS

Click Save to Windows Azure and the Connect...button.

Click Save to Windows Azure and Connect...

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:

enter the Storage Account name

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

You should now be connected to your storage account

Click next to view the Summary and Finish.

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:

you will see that the .bacpac file was created

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

connect to the SQL Azure instance

Choose Import from Windows Azure and click Connect...

Choose Import from Windows Azure and click Connect...

Enter the Storage Account name and Account key 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:

On the Database Settings screen you can change the database name

Click next to view the Summary and Finish:

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:

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

Next Steps
  • To view more Azure tips from MSSQLTips.com click here.
  • Signing up for a free trial of Azure is easy. Visit WindowsAzure.com.


Last Updated: 2013-08-02


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, November 02, 2016 - 5:45:34 AM - Thomas Back To Top

Thank you very much mate. This helped me a lot.


Saturday, July 09, 2016 - 5:10:56 PM - Robert Back To Top

 Thank you Brady.  I would have wasted a lot of time without your help.

 


Thursday, May 15, 2014 - 1:30:58 AM - Dinesh Back To Top

hi,

This article is very helpful to me. I have one doubt. I have created windows Azure trial account. Is Any charges for cretae Storage Account?. Its free or not. can you tell me this details?.Its very urgent.


Friday, August 16, 2013 - 8:19:21 AM - Brady Back To Top

GrahamW- I wouldn't consider myself an "advanced" level cloud expert but using Windows Azure you can create virtual machines as well as virtual SQL Servers. In your case, it might be worth testing a VM and a SQL Server VM and running through some tests to see how everything works. It's a free trial so it's worth checking out.

Matimba-You have to have a credit card for the free trial, however, from experience once your trial runs out Microsoft will automatically deactivate your account so they will not automatically charge your account for the 2nd month which makes things easier to manage as far as opting out.

 

Be on the look out for another tip regarding this subject...


Friday, August 16, 2013 - 4:10:09 AM - Matimba Back To Top

I have been trying to learn more about SQL Azure but the because I have to use a credit card "not willing to get one" in order to access a trail of SQL Azure prevents me from doing so


Thursday, August 15, 2013 - 10:20:49 AM - GrahamW Back To Top

I am a newbie to cloud solutions.. I currently have on premise MSSQL Server but I also have external executables that run on a scheduled basis to import/export data to and from the database (by running stored procedures and saving/sending the output as defined in a config file).  Is there any way to migrate this whole system to SQLAzure or would we just need to replicate what we have on a VM? Any advice appreciated.


Friday, August 02, 2013 - 7:38:10 AM - Christoph MŁller-Spengler Back To Top

First of all thank you for sharing your knowledge. There are two other ways to move OnPremise databases to SQL Database.

1)

In SSMS 2012 you can right click on your database, choose "Tasks -> Deploy database to SQL Azure". You will see a simple wizard that guides you through all necessary steps.

 

2)

Second option is a executable wizard called "SQLAzureMigrationWizard" that allows you to move databases from OnPremise to SQL Database or vice versa or from OnPremise to OnPremise and From SQL Database to SQL Database. SQLAzureMigrationWizard also provides you with BatchBackup and BatchUpload modules which allow you to schedule your Up-/Download. You can download SQLAzureMigrationWizard from here: http://sqlazuremw.codeplex.com/

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools