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:
Log in to your on-premises SQL Server database.
Generate a script for all the data definition objects, which include tables, views, indexes, and constraints (see Figure 1).
Modify the script to remove the features or commands not supported by SQL Azure.
NOTE
For commands not supported in SQL Azure, please refer to the SQL Azure documentation.
Save the script as a SQL Azure database definition script.
Connect to the SQL Azure database to which you want to migrate the data definition.
Open a New Query window, and copy and paste the content of the SQL Azure database script into it.
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
NOTE
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.
