Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 22, 2010

Migrating Your SQL Server Database Applications to SQL Azure

By Jani Järvinen

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM