In the last post I talked about sequence I would take for this migration. The first step in this sequence will be to migrate the database from an on-premises SQL Server to SQL Azure.
Creating the on-premises database
The purpose of this database is to support an application where logged in users may post questions to the instructors at the Windows Azure Boot Camps being hosted around the country. After a question has been posted the attendees can vote to indicate the general interest level in the questions. At the end of the event, a quick run through the list can be used to uncover any unanswered questions.
The database was created with all of the default options via the SQL Server Management Studio with the name of AzureOverflow. The relatively simple needs for this database are a table for questions, a table for votes, and support for user management. The scripts to create the tables and relationships that I added to the database can be found here. The ASP.NET Membership provider was chosen to provide the user management support needed for this application. The command line used to create the membership objects was:
aspnet_regsql –A mr –d AzureOverflow –E
The primary tool we will use to perform this migration is the SQL Azure Migration Wizard. As of this writing, this tool has the capacity to do migrations from:
- SQL Server to SQL Azure
- SQL Azure to SQL Server
- SQL Azure to SQL Azure
What I will focus on here is the analysis features of this tool. This will give you some warning about areas where you might expect issues to arise when performing the migration. This should be your first stop before you actually try to move your data to SQL Azure. To perform the Analysis, start up the Migration Wizard and select SQL Database under the Analyze Only section.
After this you will be prompted for the necessary information to connect to your SQL Server. Analysis results from the AzureOverflow database indicate that there are a few issues with some of the items created by the Membership Provider, but the items required to support questions and voting were fine.
Migrating Membership Data
As it turns out, using the ASP.NET Membership Provider on SQL Azure is a solved problem. An updated tool for creating the Membership database on SQL Azure can be found in KB2006191. The command line will be similar to the aspnet_regsql tool with a few modifications. Since Windows Authentication is not currently supported within SQL Azure, we will need to provide a user name and password for SQL Authentication. A server name will also be required since the tool will not be running against a local database.
aspnet_regsqlazure –s mydatabase.database.windows.net –d AzureOverflow –u username@mydatabase –p P@ssw0rd –a mr
After running this command, all of the required elements for the Membership Provider will be created on our SQL Azure database. That, however, is not the end of the story in regards to the Membership Provider. No real business is going to be willing to throw away the user data already stored in their on-premises database, so next we need to migrate the data. The most efficient way to deal with copying a mass of data from one database to another is to use BCP (Note that the Migration Wizard uses BCP as well). Sure, you could use the Generate Scripts feature from within Management Studio, but the result of that would be an insert statement for each entry in the tables that you want to migrate. If you were performing a migration on a database of any reasonable size, this is not a viable option. BCP is by far the most efficient way to move our data. We will perform the BCP operation in two steps. First we need to export the data from each table into a file:
bcp dbo.MyTableName out MyTableName.dat –n –S MySqlServerName –T
Next, we need to import the data from the file created above into the SQL Azure database. Again, note that we will need to provide the server name as well as user name and password for SQL Server authentication.
bcp dbo.MyTableName in –n –S myserver.database.windows.net –U username@myserver –P P@ssw0rd –E
A command file which performs this export and import on all of the Membership Provider tables used by this database can be found here.
Migration of the Application Data
This portion of the migration that relates to questions and voting is as it should be, completely uninteresting. The SQL Azure Migration Wizard can handle the tables and data required to support the questions and votes without issue. When the option comes up to Choose Objects, I will select all of the tables that have not already been migrated with the Membership Provider in the steps above.
After connecting the wizard to our SQL Azure database, the tables will be created and the application data migrated without incident. In most cases, the Migration Wizard will be the only tool you need. Be sure to perform the analysis first to determine if this is true for your data. For more information on using the migration wizard check out this post by Rich Dudley. It is an excellent example that walks through using the wizard from start to finish.
Since SQL Azure is simply a TDS endpoint, the only change that needs to occur inside of our web application is the connection string to the database. There are a couple of differences required of connection strings for SQL Azure databases. First, the connection is to a database and not to a server, so the Initial Catalog parameter of the connection string is a required element. Also, as already mentioned, the current version of SQL Azure does not support Trusted Connections, so you will need to provide a user name and password in the connection string. Also note that the user name is required to be in the format user@server. I have heard rumblings that this will not be required in the near future, but nothing that is confirmed. This results in a connection string entry that looks like:
<add name="ApplicationServices" connectionString="Data Source=DATABASENAME.database.windows.net;Initial Catalog=AzureOverflow;User Id=USERNAME@DATABASENAME;Password=PASSWORD;" />
Testing the Application
After migrating the user data, application data, and updating the application configuration, our web application is now using a SQL Azure database rather than the on-premises SQL Server. Make sure you run through your usual battery of smoke tests to validate major pieces of functionality at this point.
Use the Force
If you have a database that the Migration Wizard simply will not move successfully, be sure to check out Roger Doherty’s blog post on Brute Force Migration of Existing SQL Server Databases to SQL Azure. For this method to work, you will need to change the default scripting option to script for a SQL Azure database before you generate your DDL scripts.
There is very little that can go wrong using the brute force approach listed in the article, but it is certainly a fair bit more effort than the Migration Wizard. So if at all possible, stick with the Migration Wizard for a simple and painless migration. As with many things cloud related, a hybrid approach may be your best options. You can see that throughout this post with the use of the Migration Wizard, updated aspnet_regsqlazure to perform the DDL operations, and using BCP to migrate the user data. Carefully consider your cloud migration tasks; There are usually several ways to perform a task. Determine the cost of these options in terms of your time, the time required to perform the task, and the cost of the cloud resources you will use during the migration. All of these costs will influence your choice of migration techniques.