Migrating Your SQL Server Database Applications to SQL Azure

Wednesday Sep 22nd 2010 by Jani Järvinen
Share:

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.

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.

Setting up an SQL Azure database

If you don’t yet have experience in working with SQL Azure, a good way to start learning is with a prototype database on the cloud. If you already have an Azure subscription for instance through your MSDN subscription (MSDN subscription Azure benefits were recently increased from eight to 16 months), go ahead and log in to SQL Azure.  The web portal address is http://sql.azure.com/.

Once you have signed in (follow the link at the top of the page), navigate to the “SQL Azure” part of the service (the link on the left), and if you already haven’t, create yourself a project. Then, click the project name, and you will be taken to a page that displays your databases (Figure 3).

Click for larger image

The SQL Azure
web management portal at sql.azure.com
Figure 3. The SQL Azure web management portal at sql.azure.com.

At this point, click the Create Database button. This will show a “dialog box” on the screen, asking for the name and maximum size of your database (Figure 4). Remember that you pay for what you use: a larger database is more costly than a smaller one, and two databases cost twice the price of one. Thus, proceed with care.

Creating a
    new database requires you to give a name and select the database size.
Figure 4. Creating a new database requires you to give a name and select the database size.

Once you have created your database inside SQL Azure, it is important to remember to configure firewall settings. By default, the firewall in the Azure computing centre is restrictive, and does not allow any connections to the TCP port 1433. You can however allow any range of IP addresses, and at first, this would probably be your own development machine. Remember that you must type in your public IP address as given by your ISP, and not your internal IP address. You are now working with a public server, after all.

After the SQL Azure is created and you have enabled access to it, you should be able to connect to your Azure database using either SQL Server’s Management Studio or Visual Studio’s Server Explorer (Figure 5). Remember that you will need to have SQL Server Management Studio version 2008 R2 or Visual Studio 2010 for this to work. Earlier versions of SQL Server Management Studio will report errors if you try to connect to SQL Azure.

Connecting
    to SQL Azure with SQL Server Management Studio.
Figure 5. Connecting to SQL Azure with SQL Server Management Studio.

With an available connection with either of these tools, go ahead and create some tables that your current application requires. At first, you might not need all tables. Instead, focus on the initial or core tables, and then work your way onwards from there.

Testing your application with an Azure database

In an ideal world, migrating your database application to use SQL Azure would be a two-step process: first, migrate your data to Azure, and then change the connection string in your application to point to the cloud database. In reality however, things usually are not so simple.

Even so, one strategy that works especially with smaller applications is to dive head-first into Azure. Go ahead and create some tables in your Azure database, and then point your application to your cloud database. Record the error messages, and start working from there.

For larger applications, it is better to plan more in advance. During a database code review, you should aim at identifying code parts that use features in SQL Server that are not available in Azure. On MSDN, there is a topic called “SQL Server Feature Limitations” that lists features not supported by SQL Azure (see again Table 1). With the non-supported code blocks marked, you should work to change your code so that it becomes compatible.

In larger applications, such changes might, depending on code complexity and coupling, easily cause chain-effects that require editing of code on multiple levels. If this is the case, it might be worthwhile to create a new prototype application that only contains parts of the original code. With such a separate application, even larger code changes are usually much easier to manage. This is sometimes called the “divide and conquer” method.

In this method, the prototype application should initially have your database logic to barely connect to the Azure database. Once that works, you add more functionality from the original application. This process is repeated until you can see that the functionality of your original application starts to work. It doesn’t need to have full functionality, but enough so that you can see what things in the original application need to be changed.

Finally, with the lessons learned from the prototype application, you can apply the solutions you found to the real application. After some number of iterations in writing code, testing and debugging, you should in the end have an application that is compatible with SQL Azure. This is indeed a goal worth striving for.

Conclusion

Cloud based applications and databases are tempting to developers and users alike. For one thing, almost unlimited scalability and not having to worry about server maintenance are things that can save organizations big dollars. However, moving applications to the cloud does not always happen in a snap.

In this article, you learned strategies to migrate your database applications into Microsoft’s SQL Azure cloud database, and what kind of issues you might run into while doing so. Remember, that each application is different and must be treated individually. The rule of thumb is that the more modern and loosely-coupled your application architecture is in the beginning, the easier will be your task to move your SQL database to Azure.

This article walked you through different SQL Server migration options, and also showed how to get started with SQL Azure databases. Finally, you discovered compatibility issues that most commonly affect code compatibility when moving from SQL Server to SQL Azure.

The old proverb holds true: there’s a silver lining to every cloud.

Jani Järvinen

# # #

About the Author

Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP, a frequent author and has published three books about software development. He is the group leader of a Finnish software development expert group at ITpro.fi, former of a Finnish .NET user group and a board member of the Finnish Visual Studio ALM User Group. His blog can be found at http://www.saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.

# # #

Related Articles

Internet.com Cloud Computing Showcase Sponsored by Microsoft ...
Windows Azure Platform for Enterprises
Get Help Sponsored by Microsoft Windows Azure Platform

Links

SQL Azure management portal:
http://sql.azure.com/

SQL Server 2008 R2 Express:
http://www.microsoft.com/express/Database/

SQL Azure Migration Wizard v3.3.7:
http://sqlazuremw.codeplex.com/

Windows Azure Platform Benefits for MSDN Subscribers:
http://msdn.microsoft.com/en-us/subscriptions/ee461076.aspx

Microsoft SQL Azure Data Sync CTP:
http://www.microsoft.com/windowsazure/sqlazure/datasync/default.aspx

SQL Server Feature Limitations in SQL Azure:
http://msdn.microsoft.com/en-us/library/ff394115.aspx

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved