How to: Use Integration Services to Migrate a Database to Windows Azure SQL Database
SQL Server Integration Services (SSIS) can be used when complex transformations of data are required to migrate a database from an on-premises instance of SQL Server to Windows Azure SQL Database.
Before you begin:
Recommendations, Limitations and Restrictions, Prerequisites
Use the SQL Server Integration Services to:
Migrate a Database, Analyze a Database, Analyze a Transact-SQL File, Analyze a Trace File
Author: Shaun Tinline-Jones
Before You Begin
SSIS can be used to perform a broad range of data migration tasks. SSIS provides support for complex workflow and data transformation between the source and destination. It is a good choice to transfer of data for databases that require many changes to work on Windows Azure SQL Database. You can use SSIS data transfer packages with another mechanism for transferring the database schema, such as a Data-tier Application package.
The most powerful use of SSIS is to perform complex transformations for migrations that require significant schema changes. In these projects, it is best to use another mechanism to manage the development of the new schema (such as using SQL Server Data Tools). But use SSIS data transfer packages to handle transforming the source data into the format specified for the destination database. While SSIS provides package types for transferring schema information, they are most useful when there are no changes between the source and destination databases. Another time to consider using SSIS is when you must optimize the data load time to fit within the cutover window for the project.
The SSIS Import/Export Wizard can be quickly used to create packages that move data from a single data source to a destination with no transformations. You can use the wizard to generate basic packages that map data from a source table to its destination. You can then edit the package to add robust error handling and retry logic.
The SSIS ADO.NET adapter supports SQL Database. It provides an option to bulk load data specifically for Windows Azure SQL Database. Use the SSIS ADO.NET Destination adapter to transfer data to Windows Azure SQL Database.
For each Windows Azure SQL Database ADO.NET destination, make sure to use the Use Bulk Insert when possible option. That allows you to use bulk load capabilities to improve the transfer performance. Another way to improve performance is to split source data into multiple files on the file system. In SSIS Designer, you can reference the files using the Flat File Component.
Limitations and Restrictions
SSIS is not available as a Windows Azure service similar to Windows Azure SQL Database. You can run SSIS packages on an on-premises instance of SQL Server to transfer data to Windows Azure SQL Database.
A package might fail due to throttling or network issues. Design packages so that they can be resumed at the point of failure, without redoing all the work that completed before the failure.
Connecting to Windows Azure SQL Database by using OLEDB is not supported.
The version of SQL Server Integration Services in SQL Server 2008 R2 or later support Windows Azure SQL Database.
Migrate a Database by Using SQL Server Integration Services
The following is a screenshot for configuring the ADO.NET Connection to Windows Azure SQL Database: