Bernie Cook's Blog

Azure, C#, .NET, Architecture & Related Tech News

SQL 2005 to SQL Azure Migration

1 Comment

Over the last few weeks I have, amongst other things, been migrating an n-tiered ASP.NET 3.5/SQL 2005 solution to Windows Azure. Usually when I hear the “migration” word I curl into a fetal position and start rocking in a dark corner but I finally had a chance to move the last of a set of solutions I manage to Azure so I was more relieved than anything.

After working through a number of ASP.NET migration issues, i.e. migrating over to the Azure session state provider and caching service, etc. I could finally see light at the end of the tunnel. I wasn’t overly concerned about the SQL migration as my database wasn’t overly complex in the scheme of things. The following post is a collection of steps I followed to successfully migrate the SQL database to the cloud.

So as far as the database side of the migration went I had to migrate the following:

  • Approximately 100 tables
  • Approximately 50 stored procedures
  • The usual collection of indexes, foreign keys and a small number of circular relationships
  • All of the existing data

So nothing too involved. Personally I’m a big fan of keeping my logic out of the database hence there are no views, functions, etc. Why the stored procedures then? Well this is how I built systems several years ago and these days I wouldn’t go past a LINQ/Entity Framework combination unless I had a perform a bulk import or something similarly intensive.

Anyway enough woffling, here is how I approached the database migration – and keep in mind this is all done with freely available applications. I’m  assuming you’re running a Windows machine, have SQL 2008 R2 installed and have an active Windows Azure subscription.

I wanted to qualify that I originally started off with Red Gate’s SQL Azure Backup (v2.6.1.131) tool but it kept failing part way through, and it’s now discontinued. After speaking to some of the Red Gate folks at DevWeek 2012 I gathered it’s because they’re moving to a new, and more lucrative, SQL Azure toolset. Fair enough.

1. Generate Database Scripts

There are a number of ways of doing this but I chose the SQL Azure Migration Wizard option. It generates both the database scripts and data files so it gets you most of the way there.

It’s important to point out a few things first:

  • For ease I’d recommend restoring a backup of the SQL database you aim to migrate onto your local machine – it helps speed things along considerably
  • Select the wizard’s advanced option (see screenshot if need be) and ensure you’re generating the table schema and data
  • If you have any circular references then you’ll most likely find you’re creation scripts will break when run for the first time so run them anyway and then cut out the scripts which are breaking and paste them at the end of the script file (SQL Server Management Studio does a good job of telling you on which line the errors are so they’re easy to locate). You’ll find that eventually you’ll have no script errors and you’re database structure will be generated correctly. Unfortunately SQL Azure Migration Wizard doesn’t resolve them all that well, but they’re easy to fix so no biggy.
  • The data files, stored as BCP files, will be dropped in a folder on your system – most likely “C:\SQLAzureMW\BCPData\” whic isn’t immediately obvious. If they’re not there you’ll see the file locations referenced at the bottom of the script created by SQL Azure Migration Wizard.

2. Generating the SQL Azure Database

Create a new, and empty, database in SQL Azure, easily done via the Azure Management Portal, and then connect via your local copy of SQL 2008 R2. Don’t forget to create a new firewall rule so you can connect remotely.

Create a new query and paste in your scripts from step 1 above, then run them and after a few seconds you should have your structure in place.

3. Populating the Database

