Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Azure Migration Wizard


By:   |   Read Comments (2)   |   Related Tips: More > Azure

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

SQL Azure provides relational database capability in the cloud.  One of the features that is missing is a way to move databases up or down from your in-house SQL server.  So how do you move a database schema and data up to the cloud?  In this tip I will walk through a utility that will help make the migration much easier.

Solution

SQL Azure is Microsoft's relational database that is part of its Windows Azure Cloud Platform as a Service offering.  While it includes most of the features of SQL Server 2008 it doesn't include any backup or restore capabilities that allow for hoisting schema and data from an on-premises database up to SQL Azure.  The documentation refers to using SQL Server Management Studio's (SSMS) scripting capability for this task. 

While SSMS has the ability to script both schema and data there are several problems with this approach:

  • SSMS scripts all database features, but there are some features that SQL Azure doesn't support
  • SSMS doesn't always get the order of objects correct
  • SSMS scripts data as individual insert statements, which can be very slow

Recognizing that the SSMS approach doesn't work very well, two programmers, George Huey and Wade Wegner created the SQL Azure Migration (SAMW) wizard and posted it on CodePlex.  You'll find the project on the SQL Azure Migration Wizard page where you can download the program including the source code, engage in discussions and even post a patch if you're interested in contributing to the project.

You'll need the SQL Server R2 client tools (November CTP or later) and the .Net Framework on the machine where you unzip the download file.  There's no install program, at least not yet, just the SQLAzureMW.exe program and configuration files.

SAMW is a wizard style Windows Forms program.  When the SAMW is started the first task is to select a process to apply.  The available options, shown below, allow you to analyze and move schema and data between databases on the local network and SQL Azure databases.   For the purposes of this article we'll follow the Analyze and Migrate Wizard shown selected here. 

how do you move a database schema and data up to the cloud?

One of the features that makes SAMW much more than a scripting tool is the optional analysis step.  SQL Azure is based on SQL Server 2008, but the features that Azure supports are not identical to terrestrial SQL Server 2008.   The Azure T-SQL is a subset of the full 2008 T-SQL. The analysis step examines scripts looking for features or syntax that doesn't work in SQL Azure.  Some typical issues are:

  • Tables without clustered indexes
  • Cross database references
  • use of SQLCLR or one of the built-in data types that depend on the CLR such as hierarchyid

SAMW will find these issues and many more by examining the T-SQL using regular expressions and can in addition, suggest or make changes.  In particular, it does a good job of adding clustered indexes to tables that don't have them. 

As you can see from the choices above there are three places that the T-SQL used for the analysis can come from:

  • The Database
  • A file of T-SQL code
  • A SQL Profiler Trace file

Any of these sources is analyzed by applying a set of regular expressions that live in SAMW's NotSupportedByAzureFile.Config file.  The top of the file, formatted for visibility, is shown here with its first rule that removes "NOT FOR REPLICATION" clauses.

three places that the T-SQL used for the analysis can come from

The NotSupportedbyAzureFile can be customized to make additional changes, add additional messages, or to relax restrictions as SQL Azure evolves.

Once we've chosen the "Analyze and Migrate\SQL Database" wizard and press "Next >" the standard select a server dialog appears and a source database must be selected as seen here:

"Analyze and Migrate\SQL Database" wizard

The screen above shows picking the old pubs database.  It's still possible to download the pubs and Northwind databases from the Microsoft download site.  Just search for "SQL Server 2000 Northwind and pubs". 

Once a database is chosen it's possible to select all objects or a subset of the database objects to analyze and move.  In this case I've chosen to move all objects

Just search for "SQL Server 2000 Northwind and pubs"

A summary screen is displayed and then SAMW goes to work scripting the selected database objects and analyzing them for issues based on the NotSupportedByAzureFile and then BCPing the data out to temporary files.

The pubs database is pretty simple and the only problems that it found were missing clustered indexes on two tables: dbo.discounts and dbo.roysched. SAMW picks a column to cluster on arbitrarily and moves forward to the BCP step.  This screen shot illustrates how the report shows what its done:

SAMW picks a column to cluster on arbitrarily and moves forward to the BCP step

After the analysis is done the user must proceed to connect to SQL Azure as shown below.  Notice the server name is followed by ".database.windows.net" and the server name, preceded by an @ sign, must go in the User name field after the actual user name.  This extra server name is necessary because some connection methods need the server name in the UserID field of the connection string.  Parts of the server name and user name below have been obscured for privacy reasons.

After the analysis is done the user must proceed to connect to SQL Azure as shown below

The final step is to create the target database for migration and push the trigger.  The upload process for pubs isn't very long because the database is so small.  Notice the upload rows per second that are reported below: 14.21, 551.28 and 190.91. 

The final step is to create the target database for migration and push the trigger

The upload rates are much slower that I've come to expect from BCP.  Even going to a slow server I'd expect ten times the number of rows per second.  The slower performance may have a lot to do with the transmission over the internet, but it also may be due, in part, to two SQL Azure features:

  • The data is written two three copies of the database
  • Clustered Indexes are required instead of heaps, which are best for BCP

All in all the SQL Azure Migration Wizard is a necessary tool for anyone working with SQL Azure in the short term.  When additional features are added to SQL Azure, such as SQL Server backup and restore, SAMW may not be necessary.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, October 14, 2017 - 6:59:36 PM - Vivek Back To Top

 

Looks like the download isn't available anymore. Did you have a local copy somewhere that you can share? 


Friday, August 07, 2015 - 4:27:46 AM - Ana Back To Top

Is there a way to analyze databases of multiple servers to be migrated to SQL Azure. The tool seems to analyze databases of a single server. SO is there a way multiple servers can be passed to the tool and we get analysis of all the databases on those servers?

 


Learn more about SQL Server tools