Migrating Databases to Azure SQL Database (formerly SQL Azure)
Updated: December 28, 2012
To migrate an existing SQL Server database to Microsoft Azure SQL Database, you export the database's schema and then transfer the data from the database to Microsoft Azure SQL Database.
This topic provides preliminary information on various migration techniques and tools that you can use while migrating databases from SQL Server to SQL Azure. For detailed information on migration, see Migrating SQL Server Databases to Azure SQL Database and Choosing Tools to Migrate a Database to Azure SQL Database in the Migrating Data-Centric Applications to Azure guide.
Migrating Databases to SQL Database by Generating a Script of an Existing Database
To migrate an existing SQL Server database to Azure SQL Database you can export the schema as a Transact-SQL script and then execute that script against Microsoft Azure SQL Database as follows:
Export a script from SQL Server Management Studio and set the appropriate export options. Because Microsoft Azure SQL Database supports a subset of features found in SQL Server, you may have to make some modifications to the script before you are able to execute it in the cloud.
Execute the script to create the schema in Microsoft Azure SQL Database.
You can use the Generate and Publish Scripts Wizard to transfer a database from a local computer to Microsoft Azure SQL Database. The Generate and Publish Scripts Wizard creates Transact-SQL scripts for your local database. The wizard uses them to publish database objects to Microsoft Azure SQL Database. For more information, see How to: Migrate a Database by Using the Generate Scripts Wizard (Azure SQL Database).
Migrating Databases to SQL Database Using Microsoft Sync Framework 2.1
Microsoft Sync Framework 2.1 provides synchronization capabilities between on-premise and Azure SQL Database servers, as well as between two or more Azure SQL Databases in the same or different data centers. Using Sync Framework 2.1, you can extend the schema and data within your SQL Server database or Azure SQL Database to Azure SQL Database data centers around the world to provide geo-available data access. For more information, see Synchronizing Azure SQL Database and Microsoft Sync Framework 2.1 Software Development Kit (SDK).
Migrating Databases to SQL Database by Using Data-tier Application Export and Import
You can perform a Data-tier Application (DAC) export to migrate both the definition of the objects in a database and the data from the user tables into a DAC export file (BACPAC). You can then copy the BACPAC file into the Azure blob storage service, and perform a DAC import to create a new database containing all of the objects and data. For more information, see How to: Import a Data-tier Application (Azure SQL Database).
Moving Data into SQL Database
Azure SQL Database supports the use of SQL Server Integration Services. SQL Server Integration Services is a convenient way to move data into and out of Azure SQL Database. Alternatively, you can transfer data to Azure SQL Database by using the bulk copy utility (bcp.exe).
SQL Server Integration Services
You can transfer data to Microsoft Azure SQL Database by using SQL Server 2008 Integration Services. In SQL Server 2008 R2 or later, the Import and Export Data Wizard provides support for Azure SQL Database . You can use this tool to migrate on-premise databases to Microsoft Azure SQL Database. For more information, see How to: Run the SQL Server Import and Export Wizard in SQL Server Books Online.
Migrating Databases to SQL Database by SQL Database Migration Wizard
The SQL Database Migration Wizard walks you through the selection of your SQL objects, creates SQL scripts suitable for Azure SQL Database, and allows you to migrate data between on-premise SQL Server 2005 or 2008 and Azure SQL Database servers, as well as between two or more Azure SQL Databases in the same or different data centers. For more information, see Azure SQL Database Migration Wizard on Codeplex.
|The SQL Database Migration Wizard tool is built by the community and not supported.|
The bcp Utility
The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. For more information, see bcp Utility in SQL Server Books Online.