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-premises databases to the Azure Virtual Machine. What is the easiest way to accomplish this task? See how it is done in this tip.
Solution
This tip is branching off of another tip I wrote recently, “Move an On Premises SQL Server Database to the SQL Azure Cloud” where I explained how to move an on-premises database to the cloud using the Export/Import Data-tier application. After some comments on this tip, research and testing I think it would be beneficial for the SQL community to understand the different options of moving to the cloud.
If you’d like to follow along with the examples, I suggest creating a free (trial) account at http://www.windowsazure.com. Once you are logged into the Windows Azure portal a SQL Server will need to be created.
Click on SQL Databases, Servers, Create a SQL Database Server:
Create a login name, password, and choose your region and click the check mark:
A new SQL Server should be built in a few seconds! Click Manage at the bottom and a few firewall rules options may appear. Click Yes on all of them.
Next, click on the server, and click Dashboard:
On the right side of the screen, you will see the full SQL Server name. In this example, the name is ns9rkrmwj9.database.windows.net. Write this name down, you’ll need it later.
Now that we have a SQL Azure Server built, let’s move our databases.
Using the Deploy Database to SQL Azure Wizard
In SQL Server 2012, there is a new wizard that will allow you to move an on-premises database to SQL Azure. This is only available in SQL Server 2012 since SQL Azure was nonexistent in previous versions.
To use this wizard, open SQL Server Management Studio (SSMS) and connect to the server. Right click the database you want to move, click Tasks, Deploy Database to SQL Azure…
Click Next at the introduction window until you are at the Deployment Settings screen. On the Deployment Settings screen click Connect to connect to your SQL Azure Server that we created above.
Make sure you adjust the Maximum database size to the desired amount and click Next:
View the Summary page and click Finish:
During my testing I noticed that each table being exported has to have a clustered index and cannot contain extended properties.
Once the wizard completes successfully, connect to the SQL Azure Server and view the copied database:
Next Steps
- Check out the previous tip regarding Moving an On-Premises SQL Server Database to the SQL Azure Cloud
- Certain features aren’t supported in SQL Azure like they are on a standalone SQL Server so be sure to view error logs and test the migrated database before using in a production environment.
Brady has been in the IT industry for 10+ years. He has worked in administrative roles using MSSQL 2000 to 2012 as well as Sharepoint 2007 and 2010. He currently serves as a Database Administrator in Nashville, TN. You can view his blog @ http://www.sqlbrady.com.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2013
