This blog describes the various approaches you can use to migrate an on-premises SQL Server database to Azure SQL Database.
In this migration process you migrate both your schema and your data from the SQL Server database in your current environment into SQL Database, provided the existing database passes compatibility tests. Fortunately with SQL Database Version 12 (V12), there are very few remaining compatibility issues other than server-level and cross-database operations (see Azure SQL Database Transact-SQL differences, Azure SQL Database General Limitations and Guidelines, and Azure SQL Database resource limits). Databases and applications that rely on partially or unsupported functions will need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.
If not done already, create a server to host the database you wish to migrate, and then set up a firewall rule for it (see SQL Database tutorial: Create a SQL database in minutes using sample data and the Azure portal).
Note: If you are fairly confident your database is compatible and it is not large a large database, you can skip testing for compatibility and just use the SSMS Migration Wizard. If this wizard detects compatibility issues, errors will be displayed to the screen and the migration will not continue – you can then proceed to the steps below to test and fix the compatibility issues. Or, you can run the SQL Azure Migration Wizard and it can fix most compatibility issues (the few that remain) while creating the SQL Database, so this could be the only tool you need to use since it also migrates the data.
UPDATE: The SQL Server 2016 Upgrade Advisor Preview has an option to migrate from on-prem SQL Server to SQL Database (schema and data).
Test compatibility
First you will want to test for SQL Database compatibility issues before you start the migration process:
- SqlPackage is a command-prompt utility that will test for compatibility issues and generate a report containing detected compatibility issues. See Determine SQL Database compatibility using SqlPackage.exe
- The Export Data-tier Application Wizard in SQL Server management studio will display detected errors to the screen. See Determine SQL Database compatibility using SSMS
Fix compatibility issues
If there are any compatibility issues, you will need to fix them before proceeding with the migration by using:
- SQL Azure Migration Wizard, available on CodePlex, will generate a T-SQL script from the incompatible source database that is then transformed to make it compatible with the SQL Database. It will then connect to the SQL Database and execute the script on the target database. It also has options to analyze trace files to determine compatibility issues and the script can be generated with schema only or can include data in BCP format. It will also process the body of functions or stored procedures which is normally excluded from validation performed by SSDT (see next option), so it may find issues that might not otherwise be reported by SSDT alone. See the wizard in action via the video SQL Database Migration Wizard
- SQL Server Data Tools for Visual Studio (SSDT). Import the database schema into a Visual Studio database project for analysis. 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, resolve the errors in 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. You can then migrate this updated database
- SQL Server Management Studio (SSMS). Use SSMS to fix compatibility issues using various Transact-SQL commands, such as ALTER DATABASE
Migrate database
And lastly you will migrate the compatible SQL Server database to SQL Database. There are several migration methods for various scenarios. The method you choose depends upon your tolerance for downtime, the size and complexity of your SQL Server database, and your connectivity to the Microsoft Azure cloud.
- SSMS migration wizard. 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
- Export/Import BACPAC file. If you have connectivity challenges (no connectivity, low bandwidth, or timeout issues) and for medium to large databases, use a BACPAC file. With this method, you export the SQL Server schema and data to a BACPAC file using SSMS or using SqlPackage and then import the BACPAC file into SQL Database using SSMS or SqlPackage or the Azure Portal or PowerShell
- BACPAC and BCP. Use this option for much large databases to achieve greater parallelization for increases performance, albeit with greater complexity. With this method, migrate the schema and the data separately. Perform the following steps: 1) Export the schema only to a BACPAC file, 2) Import the schema only from the BACPAC File into SQL Database, 3) Use BCP to extract the data into flat files and then parallel load these files into Azure SQL Database
- Transactional replication. When you cannot afford to remove your SQL Server database from production while the migration is occurring, you can use SQL Server transactional replication as your migration solution. See Migrate SQL Server database to SQL Database using transactional replication
To migrate schema and data from on-prem SQL Server (or Azure SQL Database) to Azure SQL Data Warehouse, use the Data Warehouse Migration Utility (Preview).
There is also a SQL Server Migration Assistant for Oracle, Sybase, DB2, MySQL, and Microsoft Access to migrate from SQL Server to Azure SQL Database.
More info:
Migration cookbook now available for the latest Azure SQL Database Update (V12)
Migrating a SQL Server database to Azure SQL Database
How to Migrate from On-Premises to Azure SQL Database
Migrating an on premise SQL Server Database to Azure
Free ebook: Microsoft Azure Essentials Migrating SQL Server Databases to Azure
