Azure for Developers Tutorial Step 1: Migrating the database to Windows Azure

This is the first part of the Azure for Developers tutorial in which we set up a WCF service running in Azure to provide CRUD operations to a client application. For more information, please check out the Introduction.

In this step, we will attach the database to our local SQL Server instance, and then migrate it to a Windows Azure SQL Database (previously known as SQL Azure, and referred to from hereon out as WASD) using the SQL Azure Migration Wizard from CodePlex.

Let’s start by attaching the CodeCamp database to our local SQL Server instance.

Unzip the provided database and copy it to the folder with the rest of your SQL Server databases. If you took the default installation, this is under Program Files/SQLServer something or other. (Is it just me, or is it a little vexing that Microsoft put out guidelines against storing data in Program Files when Windows Vista came out, and made it difficult to get around that restriction without turning off UAC, but then they go and store the SQLServer databases there?)

Open the SQL Server Management Studio (SSMS) and connect to your local database server. Right-click on Databases and select Attach.

In the Attach Databases window, click the Add button in the middle of the screen and browse to where you put the CodeCamp database. Select it, click OK. You should see something similar to this:

Click OK to attach the database. If you look at the database, you will see there is a table called Customer, and a handful of stored procedures that we are going to use in the WCF service.

We want to migrate this database to a WASD so we can run our service against the cloud. If you are only going to run this service locally, you can skip this step and go directly to step 2.

To migrade the database, we’re going to use the SQL Azure Migration Wizard from Codeplex. You can use this handy dandy tool to analyze your database and see if you can migrate it to Azure or not. When I ran this on GoldMail’s database, I found a CLR routine that I didn’t know was in there. CLR routines aren’t allowed in WASD’s, so I replaced it with some T-SQL code in the stored procedure where it was being called. I also use this rool when I create new tables and procedures locally and want to move them into the staging database for official testing, and to copy data from the production database to the staging database.

The other thing you may see when you migrate your own database is that all tables in a WASD require an unclustered index. If your table does not have one of these, the migration wizard will add one for you.

Download the version of the wizard that you need (depending on your version of SQL Server) and unzip it. Then double-click on the SQLAzureMW.exe file to run it. You should see this:

Select SQL Database under Analyze/Migrate and click Next. Specify your local SQL Server instance and authentication, and click Connect. You can try the dropdown on ServerName, but this doesn’t always work for me, so I usually end up typing in the name of my computer and SQL Server instance. After clicking Connect, you should see the list of databases, and you should see [CodeCamp] in the list.

Select CodeCamp and click Next.

You can now select how much of your database you want to migrate. We had two huge tables with millions of records, and the rest of the tables had fewer than 10,000 records. So the day we actually moved everything into production on Azure, we migrated everything except those two tables, and while we were putting all of the services into place and testing the client applications, we migrated the last two tables.

In our tutorial, we want to migrate the whole database, so leave “all database objects” selected, and click Next to review your selections, then click Next to generate the SQL Script. This wizard uses BCP to copy the data, so the amount of time it takes will depend on how much data you have. We have none, so it should be pretty fast!

After reviewing the results summary, click Next. Now you’re going to specify the WASD you’re going to import the database into.

The SERVER name is in the format of SERVER.database.windows.net, with SERVER being your WASD server, which is assigned by Microsoft after you set up your server in the Windows Azure Portal.

Since WASD does not support Windows Integrated Security, you have to specify a username and password to the database. I’m going to use my administrative account (the name of my WASD server is blurred out).

After connecting, you should see the list of databases on the server. Click on the [Create Database] button at the bottom and create a database called CodeCamp.

After creating the database, be sure it’s selected, and then click Next. It will ask if you want to execute your script against the CodeCamp database; click Yes. It wll now show you the results of running the script.

If there were any errors, they would be displayed in red. Now you can just exit the wizard. If you now connect to your database in Azure using the SQL Server Migration Wizard, you will be able to see the CodeCamp database.

So at this point, you have the database set up in Azure and are ready to start creating the WCF service to access it. We’ll cover that in step 2.

Tags: Windows Azure