Migrating on premise SQL Server Database to off premise SQL Azure
Microsoft's answer to Relational data service on Cloud is SQL Azure, the immediate next question is how easy and seamless is to move my existing investment of on premise SQL Server database to SQL Azure. Of course, there are other questions related to cost, benefits, etc. but we will park them for now and possibly focus on only technical aspects of moving from SQL Server to SQL Azure in this blog.
To help migrate and synchronize data between SQL Server and SQL Azure, Microsoft has provided a nice utility called "SQL Azure Data Synch". It is currently in CTP2. It has lot to offer to simplify the effort of moving from SQL Server to SQL Azure and still lot is desired. It can also be used to synchronize two or multiple instances of SQL Server with SQL Azure databases.You can download the SQL Azure Data Synch from the link here
In the context of moving data from SQL Server to SQL Azure, SQL Azure Data Synch helps you copy/migrate database tables and data from SQL Server to SQL Azure. Primary key and check constraints of table are also copied. However importantly, it do not migrate foreign key constraints between the tables and database logic such as stored procedures, triggers.
If you need foreign keys, stored procedures, views, triggers you have to explicitly create the script for these objects in SQL Server and run in the target SQL Azure database. As such this is not difficult but I wonder why it needs to be done manually, may be Microsoft plans to get this feature by RTM, needs to wait and watch.
You can script the source database (SQL Server) objects by connecting using SQL Server 2008 R2 Management studio and then running the same script in target database (SQL Azure) using SQL Server 2008 R2 Management studio. The scripts in SQL Server can be created by choosing the SQL Server database, right click, select Tasks and then Generate scripts, the wizard pops up and takes you through.
SQL Server 2008 R2 Management studio is a nice tool which gives a side by side view of both on premise (SQL Server) and off premise (SQL Azure) database and helps you to operate.
I took a bit of pain to work with SQL Azure Data Synch CTP and figure out how to migrate from SQL Server to SQL Azure but later on found out it is well documented here with detail steps and if you are looking to do the same, this should help save your lot of time.
Happy Migrating your SQL Server databases to SQL Azure!!!
