Azure SQL Database V12 brings near-complete engine compatibility with SQL Server 2014 and SQL Server 2016. For compatible databases, migration to Azure SQL Database is a straightforward schema and data movement operation requiring few, if any, changes to the schema and little or no re-engineering of applications. Where databases need to be changed, the scope of these changes is more confined than with Azure SQL Database V11.
By design, server-scoped features of SQL Server are not supported by Azure SQL Database V12. Databases and applications that rely on these features will need some re-engineering before they can be migrated.
[AZURE.NOTE] To migrate other types of databases, including Microsoft Access, Sybase, MySQL Oracle, and DB2 to Azure SQL Database, see SQL Server Migration Assistant.
The workflow for migrating a SQL Server database to Azure SQL Database are:
- Determine if your database is compatible
- If not compatible, fix database compatibility issues
- Migrate a compatible database
There are two primary methods to use to determine if your source database is compatible.
- Export Data Tier Application: This method uses a wizard in Management Studio and displays error messages on the console.
- SQLPackage.exe: sqlpackage.exe is a command-line utility that ships with Visual Studio and SQL Server. This method will generate a report.
[AZURE.NOTE] There is a third method, that will also use trace files to test for compatibility. This is SQL Azure Migration wizard, a free tool on Codeplex. However, this tool currently may find compatibility errors that were issues for Azure SQL Database V11 that are not issues for Azure SQL Database V12.
If database incompatibilities are detected, you will need to fix these incompatibilities before you can migrate your database to Azure SQL Database. For guidance on how to fix database compatibility issues, go to fix database compatibility issues.
[AZURE.IMPORTANT] These options do not catch all of the compatibility issues between different levels of SQL Server databases (i.e. level 90, 100, and 110). If you are migrating from an older database (level 80, 90, 100, and 110), you should go through the upgrade process first (at least in the dev environment) and once on SQL Server 2014 or later, then migrate to Azure SQL Database.
-
Open a command prompt and change a directory containing the newest version of sqlpackage.exe. This utility ships with both Visual Studio and SQL Server.
-
Execute the following command, substituting for the following arguments: < server_name >, < database_name >, < target_file >, < schema_name.table_name > and < output_file >. The reason for the /p:TableName argument is that we only want to test for database compability for export to Azure SQL DB V12 rather than export the data from all tables. Unfortunately, the export argument for sqlpackage.exe does not support extracting no tables, so you will need to specify a single small table. The < output_file > will contain the report of any errors.
'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file > /p:TableData=< schema_name.table_name > > < output_file > 2>&1'
-
Open the output file and review the compatibility errors, if any. For guidance on how to fix database compatibility issues, go to fix database compatibility issues.
-
Verify that you have version 13.0.600.65 or later of SQL Server Management Studio. New versions of Management Studio are updated monthly to remain in sync with updates to the Azure portal.
[AZURE.IMPORTANT] Download the latest version of SQL Server Management Studio. It is recommended that you always use the latest version of Management Studio.
-
Open Management Studio and connect to your source database in Object Explorer.
-
Right-click the source database in the Object Explorer, point to Tasks, and click Export Data-Tier Application…
-
In the export wizard, on the Settings tab, configure the export to save the BACPAC file to either a local disk location or to an Azure blob. A BACPAC file will only be saved if you have no database compatibility issues. If there are compatibility issues, they will be displayed on the console.
-
Click the Advanced tab and clear the Select All checkbox to skip exporting data. Our goal at this point is only to test for compatibility.
-
Click Next and then click Finish. Database compatibility issues, if any, will appear after the wizard validates the schema.
-
If no errors appear, your database is compatible and you are ready to migrate. If you have errors, you will need to fix them. To see the errors, click Error for Validating schema. For how to fix these errors, go to fix database compatibility issues.
- For small to medium databases, migrating a compatible SQL Server 2005 or later database is as simple as running the Deploy Database to Microsoft Azure Database Wizard in SQL Server Management Studio. If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues), you can use a BACPAC to migrate a SQL Server database to Azure SQL Database.
- For medium to large databases or when you have connectivity challenges, use a BACPAC to migrate a SQL Server database to Azure SQL Database. With this method, you use SQL Server Management Studio to export the data and schema to a BACPAC file (stored locally or in an Azure blob) and then import the BACPAC file into your Azure SQL instance. If you store the BACPAC in an Azure blob, you can also import the BACPAC file from within the Azure portal or using PowerShell.
- For larger databases, you will achieve the best performance by migrating the schema and the data separately. With this method, you script the schema using SQL Server Management Studio or create a database project in Visual Studio and then deploy the schema to Azure SQL Database. After the schema has been imported into Azure SQL Database, you then use BCP to extract the data into flat files and then import these files into Azure SQL Database.
The Deploy Database to Microsoft Azure Database wizard in SQL Server Management Studio migrates a migrating a compatible SQL Server 2005 or later database directly to your Azure SQL logical server instance.
[AZURE.NOTE] The steps below assume that you have already provisioned your Azure SQL logical instance and have the connection information on hand.
-
Verify that you have version 13.0.600.65 or later of SQL Server Management Studio. New versions of Management Studio are updated monthly to remain in sync with updates to the Azure portal.
[AZURE.IMPORTANT] Download the latest version of SQL Server Management Studio. It is recommended that you always use the latest version of Management Studio.
-
Open Management Studio and connect to your source database in Object Explorer.
-
Right-click the source database in the Object Explorer, point to Tasks, and click Deploy Database to Microsoft Azure SQL Database…
-
In the deployment wizard, configure the connection to your Azure SQL Database server.
-
Provide the New database name for the database on Azure SQL DB, set the Edition of Microsoft Azure SQL Database (service tier), Maximum database size, Service Objective (performance level), and Temporary file name for the BACPAC file that this wizard creates during the migration process. See Azure SQL Database service tiers for more information on service tiers and performance levels.
-
Complete the wizard to migrate the database. Depending on the size and complexity of the database, deployment may take from a few minutes to many hours.
-
Using Object Explorer, connect to your migrated database in your Azure SQL Database server.
-
Using the Azure Portal, view your database and its properties.
For medium to large databases or when you have connectivity challenges, you can separate the migration process into two discrete steps. You can export of the schema and its data into a BACPAC file using one or two methods.
You can store this BACPAC locally or in an Azure blob. You can then import this BACPAC file into Azure SQL Database using one of several methods.
- Import from a BACPAC file into Azure SQL Database using SQL Server Management Studio
- Import from a BACPAC file into Azure SQL Database using SqlPackage
- Import from a BACPAC file into Azure SQL Database using the Azure portal
- Import from a BACPAC file into Azure SQL Database using or PowerShell
Use the steps below to use Management Studio to export a migrating a compatible SQL Server database to a BACPAC file.
-
Verify that you have version 13.0.600.65 or later of SQL Server Management Studio. New versions of Management Studio are updated monthly to remain in sync with updates to the Azure portal.
[AZURE.IMPORTANT] Download the latest version of SQL Server Management Studio. It is recommended that you always use the latest version of Management Studio.
-
Open Management Studio and connect to your source database in Object Explorer.
-
Right-click the source database in the Object Explorer, point to Tasks, and click Export Data-Tier Application…
-
In the export wizard, configure the export to save the BACPAC file to either a local disk location or to an Azure blob. The exported BACPAC always includes the complete database schema and, by default, data from all the tables. Use the Advanced tab if you want to exclude data from some or all of the tables. You might, for example, choose to export only the data for reference tables rather than from all tables.
Use the steps below to use the SqlPackage.exe command line utility to export a migrating a compatible database to a BACPAC file.
[AZURE.NOTE] The steps below assume that you have already provisioned an Azure SQL Database server, have the connection information on hand, and have verified that your source database is compatible.
-
Open a command prompt and change a directory containing the sqlpackage.exe command line utility - this utility ships with both Visual Studio and SQL Server.
-
Execute the following command, substituting for the following arguments: < server_name >, < database_name >, and < target_file >.
'sqlpackage.exe /Action:Export /ssn:< server_name > /sdn:< database_name > /tf:< target_file >
Use the steps below to import from a BACPAC file into Azure SQL Database.
[AZURE.NOTE] The steps below assume that you have already provisioned your Azure SQL logical instance and have the connection information on hand.
-
Verify that you have version 13.0.600.65 or later of SQL Server Management Studio. New versions of Management Studio are updated monthly to remain in sync with updates to the Azure portal.
[AZURE.IMPORTANT] Download the latest version of SQL Server Management Studio. It is recommended that you always use the latest version of Management Studio.
-
Open Management Studio and connect to your source database in Object Explorer.
Once the BACPAC has been created, connect to your Azure SQL Database server, right-click the Databases folder and click Import Data-tier Application...
-
In the import wizard, choose the BACPAC file you just exported to create the new database in Azure SQL Database.
-
Provide the New database name for the database on Azure SQL DB, set the Edition of Microsoft Azure SQL Database (service tier), Maximum database size and Service Objective (performance level).
-
Click Next and then click Finish to import the BACPAC file into a new database in the Azure SQL Database server.
-
Using Object Explorer, connect to your migrated database in your Azure SQL Database server.
-
Using the Azure Portal, view your database and its properties.
Use the steps below to use the SqlPackage.exe command line utility to import a compatible SQL Server database (or Azure SQL database) from a BACPAC file.
[AZURE.NOTE] The steps below assume that you have already provisioned an Azure SQL Database server and have the connection information on hand.
-
Open a command prompt and change a directory containing the sqlpackage.exe command line utility - this utility ships with both Visual Studio and SQL Server.
-
Execute the following command, substituting for the following arguments: < server_name >, < database_name >, < user_name >, < password > , and < source_file >.
'sqlpackage.exe /Action:Import /tsn:< server_name > /tdn:< database_name > /tu:< user_name > /tp:< password > /sf:< target_file >
If you determine that your source SQL Server database is not compatible, you have a number of options to fix the database compatibility issues that you identified previously.
- Use the SQL Azure Migration wizard. You can use this Codeplex tool to generate a T-SQL script from an incompatible source database that is then transformed by the wizard to make it compatible with the SQL Database and then connect to Azure SQL Database to execute the script. This tool will also analyze trace files to determine compatiblity issues. The script can be generated with schema only or can include data in BCP format. Additional documentation, including step-by-step guidance is available on Codeplex at SQL Azure Migration wizard.
[AZURE.NOTE] Note that not all incompatible schema that can be detected by the wizard can be processed by its built-in transformations. Incompatible script that cannot be addressed will be reported as errors, with comments injected into the generated script. If many errors are detected, use either Visual Studio or SQL Server Management Studio to step through and fix each error that could not be fixed using the SQL Server Migration Wizard.
- Use Visual Studio. You can use Visual Studio to import the database schema into a Visual Studio database project for analysis. To analyze, you specify the target platform for the project as SQL Database V12 and then build the project. If the build is successful, the database is compatible. If the build fails, you can resolve the errors in SQL Server Data Tools for Visual Studio ("SSDT"). Once the project builds successfully, you can publish it back as a copy of the source database and then use the data compare feature in SSDT to copy the data from the source database to the Azure SQL V12 compatible database. This updated database is then deployed to Azure SQL Database using the options discussed previously.
[AZURE.NOTE] If schema-only migration is required, the schema can be published directly from Visual Studio directly to Azure SQL Database. Use this method when the database schema requires more changes than can be handled by the migration wizard alone.
- SQL Server Management Studio. You can fix the issues in Management Studio using various Transact-SQL commands, such as ALTER DATABASE.