Skip to content

Latest commit

 

History

History
122 lines (87 loc) · 11.4 KB

sql-database-cloud-migrate.md

File metadata and controls

122 lines (87 loc) · 11.4 KB
title description keywords services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
SQL Server database migration to Azure SQL Database | Microsoft Docs
Learn how about SQL Server database migration to Azure SQL Database in the cloud.
database migration,sql server database migration,database migration tools,migrate database,migrate sql database
sql-database
sql-database
migration
conceptual
CarlRabeler
carlrab
craigg
10/15/2018

SQL Server database migration to Azure SQL Database

In this article, you learn about the primary methods for migrating a SQL Server 2005 or later database to a single or pooled database in Azure SQL Database. For information on migrating to a Managed Instance, see Migrate to SQL Server instance to Azure SQL Database Managed Instance.

Migrate to a single database or a pooled database

There are two primary methods for migrating a SQL Server 2005 or later database to a single or pooled database in Azure SQL Database. The first method is simpler but requires some, possibly substantial, downtime during the migration. The second method is more complex, but substantially eliminates downtime during the migration.

In both cases, you need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL Database V12 is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

Note

To migrate a non-SQL Server database, including Microsoft Access, Sybase, MySQL Oracle, and DB2 to Azure SQL Database, see SQL Server Migration Assistant.

Method 1: Migration with downtime during the migration

Use this method to migrate to a single or a pooled database if you can afford some downtime or you are performing a test migration of a production database for later migration. For a tutorial, see Migrate a SQL Server database.

The following list contains the general workflow for a SQL Server database migration of a single or a pooled database using this method. For migration to Managed Instance, see Migration to a Managed Instance.

VSSSDT migration diagram

  1. Assess the database for compatibility by using the latest version of the Data Migration Assistant (DMA).
  2. Prepare any necessary fixes as Transact-SQL scripts.
  3. Make a transactionally consistent copy of the source database being migrated or halt new transactions from occurring in the source database while migration is occurring. Methods to accomplish this latter option include disabling client connectivity or creating a database snapshot. After migration, you may be able to use transactional replication to update the migrated databases with changes that occur after the cutoff point for the migration. See Migrate using Transactional Migration.
  4. Deploy the Transact-SQL scripts to apply the fixes to the database copy.
  5. Migrate the database copy to a new Azure SQL Database by using the Data Migration Assistant.

Note

Rather than using DMA, you can also use a BACPAC file. See Import a BACPAC file to a new Azure SQL Database.

Optimizing data transfer performance during migration

The following list contains recommendations for best performance during the import process.

  • Choose the highest service tier and compute size that your budget allows to maximize the transfer performance. You can scale down after the migration completes to save money.
  • Minimize the distance between your BACPAC file and the destination data center.
  • Disable auto-statistics during migration
  • Partition tables and indexes
  • Drop indexed views, and recreate them once finished
  • Remove rarely queried historical data to another database and migrate this historical data to a separate Azure SQL database. You can then query this historical data using elastic queries.

Optimize performance after the migration completes

Update statistics with full scan after the migration is completed.

Method 2: Use 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. To use this method, the source database must meet the requirements for transactional replication and be compatible for Azure SQL Database. For information about SQL replication with Always On, see Configure Replication for Always On Availability Groups (SQL Server).

To use this solution, you configure your Azure SQL Database as a subscriber to the SQL Server instance that you wish to migrate. The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue occur.

With transactional replication, all changes to your data or schema show up in your Azure SQL Database. Once the synchronization is complete and you are ready to migrate, change the connection string of your applications to point them to your Azure SQL Database. Once transactional replication drains any changes left on your source database and all your applications point to Azure DB, you can uninstall transactional replication. Your Azure SQL Database is now your production system.

SeedCloudTR diagram

Tip

You can also use transactional replication to migrate a subset of your source database. The publication that you replicate to Azure SQL Database can be limited to a subset of the tables in the database being replicated. For each table being replicated, you can limit the data to a subset of the rows and/or a subset of the columns.

Migration to SQL Database using Transaction Replication workflow

Important

Use the latest version of SQL Server Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database. Older versions of SQL Server Management Studio cannot set up SQL Database as a subscriber. Update SQL Server Management Studio.

  1. Set up Distribution

  2. Create Publication

  3. Create Subscription

Some tips and differences for migrating to SQL Database

  • Use a local distributor
    • Doing so causes a performance impact on the server.
    • If the performance impact is unacceptable, you can use another server but it adds complexity in management and administration.
  • When selecting a snapshot folder, make sure the folder you select is large enough to hold a BCP of every table you want to replicate.
  • Snapshot creation locks the associated tables until it is complete, so schedule your snapshot appropriately.
  • Only push subscriptions are supported in Azure SQL Database. You can only add subscribers from the source database.

Resolving database migration compatibility issues

There are a wide variety of compatibility issues that you might encounter, depending both on the version of SQL Server in the source database and the complexity of the database you are migrating. Older versions of SQL Server have more compatibility issues. Use the following resources, in addition to a targeted Internet search using your search engine of choices:

In addition to searching the Internet and using these resources, use the MSDN SQL Server community forums or StackOverflow.

Important

SQL Database Managed Instance enables you to migrate an existing SQL Server instance and its databases with minimal to no compatibility issues. See What is an Managed Instance.

Next steps