Migrating data from on-premise SQL Server to SQL Azure

Abstract

SQL Azure is the cloud based relational database from Microsoft. Businesses can take advantage of SQL Azure which provides availability, scalability, and security of data without being really concerned about the infrastructure.

The key principles of SQL Azure are:

Managed Service - delivering information platform capabilities as fully managed services which consumers can consume, without having to worry about provisioning, deploying, and managing underlying hardware and software.

Scale on Demand - flexibility to scale out depending on needs and a pay-as-you-grow business model.

Introduction

Instead of maintaining our own server or machines, we can exploit the acres of internet-accessible servers. We can rely on service providers who maintain servers for our business data. Applications that run on desktops and mobile devices can use services in the cloud to synchronize information across many systems or in other ways. This is done exploiting the cloud’s capabilities. Microsoft’s Azure Services Platform is a group of cloud technologies, each providing a specific set of services to application developers.

Management portal

This is a Silverlight based Windows Azure platform (www.windows.azure.com) that provides access to service deployment and management tasks as well as at-a-glance status information that lets you know the overall health of your deployments and accounts. The Management Portal organizes the components of your Windows Azure deployments with constantly refreshed information that’s easy to discover and understand. Below is the figure of management portal.

Technique to upload on premise Database on SQL Azure

There are different ways to upload our on premise database into SQL Azure. From SQL Azure through 1433 port we can connect to the server of management portal. If you have an account in Azure you can upload your database into the cloud. Below are different ways to upload the DB in SQL Azure.

1. Generate Script wizard.
2. SQL bulk copy API and SQL Server Integration Service (SSIS).
3. Bulk Copy Function (SQL Azure model Wizard).

From the above 3 ways of the article covers the first 2 options.

Generate script wizard

1. Right click on database.



2. Click Next.



3. You will have two options. Select accordingly.



4. Select the script type follow the wizard.

In the Set Scripting Options page choose "Advanced". Set the script type to "Schema and Data" and "script Use Database" to False.



5. Progress status



6. Uploading Database to SQL Azure à all steps are below:

One of the basic steps in migrating an application to the cloud is uploading the database to the cloud. With Windows Azure Connect it is possible to bind the cloud application to a SQL server running on premise but this is not recommended. The reason is simple. Traditional SQL is running on a single server which by design does not scale and is not very reliable. The cloud is all about scalability, reliability and availability. Running your data on a single server might break that. I consider connecting on premise databases to Azure only as the first step in the migration process. The goal is to bring the data to the cloud.

7. Create a new empty database in SQL Azure.
1. Using SQL Management Studio 2008 R2 Create a script that creates the database.
2. Convert the script to be compatible with SQL Azure

In this post I created an empty database and called it pubs.

Access the SQL Azure DB from management studio using your credential with SQL Azure server details. Select the appropriate DB and run the script.

Click "Execute" and that is it!!! Refresh the database and make sure that all the data is here.

Common problems while porting SQL 2008 on-premise DB to SQL Azure

Problem 1: If you get error as shown below then you have to remove all ‘N’ from SQL SP Script. But if you generate the script from SQL 2008 R2 SP1 or SQL 2012 then it will work properly.

Problem 2: Another problem is that SQL Azure doesn’t support heap tables. A heap table is one without a clustered index. SQL Azure currently supports only clustered tables.

You need to make some changes for your script to run under SQL Azure. Here’s what to do:

1. Delete all instances of SET ANSI_NULLS ON.
2. Delete all instances of ON [PRIMARY].
3. Delete all instance of PAD_INDEX = OFF as well as

   ALLOW_ROW_LOCKS = ON

   and ALLOW_PAGE_LOCKS = ON.
4. In the Users table, modify the rowguid column, changing DEFAULT NEWSEQUENTIALID() to NULL.
5. In the stored procedure, remove the ENCRYPTION clause.
6. Add a clustered index to any heap tables.

There are some guidelines below which we have to keep in mind when we will upload the data using this option.

1. ON [PRIMARY] isn’t needed because, SQL Azure hides all hardware-specific access and information. There is no concept of PRIMARY or file groups because disk space is handled by Microsoft, so this option isn’t required.
2. According to SQL Server Books Online (BOL) you can remove the entire WITH clause that contains the table options. However, the only table options you really need to remove are those listed in above step 3 (PAD_INDEX, ALLOW_ROW_LOCKS, and ALLOW_PAGE_LOCKS).
3. The NEWSEQUENTIALID () function isn’t supported in SQL Azure because there is no CLR support in SQL Azure, and thus all CLR-based types aren’t supported. The NEWSEQUENTIALID () return value is one of those types. Also, the ENCRYPTION option isn’t supported because SQL Azure as a whole doesn’t yet support encryption.

SQL bulk copy API and SQL Server Integration Service (SSIS) Steps

1. Right click on DB.



2. Click Next.



3. Default it will display selected db with the below details. Don’t change anything if database in local system else select SQL server authentication and provide credential.



4. Give here your SQL Azure connection details with credential. Select destination “.NET Framework Data Provider for SQL Server”.



5. Select copy data from one or more tables or views then click next.



6. Here you can uncheck some table if you don’t want to export the data for a specific table.



7. Select the option like below and save the file in a path.



Here if we select “Run immediately” then it will start uploading data in SQL Azure. This process is known as “SQL Bulk Copy”. If you select “Save SSIS Package” then below are the pending steps which we have to complete.

1. 

2. At the end create a SSIS project steps below:



3. Delete the existing package and add this newly created package by right clicking on the add package and select add new item.
4. While selecting the package we have to make sure that we have selected the newly created package. See 7.1.

After adding new package successfully execute this package by right clicking on the new package. Then data will be upload from SSIS to SQL Azure DB.

Use Case

In this UseCase we took a database for uploading to SQL Azure. There are total 40 tables and 238 stored procedures and the database size is 599.13 MB. We have uploaded table schema and stored procedures. It took exactly  8 minutes 4 seconds with speed of 0.40 mbps using the generate script wizard (3.1).

Conclusion

Depending upon the database size we can chose one the above stated option if it is a large database then use SQL bulk copy API option. From the developer prospective there is no need of extra knowledge to upload the data on SQL Azure. There are various ways to upload the data from on-premise data to SQL Azure and also some tools are available like SQL Azure Migration Wizard which could help us to upload the data from on-premise to SQL Azure.

References

1. SQL Azure Tutorials - Helping you with Cloud Databases
2. http://social.technet.microsoft.com/wiki/contents/articles/windows-azure-and-sql-azure-tutorials.aspx
3. http://www.davidchappell.com/writing/white_papers/introducing_windows_azure_v1-chappell.pdf
4. http://sqlazuretutorials.com/wordpress/tag/azure-data-sync/
5. http://sqlazuremw.codeplex.com/releases/view/32334