How to migrate SQL server database to SQL Azure – a project experience

A project experience on how to migrate MS SQL server successfully to SQL Azure platform. What can be the major challenges and possible solutions? Here is a case study on Stylus System’s  successful database migration from MS SQL server to MS SQL Azure.

Database migration from SQL Server to SQL Azure may be easy in the case of small projects with less tables and about a dozen of stored procedures. But the real challenges arise while dealing with big projects with hundreds of tables, stored procedures, functions and triggers. At Stylus we experienced a similar scenario. In one of our project, the programming team were building an application which included many processes such as complex reporting, storing user data etc. We chose SQL Azure cloud platform, as it best helps in developing complex reports. But as the project advanced we faced a number of complex tasks which required not so obvious and simple approach and solutions.

Here we analyze the major challenges we faced and how the stylus team succeeded it!

SQL Azure is a cloud based relational database platform which enables users to host their data on cloud and use it as a service and pay as per usage. Since Microsoft takes care of installation of software, patching, and managing servers at platform level, it is categorized as Platform as a Service (PAAS) form of Cloud Computing. The recommended approach to running scheduled tasks on Azure is Web Jobs.

Database migration from MS SQL Server to MS SQL Azure platform is perfect for small data with relational requirements. But when it comes big projects, we have to really resize the data. At this point the execution may sometimes became a real brainteaser.

Usually there are three main approaches for migration.

  1. Generate and Publish Script Wizard in SQL Server Management Studio (SSMS)
  2. Use the SQL Azure Migration Wizard, a free open source solution on CodePlex
  3. Use SQL Azure Data Sync to generate your schema object and then synchronize the data

But whatever the method use choose, you must account for the fact that SQL Azure does not support all features in SQL Server. But worry not, these limitations never be a hindrance in choosing a particular migration but chances are more that it may impact your decisions on which method you chose.

Major challenges faced and the solutions implemented

Challenge 1: SQL Azure is a restricted version of SQL 2012. The initial challenge we faced was there was no design for tables and it does not support tables without clustered indexes. SQL Azure requires each table to contain a clustered index, otherwise you won’t be able to insert any data to it.

Solution: A clustered index is created before an insert operation is allowed on the table.

Challenge 2: SQL Azure does not support running SQL Server agent. If you have any tasks running at fixed time intervals, those would not be supported.

Solution: We managed to overcome this by moving the task to SQL Server Agent of the regular SQL Server.

Challenge 3: Another complicated issue sited is SQL Azure does not give database back up. In order to protect your data against user/application errors or a total loss.

Solution:  It is best practice to keep a Dacpac file up-to-date so that there are options that you can change the database file or to create a new one whenever required.

Challenge 4: Another major drawback noticed was the failure in viewing the data of one database through the context of the other. This makes it impossible to create views, which would sum up data from several databases and would thus be useful for instance for reports generation.This will end up in confusion while generating reports by pulling the data from different databases.

Solution: We successfully managed this by creating new schemas in the same database. This will make it possible to create views, which would sum up data from several databases and would thus be useful for instance for reports generation.

SQL Azure is best recommended for running web based jobs. It does support backups on some plans. Bur Microsoft Azure SQL Database has built-in backups to support self-service Point in Time Restore and Geo-Restore for Basic, Standard, and Premium service tiers. If you have Web or Business Edition databases, you must create your own backup strategy. You can use database copy or Import and Export services to create copies of the data and export the file to a Microsoft Azure storage account.

Though the migration looked complex at the initial stages, we successfully managed it through simple methods. From our experience we can say SQL Azure can be best not just for small projects, whereas it suits for large project migrations, but programmers must have a clear understanding of things/steps that should be well-thought-out before choosing Azure DB. It’s up to you to ensure a smooth and problem-free migration.

If you have any requirements of the same type to use SQL Azure to host your database, we are here to help you in solving your migration problems. Do talk to us.

Comments are closed.