Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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




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.



    



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

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

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

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

 


Learn more about SQL Server tools