SQL Azure : Database-Migration Strategies

10/11/2010 2:12:48 PM

1. Data Definition Migration

The data definition refers to the design of your database schema, which may include storage-specific objects like tables, views, indexes, constraints, and so on. The data definition is tightly coupled to the type of data stored in the database to achieve optimal performance.

A particular database's data definition can be easily represented by a script that can be automatically generated in SQL Server Management Studio. With minor modifications, these scripts can be executed on SQL Azure to migrate the data definition from on-premises SQL Server to SQL Azure. So, other than execution tools like SQL Server Management Studio and SQLCMD, you don't need any specific tools to migrate data definition from on-premises SQL Server to SQL Azure when you have the script representing the data definition. This is the recommended approach to migrate data definitions from on-premises to SQL Azure, because this approach gives you more control over the definition of the data.

You can also use SSIS to replicate data definitions on the fly between an on-premises database and SQL Azure. But this approach may require more work in designing, building, testing, and deploying packages.

Typical steps required to migrate data definition from on-premises SQL Server to SQL Azure are as follows:

  1. Log in to your on-premises SQL Server database.

  2. Generate a script for all the data definition objects, which include tables, views, indexes, and constraints (see Figure 1).

    Figure 1. Generate a script.
  3. Modify the script to remove the features or commands not supported by SQL Azure.


    For commands not supported in SQL Azure, please refer to the SQL Azure documentation.

  4. Save the script as a SQL Azure database definition script.

  5. Connect to the SQL Azure database to which you want to migrate the data definition.

  6. Open a New Query window, and copy and paste the content of the SQL Azure database script into it.

  7. Execute the script to install the data definition objects in the SQL Azure database.

2. Data Migration

Data migration refers to the actual data stored in SQL Server. An on-premises SQL Server supports several tools to migrate data across different SQL Server instances as well as heterogeneous databases like Oracle, DB2, Access, and so on. Some of the popular data-migration tools are as follows:

  • SQL Server BCP Utility: Used for bulk copying data between SQL Server instances and/or file systems

  • SQL Server Management Studio: Used to back up and restore SQL Server databases

  • Database mirroring: Supports real-time data mirroring across SQL Server databases

  • Log shipping: Used for real-time backup and restore functionality

  • Replication: Supports real-time data mirroring across SQL Server databases

  • SQL Server Integration Services (SSIS): Includes built-in backup and restore tasks that can be included in packages and executed in a standalone manner or coupled with other business logic


This isn't an exhaustive list but just the most commonly used tools that are included with SQL Server. Most companies use advanced data-replication and -migration tools built by Microsoft's partner companies. I discuss only the most popular out-of-the-box SQL Server tools that you can use for data migration.

Most of the tools from this list need both the source database and the destination database supporting the tool. As of this writing, other than SSIS and the BCP Utility (supported in future releases of SQL Azure), SQL Azure doesn't support any of these tools. Even within SSIS, some of the maintenance tasks aren't supported by SQL Azure, so your best option is to use tasks that support ADO.NET connections. The BCP tool is the simplest to use and the best option for quickly scripting and/or scheduling the data migration on a periodic basis. On the other hand, SSIS gives you the most flexibility because you can design workflows and/or data transformations within your data-migration package. The BCP Utility is the best option for simple, quick, no-code data migrations, whereas SSIS is the best option for data migrations involving workflows and/or transformations.

3. Business Logic Migration

In the simplest terms, the business logic refers to the logic that is applied to the data before it's stored in the database or retrieved from the database for viewing. The business logic may also consist of business rules applied to inbound as well as outbound data in specific conditions. In some distributed systems, the business logic is embedded in the middle tier; in other cases, it's embedded in stored procedures closer to the database. There are also some client/server systems where the business logic is embedded in the client tier. Microsoft Excel is a very good client example in which you can connect to a database and add business logic to the data retrieved in Excel.

When you're planning a database migration, migrating the business logic associated with the data is equally important. In cases where the business logic is programmed in stored procedures, you can follow the same procedure as in the data-definition migration discussed earlier. You can generate a script defining the stored procedures and execute the script in SQL Azure. SQL Azure doesn't support CLR stored procedures yet, so you have to reprogram the stored procedures in .NET middle-tier components and TSQL stored procedures.

When the business logic is embedded in the middle tier, you must identify the SQL Server–specific features used in the business logic and verify their supportability in SQL Azure. If they aren't supported, then you have to redesign an alternative.

Your business logic migration strategy will change depending on the tier that owns the business logic. Typically, in large scale enterprise systems, the business logic is programmed in the middle tier so multiple applications can share the same data and have their own business logic components. In these cases, migration may require a detailed analysis and planning exercise. In small- to medium-scale databases, the business logic tier is typically programmed in stored procedures and closer to the data. In these cases, if the business logic is in TSQL stored procedures, the process is easier—assuming the stored procedures access objects supported by SQL Azure. If the business logic is in CLR stored procedures, you need a detailed planning and analysis exercise similar to that used with middle-tier components.

4. Application Migration

All databases provide data-retrieval and -modification services to one or more applications that process inbound and outbound data from the database. Without applications, databases are simply silos of isolated data providing no value to the business. You don't need a database to create a silo of data; you can store the data in a file system, on tape, or on a storage area network in its raw format. Enterprises store data in databases to make it available to applications. Applications then retrieve data from the databases and present it to end users in a readable and business-friendly format.

When you're designing a strategy for a database migration to SQL Azure, you have to consider all the applications that are actively using the database and supporting business functions. In your migration strategy, you must design a business continuity plan in which the database is migrated to SQL Azure without affecting the business continuity of the applications and the database itself. In some cases, you may also have to migrate the applications to Windows Azure along with the database to SQL Azure. Business continuity is critical to enterprises, and all migration strategies must be designed so that application downtime is zero.

Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer