Overview of Options for Migrating Data and Schema to Windows Azure SQL Database

Article
1/17/2024

This article provides information on the different methods of migrating data (including schema information,) to SQL Database. This article is primarily concerned with migrating information from SQL Server to SQL Database. For migrating Microsoft Access, MySQL, and other databases that are not SQL Server use the SQL Server Migration Assistant, which can be found here.

Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

Prerequisite

Windows Azure SQL Database is based on SQL Server 2008 (level 100). If you are migrating from an older database (level 80 or 90), you should go through the upgrade process first (at least in the development environment) and once on SQL Server 2008, then migrate to SQL Database.

If you are migrating SQL Server to SQL Database you need to be aware that these features of SQL Server are currently not supported by SQL Database:

Full Text Search
SQL Server Agent

There are workarounds to accomplish the above; however they need to be addressed before migration.

Using Data-tier Application Import and Export for SQL Database

The import and export services introduced in Microsoft SQL Server 2012 Data-tier Application Framework v2.0 Feature Pack CTP allow you to deploy and extract schema and data to SQL Database. This allows you to store and retrieve a full database in a single operation, and enable migration of data between multiple versions of SQL Server from SQL Server 2005 SP4 and above..

For more information on using Data-Tier Application Import and Export with SQL Database, see [[How to Use Data-Tier Application Import and Export with Windows Azure SQL Database]]. To begin using import and export services, see <labs page>.

Using BCP

One option for migrating an on-premise SQL Server to SQL Database is to use the
Generate Script Wizard in SQL Server Management Studio and a custom bcp utility command line script generated from Transact-SQL.

Generating the Schema

When moving an on-premise SQL Server database to SQL Database the first step is to generate a script that will transfer the schema of your SQL Server database to SQL Database. SQL Server Management Studio 2008 R2 easily does this with a newly added advanced setting that allows you to target SQL Database as you engine type. For the detailed steps, see [[How to Migrate Schema to SQL Database using the Generate SQL Server Scripts Wizard]].

In order to use SSIS or BCP to transfer your SQL Server data to SQL Database you need to have the schema is place on SQL Database including your clustered indexes. Another option to upload data is changing the settings on the Generate and Publish Scripts dialog to include data generation. INSERT statements will be added to the script increasing the overall size of the script – this only works well for small databases.

Transferring the Data

If you are migrating tables from SQL Server to SQL Database, one of the easiest ways is to use bcp utility to draw data out of your SQL Server into a file and then move the data from the file to SQL Database. However, it can be tedious to write all the bcp utility commands by hand, since bcp utility requires that you execute a single command for each table, moving one table’s worth of data at a time. Would it not be nice to move all the tables with a single batch file? Presented below Transact-SQL script that will create a batch file with all the bcp utility commands you need to move a whole database.

Preliminaries

Before you run the script below:

The tables on the SQL Database destination database should be empty, which means that you shouldn’t run the BCP utility batch file twice on the same destination database.
The script below runs in SQL Server Management Studio connected to the source SQL Server database. You will need to modify the variables at the top of the script to reflect your source SQL Server and destination SQL Database server, before you execute the script.

The Transact-SQL script will generate the commands for a batch file using Transact-SQL PRINT statements. After executing the script, just copy the whole output to a file with a .bat extension. Once you have the batch file create you can run it from the command line to facilitate the move of your database.

Preserving Primary Keys

If you are using IDENTITY to generate the primary keys in your database, the bcp utility commands generated will preserve the numbering of your primary keys using the –E flag. However, the referential integrity of the foreign keys will not be checked when they are inserted. This was done so that rows could be inserted regardless of the dependencies amongst the tables – primary keys do not need to be inserted before foreign keys.

Because the primary keys are not regenerated there should not be any constraints violated as long as the source database is not written too while the batch file is running. Here lies the hitch, you need to make sure that your source database is either in read only mode, or that no application is writing data to it. The bcp utility commands are not wrapped inside a big transaction. There can be significant time between the time the first command in the batch file is executed and the last, this gives an opportunity for the data writes.

The Script

SET NOCOUNT ON

DECLARE @DestServer nvarchar(max)
DECLARE @DestDatabase nvarchar(max)
DECLARE @DestLogin nvarchar(max)
DECLARE @DestPassword nvarchar(max)

DECLARE @SrcServer nvarchar(max)
DECLARE @SrcDatabase nvarchar(max)
DECLARE @SrcLogin nvarchar(max)
DECLARE @SrcPassword nvarchar(max)

-- SQL Database Server
SET        @DestServer = 'yourServer.database.windows.net'
SET        @DestDatabase = 'yourDatabase'
SET        @DestLogin = 'yourLogin@yourServer'
SET        @DestPassword = 'yourPassword'

-- SQL Server
SET        @SrcServer = 'yourServer'
SET        @SrcDatabase = 'yourDatabase'
SET        @SrcLogin = 'yourLogin'
SET        @SrcPassword = 'yourPassword'

PRINT 'echo %DATE% %TIME% > bcp.log'
PRINT ''

---------------------------------------------------

DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)

DECLARE table_cursor CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM information_schema.tables 
    WHERE Table_Type = 'BASE TABLE'
    OPEN table_cursor;

-- Perform the first fetch.
FETCH NEXT FROM table_cursor INTO @Schema, @Table;

-- Check @@FETCH_STATUS to see if there are any
-- more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT 'REM Download ' + @Schema + '.' + @Table
    PRINT 'echo bcp.exe ' + @SrcDatabase + '.' + @Schema + '.' +
        @Table + ' out ' + 
        @Schema + '.' + @Table + '.bcp -q -h "TABLOCK" -N -CRAW -S' +
        @SrcServer + ' >> bcp.log'
    PRINT 'bcp.exe ' + @SrcDatabase + '.' + @Schema + '.' + @Table + 
        ' out ' + @Schema + '.' + @Table + 
        '.bcp -q -h "TABLOCK" -N -CRAW -S' + @SrcServer + 
        ' -U' + @SrcLogin + ' -P' + @SrcPassword + ' >> bcp.log'
    PRINT ''

    PRINT 'REM Upload ' + @Schema + '.' + @Table
    PRINT 'echo bcp.exe ' + @DestDatabase + '.' + @Schema + '.' + 
        @Table + ' in ' + @Schema + '.' + @Table + 
        '.bcp -q -h "TABLOCK" -N -E -CRAW -S' + @DestServer +
        ' >> bcp.log'
    PRINT 'bcp.exe ' + @DestDatabase + '.' + @Schema + '.' +
         @Table + ' in ' + @Schema + '.' + @Table + 
         '.bcp -q -h "TABLOCK" -N -E -CRAW -S' + @DestServer + 
         ' -U' + @DestLogin + ' -P' + @DestPassword + ' >> bcp.log'
    PRINT ''
    
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM table_cursor  INTO @Schema, @Table;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;

Using SQL Server Integration Services

You can transfer data to SQL Database by using SQL Server 2008 Integration Services. You can use this tool to migrate on-premise databases to SQL Database. For more information, see How to: Run the SQL Server Import and Export Wizard in SQL Server Books Online. If you do decide SQL Server 2008 Integration Services, you for anything by the simplest tables you will need to construct your own data diagram to migrate primary and foreign keys correctly.

Using SQL Database Migration Wizard

The SQL Database Migration Wizard (found here) helps you migrate your local SQL Server 2005 / 2008 databases into SQL Database. There are two parts to the SQL Database Migration Wizard:

The Wizard: The wizard walks you through the selection of your SQL objects, creates SQL scripts suitable for SQL Database that you to migrate your data. SQL Database Migration Wizard does not catch all of the compatibility issues between different levels of SQL Server databases (i.e. level 80, 90, and 100).
The SQL Database Migration Wizard Batch is a command line application that will run a TSQL script file (and use BCP to upload data) against a target server. The Wizard can be used to generate the TSQL script and the BCP files that can then be used by batch for uploading to a target server.

Note: The SQL Database Migration Wizard is an open source tool built and supported by the community.

Using SQL Server Migration Assistant

If you are migrating from MySQL or Microsoft Access you should definitely use the SQL Server Migration Assistant.

SQL Server Migration Assistant (SSMA for MySQL v1.0) has been updated to support migration from MySQL to SQL Database. This release makes it possible to move data directly and easily from local MySQL databases into SQL Database, to help you better consolidate your disparate departmental or ad-hoc data into a centrally managed and highly scalable environment on SQL Database. The SQL Server Migration Assistant toolkits were designed to tackle the complex manual process customers deal with when migrating databases. By using the SQL Server Migration Assistants, customers and partners reduce the manual effort; as a result the time, cost and risks associated with migrating are significantly reduced.

SQL Server Migration Assistant (SSMA for Access v4.2) has been updated to support migrating your schema and data from Microsoft Access to SQL Database. This release makes it possible to move data directly and easily from local Microsoft Access databases. The SQL Server Migration Assistant toolkits were designed to tackle the complex manual process customer’s deal with when migrating databases. By using the SQL Server Migration Assistants, customers and partners reduce the manual effort; as a result the time, cost and risks associated with migrating are significantly reduced.

You now can “upsize” your data from the Access to the full relational database of SQL Database; by transitioning from the Access database to SQL Database (using the migration assistant), with all the benefits of SQL Database. Best of all, your existing Access application front ends can continue to be used with the data now residing in SQL Database . Access 2010 can connect transparently to SQL Database data (via linked tables), opening up opportunities for Access users and technology providers to create rich experiences while leveraging the benefits of the cloud on a “pay as you grow” elastic basis.

Share via