Migrating Your SQL Server Database Applications to SQL Azure

Most business applications you write probably utilize some kind of relational database. And, as a .NET developer, the chances are that database is Microsoft SQL Server. This article walks you through migrating your SQL Server database applications to the cloud with SQL Azure.

For .NET developers, Microsoft SQL Server is a database engine that powers
many, many applications. This is not without a reason: Microsoft has gone
to great lengths to make SQL Server easy to use, administer and access from
Windows operating systems in general and .NET applications in particular.

Today, the development community is speaking about the cloud, and in Microsoft
circles, this means Azure. Since Azure now supports SQL Azure, the SQL Server
version in the clouds, the question quickly becomes: should I be using SQL
Azure to power my applications? There are many benefits in using the cloud,
but then again, it is no silver bullet. For each individual application,
you need to analyze whether using a cloud database is beneficial.

In this article, you are going to learn how you can start migrating your
database applications to SQL Azure on the cloud. The assumption is that you
have a .NET application that is already using Microsoft SQL Server as the
database, and a valid Azure subscription. Let’s start the discussion from
application code.

Code migration strategies

Once you have decided to investigate SQL Azure further and have calculated
that utilizing a cloud-based database might be useful for you, there are
two broad categories for migration. Firstly, you could keep your .NET application
running where it currently is (such as on the desktop or on your own web
servers) and just migrate the database to the cloud; or, you could be migrating
both the application and the database to the cloud.

Either way you choose to proceed, there are some changes that you need to
make to your application and the parts of code that works with databases.
If you are using modern layered architectures or patterns, such as the DAL/BLL/UI
pattern, then ideally, you would only need to change your DAL (Data Access
Layer). Similarly, for MVC and MVVM patterns, changing only the database
logic behind the model (“M”) should suffice.

Although SQL Azure can be seen as a cloud-based version of regular SQL Server
installed on your own servers (“on-premises”), there are technical differences
in these two platforms. The rule of thumb is that the more advanced features
you are using with your own SQL Server installation, the more likely you
are to run into features that are not (yet) supported by SQL Azure. For example,
certain indexing options, full text search and User-Defined Types (UDTs)
are not supported by SQL Azure (see Table 1).

The corollary of this is that if you are currently using SQL Server as a
plain data store by simply manipulating tables or views directly, then migrating
to SQL Azure is usually straightforward.

Table 1. Some SQL Server
features not currently supported by SQL Azure.

Change Data Capture
Common Language Runtime (CLR) features
Data Compression
External Key Management
FILESTREAM Data
Full-Text Search
Large User-Defined Types (UDTs)
Replication
Resource Governor
Sparse Columns
Table Partitioning
Transparent Data Encryption
Typed XML and XML indexing.

The amount of work needed to migrate also depends on the data access technologies
you use in your application. Generally speaking, classic ADO.NET data access
classes like SqlConnection and SqlCommand work with SQL Azure just as well
as with an on-premises installation of SQL Server. ADO.NET Entity Framework
and LINQ to SQL are also supported by Azure, and you can sometimes simply
change your connection string to point to SQL Azure instead of your own local
database server. Third party data-access components, if such are used, need
similar consideration.

Here is an example of classic ADO.NET code that uses the SqlConnection,
SqlCommand and SqlDataReader classes. This code migrates to SQL Azure by
just changing the connection string.

  string connStr = "Data Source=...";
SqlConnection conn = new SqlConnection(connStr);
try
{
    conn.Open();
    string sql = "SELECT * FROM customers";
    SqlCommand cmd = new SqlCommand(sql, conn);
    try
    {
        SqlDataReader reader =
            cmd.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                // work with the database data
            }
        }
        finally
        {
            reader.Dispose();
        }
    }
    finally
    {
        cmd.Dispose();
    }
}
finally
{
    conn.Dispose();
}

In a similar fashion, extensions and libraries – such as typed datasets
– that build on the SqlConnection and SqlCommand classes, are often easy
to migrate.

Migrating your data

Once you have evaluated the code changes, you have yet another (but equally
important) step to complete: you will need to move your database data to
SQL Azure. There are multiple options to complete this task. The following
discussion assumes your source database is SQL Server; if it is something
else such as Oracle, MySQL or DB/2, the options are still the same. However,
the tools you need are different.

With SQL Server, if you need to migrate small amounts of data on one-off
basis, then you could use SQL Server Management Studio (SSMS). The latest
SSMS version 2008 R2 (the R2 is important here) can directly connect to SQL
Azure database, and thus you could work as follows.

First, generate a script (Figure 1)
from your local source database, and be sure to include the table data in
the output. When this option is set, SQL Server Management Studio will create
the necessary INSERT statements in the resulting script to contain the table
data. Then, with the script ready, connect to your SQL Azure database and
run the script. Initially, you might get errors from non-supported features
(CREATE TABLE statements are a likely suspect), but after tweaking the script,
you should be good to go.

SQL Server
        Management Studio can create a script from your database with all the
        table data included.
Figure 1. SQL Server
Management Studio can create a script from your database with all the
table data included.

For instance, here is a basic CREATE TABLE command with a primary key defined
in SQL Server 2008:

CREATE TABLE [Customers](
  [CustomerID] [nchar](5) NOT NULL,
  ...
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
([CustomerID] ASC) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]) ON [PRIMARY]
 

However, such a statement fails with multiple errors in SQL Azure, because
not all indexing options are supported. After trial and error, the statement
becomes:

CREATE TABLE [Customers] (
  [CustomerID] [nchar](5) NOT NULL,
  ...
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
([CustomerID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF))

This works with SQL Azure.

This highly manual process involving SQL statement tweaking works if the
changes required are few and you only need to do it once. However, if you
want to automate the process, or better yet, synchronize data between your
local SQL Server installation and your cloud database, there are tools for
that.

For one thing, you could use the SQL Server’s bcp command, which stands
for bulk copy. This utility can copy large amounts of data from one SQL Server
database to another, and it works with SQL Azure, too. You could then automate
or even schedule this utility to run multiple times. Secondly, you could
visit CodePlex, Microsoft-funded open-source repository. CodePlex contains
a neat little project called SQLAzureMW, short for SQL Azure Migration Wizard,
which helps you in the migration process.

The third option is to actually synchronize the data using a suitable tool.
Microsoft is currently testing a product called SQL Azure Data Sync, which
supports bi-directional data synchronization. The product is, at this writing,
in CTP state and can be freely downloaded from Microsoft’s Azure web pages
(see the Links section for details).
Internally, Azure Data Sync uses Microsoft’s Sync Framework 2.0 for the brute
work. Figure 2 shows how the product
looks like.

SQL Azure
        Data Sync is a pre-release product aimed at synchronizing data between
        SQL Server and SQL Azure databases.
Figure 2. SQL Azure
Data Sync is a pre-release product aimed at synchronizing data between
SQL Server and SQL Azure databases.

Latest Articles