This part has a few steps, so bear with me:

  1. Create a new file with a “.bat” extension
  2. Open the original SQL Scripts created by the Migration Wizard and scroll down to the bottom
  3. You’ll see a number of scripts that start with “– BCPArgs: …” you want to copy all of these and paste them into the “.bat” file
  4. Remove all instances of the word “GO”, we don’t want them hanging about
  5. Now here comes the slightly frustrating part, you want to update every line remaining so that this:
    -- BCPArgs:534723:[dbo].[User] in "c:\SQLAzureMW\BCPData\dbo.User.dat" -E -n -b 10000 -a 16384

    … is converted to this…

    bcp [MyAzureDatabase].[dbo].[User] in "C:\SQLAzureMW\BCPData\dbo.User.dat" -E -n -b 10000 -a 16384 -U {{:User ID}} -S {{:Server}} -P {{:Password}}

    What you’ve just done is make this into a simple command-line executable.

  6. Update the {{:User ID}}, {{:Server}}, {{:Password}} fields with the appropriate values – these are the same ones you used to connect to SQL Azure via your local copy of SQL 2008 R2. If you want to know more about the BCP arguments feel free to Google away. I don’t want to get into them too much here other than to say this script is going to take the data from the User table’s BCP file and insert it into the SQL Azure Database using the credentials supplied and insert the data in batches of 10,000. So if you have 25,000 records it’ll run three batches to get all of the records inserted.
  7. Once you’ve done this to every BCP file entry you’re ready to go
  8. Open up a command-line prompt, navigate to the “.bat” file and execute it.
  9. You’ll probably want to occupy yourself for a few minutes while it runs, and once it completes you’ll end up back at a flashing command prompt. It’s relative easy to spot the obvious errors by scrolling back up the list and looking for an execution command that has some related exception. If so you’ll need to address it before proceeding.

If you’re reached this point then congratulations, you’re almost there.

4. Quick Compare

There are a few ways to determine whether or not your database structure and data are intact after running the above steps, Red Gate has some great tools but I wanted to accommodate those readers on a limited budget.

So to do a quick check to ensure you haven’t missed any data insertions use these scripts, the first returns a list of all tables in your local database, and the total number of rows for each. And the latter does the same but for your SQL Azure database – so it’s a simple way of checking that no data was left behind. Hopefully you’ll have the same number tables, and the same numbers of rows in each table.

-- SQL 2008 R2
    ST.NAME AS [TableName],
    sys.tables AS ST
    INNER JOIN sysindexes AS SI ON (ST.object_id = AND SI.indid < 2)

-- SQL Azure
SELECT as TableName,
    ddps.row_count as [RowCount]
    sys.objects so
    JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID
    JOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID AND si.index_id = ddps.index_id
    si.index_id < 2 AND so.is_ms_shipped = 0

To check the structure I used Visual Studio 2011’s Beta SQL Compare feature. It’s free and all you have to do is point it at both databases and run a compare. Thanks Microsoft.

5. Final Steps

The final test is to fire up the application and ensure the data appears and behaves as expected. This unfortunately requires a fair bit of integration testing but hopefully you’ll feel a bit more reassured after the previous step’s results show no comparison problems.

Finally don’t forget to clean up any BCP files and/or firewall rules that no longer need to be used.

Good luck.

Author: Bernie

I currently live and work in the UK, just outside of London. I've been working in IT for 15+ years and in that time have solved many technical problems via blogs, forums, tutorials etc. and feel like I should be giving something back. This blog post is my way of contributing and I hope it proves just as useful to others as their contributions have to me.

One thought on “SQL 2005 to SQL Azure Migration

  1. Step 3.5:
    “Now here comes the slightly frustrating part, you want to update every line remaining so that this:

    — BCPArgs:534723:[dbo].[User] in “c:\SQLAzureMW\BCPData\dbo.User.dat” -E -n -b 10000 -a 16384

    … is converted to this…

    bcp [MyAzureDatabase].[dbo].[User] in “C:\SQLAzureMW\BCPData\dbo.User.dat” -E -n -b 10000 -a 16384 -U {{:User ID}} -S {{:Server}} -P {{:Password}}


    Here’s a regex replace to make that easier..

    \-\- BCPArgs\:\d+\:(.*?)\r\n
    bcp [{{:DatabaseName}}].$1 -U {{:User ID}} -S {{:Server}} -P {{:Password}}\r\n

    Now just replace the variables.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s