How to Migrate your Database to the Cloud Using SQL Azure

Introduction To Database Migration

As interest grows amongst corporate developers to move their applications to the cloud, there is also a corresponding demand to move the corporate data from on-premise servers to cloud based offerings. SQL Azure is Microsoft's cloud solution for data storage.

To move an on-premise database to SQL Azure, one needs to do the following:

• Modify the Database definition language
• Move data using
  • BCP
  • SQL Server Integration Services

Since SQL Azure is based upon SQL Server technology, the conversion from on-premise to online is very simple.

Getting Started

To get started, visit your Windows Azure management portal. If you have not already signed up for SQL Azure subscription, you can refer to my article "Introduction to SQL Azure" and sign up for a SQL Azure account suitable for your needs.

Once you have provisioned a server, you can setup firewall rules to allow access to your SQL Azure server from your IP address. By default, not even Microsoft Services will have access to your SQL Azure server.

If you have not already checked off the option to allow other Windows Azure services to access the SQL Azure server, please do so now.

Let's assume our database has one table with the following structure:

ID (type Int), Identity, Required, Primary Key
 
Title, nvarchar (50)
 
Year (int)

1. We can generate the DDL of our database from SQL Server Management Studio by connecting to our database and selecting Tasks->Generate Scripts. Click Next and Choose objects > Select specific database objects and then choose Tables, Views and User-defined functions.

   Choose the table you want to create the DDL for and click Next. Under Advanced Scripting options, make sure you select "SQL Azure database" for the "Script for the database engine type".

   Choose to save the scripts at a specific location and select "Finish" to generate the scripts.

2. Now, we execute the DDLs against SQL Azure to define the database and tables in the cloud database. We can simply connect to the SQL Azure database from the SQL Server management Studio.

   Once connected, you can execute the script generated earlier to create the objects in the SQL Azure database.

3. You should now disable foreign key constraints and non-clustered indexes from your SQL Azure database now in the database schema.
4. Now, that the database schema for SQL Azure is ready, you can use bcp to export table data.

   The command you will use is:

   bcp Databasename.TableName out backup.dat -n -S ServerName -T
   

5. To import the data to SQL Azure using bcp, you will execute the following:

   Bcp dbo.TableName in backup.dat -n -S phqkmyv6ym.database.windows.net -Uadminuser@sqlAzure -PsqlAzurePassword -E
   

   Here phqkmyv6ym.database.windows.net is the SQL Azure server name, Adminuser is the Administrator user on the sql Azure server.
6. Once the bulk import succeeds, you can enable the foreign key constraints you disabled earlier (in step 3) and also rebuild your indexes.

Now, you have all your data migrated to the cloud.

Summary

In this article, we saw a walkthrough of migrating our on-premise data to the cloud. I hope you found this information to be useful.

About the author

Vipul Patel is a Software Engineer currently working at Microsoft Corporation. He is currently working in the Microsoft Lync team and has worked in the .NET team earlier in the Base Class libraries and the Debugging and Profiling team. He can be reached at vipul_d_patel@hotmail.com