When examining the feasibility of migrating business applications to the Cloud with Windows Azure, one of the primary concerns centers around data migration. Many IT Leaders will find themselves asking questions like: How difficult is data migration to Windows Azure SQL? Does Microsoft provide any tools to migrate data from on-premise to Cloud?
To answer these questions and others, let’s examine a few of the methods available to migrate data from an on-premise SQL Server DB to Windows Azure SQL. Microsoft provides several options to achieve this, which can be found at http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx. For the most part, there is little difference between migrating a SQL Server DB from one server to another and migrating to Windows Azure SQL. There are a few requirements enforced on the data tables in Windows Azure SQL, such as clustered indexes on all tables. This will allow you to create a table without a clustered index, but as soon as you try to insert a row, it will throw out an error prompting you to create a clustered index on the table. Therefore, prior to data migration, you need to ensure that all tables have a clustered index.
Among several choices, the two options that I have worked with and found to be simple, effective means of migrating data to Windows Azure SQL include: (1) Microsoft’s built in Export Data Wizard and (2) a community built tool called the SQL Database Migration Wizard. (Note: This is a community built tool; therefore, it is not supported by Microsoft).
When using the Export Data Wizard, there is little change in the process to export to Windows Azure SQL.
In Management Studio, right click on the database you want to migrate
Under ‘Tasks’ select ‘Export Data’ and confirm your data source, click ‘Next’
Select ‘.Net Framework Data Provider for SqlServer’ in the Destination dropdown, filling in with your Windows Azure SQL source and security information as shown in the image below
Continue through the dialogs and click ‘Finish.’ This will migrate the data to your Windows Azure SQL database. If you need more information about this process, check out this great article – http://social.technet.microsoft.com/wiki/contents/articles/2287.windows-azure-sql-database-data-migration-using-sql-server-import-and-export-wizard-en-us.aspx.
Another option available to you, although not covered by Microsoft support, is the community built SQL Database Migration Wizard, which can be found on CodePlex at http://sqlazuremw.codeplex.com/. The documentation section on CodePlex provides detailed step-by-step instructions on how to use the wizard, so I won’t cover that here. After walking through an easy to use dialog to create the database tables from your source data, the wizard utilizes the BCP Utility for data migration. It will also automatically add clustered indexes as needed on your tables to streamline the import process. (Note: The SQL Database Migration Wizard requires SQL Server 2008 R2 SP1 or later to run.)
The key takeaway is that migrating SQL data to Windows Azure SQL is not as imposing and difficult as you might think. With only a few minor quirks, it is largely the same as an on-premise migration.
Credera has experience performing extensive data migrations for both on-premise and Cloud environments. If you have questions about migrating to Windows Azure SQL or other questions about Microsoft development, please contact us for more information.
Contact Us
Ready to achieve your vision? We're here to help.
We'd love to start a conversation. Fill out the form and we'll connect you with the right person.
Searching for a new career?
View job openings
