Using the Deploy Database to SQL Azure Wizard in SQL Server Management Studio to move to the Cloud


By:   |   Updated: 2013-09-09   |   Comments (3)   |   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

This tip is branching off of another tip I wrote recently, "Move an On Premise SQL Server Database to the SQL Azure Cloud" where I explained how to move an on premise 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 would 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:

Click on SQL Databases

Create a login name, password, and choose your region and click the check mark:

Create a SQL Database Server

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.

A new SQL Server should be built in a few seconds!

Next, click on the server, and click Dashboard:

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.

On the right side of the screen you will see the full SQL Server name

Now that we have a SQL Azure Server built, let's move our databases.

Option 1: 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 premise 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...

Using the Deploy Database to SQL Azure Wizard

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.

Click Next at the introduction window until you are at the Deployment Settings screen

Make sure you adjust the Maximum database size to the desired amount and click Next:

Make sure you adjust the Maximum database size to the desired amount

View the Summary page and click Finish:

View the Summary page

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:

connect to the SQL Azure Server and view the copied database

Option 2: Using the SQL Database Migration Wizard from Codeplex

Codeplex offers a free SQL Database Migration Wizard that is designed to help you migrate SQL Server 2005/2008/2012 databases to SQL Azure.

This free download can be found here: http://sqlazuremw.codeplex.com

Once downloaded and extracted it's a pretty easy wizard to navigate. In this section, I'll show you the steps.

First, download the zip file and extract. Once extracted, open SQLAzureMW.

 Using the SQL Database Migration Wizard from Codeplex

There are a few different options for this wizard, but for this tip we'll only discuss the Analyze/Migrate Database option. Click Analyze/Migrate - Database and click Next:

for this tip we'll only discuss the Analyze/Migrate Database option

Next, a "Connect to Server..." window will appear. This is where you will enter your source information. For this example, I'll use localhost as my ServerName and Master DB for my database. Click the "Connect" button.

Next, a Connect to Server... window will appear

The wizard will show you all databases that you have access to. In this example, I want to migrate the AdventureWorks2008R2 database to SQL Azure so I'll select it and click Next:

The wizard will show you all databases that you have access to

The next screen will give me two options, "Script all database objects" or "Select specific database objects". I want to do a full migration so I'll select the "Script all database objects" option and select Next:

Script all database objects or Select specific database objects

View the "Script Wizard Summary" page to make sure all of the selections are correct and click "Next". Click "Yes" to begin creating the SQL script:

View the Script Wizard Summary page

While the script is generating you can scroll down and see the action. Once the script is finished click "Next". You can also save the script for later use if needed.

While the script is generating you can scroll down and see the action

After the script has been created another "Connect to Server..." window will appear. This is where you put in your destination (SQL Azure) server, user and database information:

After the script has been created another Connect to Server... window will appear

Click Connect. The Setup Target Server Connection screen will appear and will list the databases associated with the connected server. Since AdventureWorks2008R2 isn't in the list, we'll need to click on the Create Database button at the bottom of the window:

The Setup Target Server Connection screen will appear and will list the databases associated with the connected server

On the "Create Database" pop-up enter the name of the database and select the collation, edition and max size:

On the Create Database popup enter the name of the database

Click "Create Database". You will notice that the new database will appear in the list. Click on the database and select "Next". Click "Yes" on the Execute Script pop-up:

You will notice that the new database will appear in the list

That's it! Connect to the SQL Azure server and view the copied database:

Connect to the SQL Azure server and view the copied database
Next Steps


Last Updated: 2013-09-09


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





Comments For This Article




Friday, January 08, 2016 - 5:15:45 AM - virender mehta Back To Top (40370)

While Migrating my database to Windows Azure i am getting this below error.Can Any one help me with some suggestions..

Error SQL71005: Error validating element [dbo].[GetStockDetails_StoreWise]:
The reference to the column ItemName could not be resolved.

Error SQL71564: The permission 'CONNECT' is not valid for the target object
'guest' in Microsoft Azure SQL Database v12.

 


Tuesday, July 22, 2014 - 10:33:45 AM - Caleb Back To Top (32824)

Hi,

I'm using SQL Server 2012. During the export process of this I recieve an error while "Validating Schema Model."

Any idea what could be cauing this?

Feel free to email me at: [email protected]


Thursday, November 21, 2013 - 5:02:24 PM - Greg Back To Top (27574)

Brady, thanks for your post !

I was wondering if there is a way to get a script for the deploy database option, I was hoping to automate the process using a script.

as Im running this every morning - any idea would be really apprciated.

 

-Greg

 



download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets

Managing Azure Blueprints with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools