Modifica

Condividi tramite

Migration guide: SQL Server to Azure SQL Database

  • Articolo

Applies to: SQL Server Azure SQL Database

In this guide, you learn how to migrate your SQL Server instance to Azure SQL Database.

Review the Prerequisites, and complete the Pre-migration steps, before continuing.

Migrate

After you complete the steps for the pre-migration stage, you're ready to perform the schema and data migration.

Migrate your data using your chosen migration method.

Migrate using the Azure SQL migration extension for Azure Data Studio

To perform an offline migration using Azure Data Studio, follow the high-level steps below. For a detailed step-by-step tutorial, see Tutorial: Migrate SQL Server to Azure SQL Database (offline).

  1. Download and install Azure Data Studio and the Azure SQL migration extension.
  2. Launch the Migrate to Azure SQL Migration wizard in the extension in Azure Data Studio.
  3. Select databases for assessment and view migration readiness or issues (if any). Additionally, collect performance data and get right-sized Azure recommendation.
  4. Select your Azure account and your target Azure SQL Database from your subscription.
  5. Select the list of tables to migrate.
  6. Create a new Azure Database Migration Service using the wizard in Azure Data Studio. If you've previously created an Azure Database Migration Service using Azure Data Studio, you can reuse the same if desired.
  7. Optional: If your backups are on an on-premises network share, download and install self-hosted integration runtime on a machine that can connect to the source SQL Server, and the location containing the backup files.
  8. Start the database migration and monitor the progress in Azure Data Studio. You can also monitor the progress under the Azure Database Migration Service resource in Azure portal.

Data sync and cutover

When using migration options that continuously replicate / sync data changes from source to the target, the source data and schema can change and drift from the target. During data sync, ensure that all changes on the source are captured and applied to the target during the migration process.

After you verify that data is same on both the source and the target, you can cut over from the source to the target environment. It's important to plan the cutover process with business / application teams to ensure minimal interruption during cutover doesn't affect business continuity.

Important

For details on the specific steps associated with performing a cutover as part of migrations using DMS, see Tutorial: Migrate SQL Server to Azure SQL Database using DMS (classic).

Migrate using transactional replication

When you can't 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 availability groups, see Configure replication with Always On availability groups.

To use this solution, you configure your database in 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.

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

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.

Transaction replication workflow

Important

Use the latest version of SQL Server Management Studio to remain synchronized with updates to Azure and SQL Database. Older versions of SQL Server Management Studio can't set up SQL Database as a subscriber. Get the latest version of SQL Server Management Studio.

Step Method
Set up distribution SQL Server Management Studio | Transact-SQL
Create publication SQL Server Management Studio | Transact-SQL
Create subscription SQL Server Management Studio | Transact-SQL

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's complete, so schedule your snapshot appropriately.
  • Only push subscriptions are supported in Azure SQL Database. You can only add subscribers from the source database.

Migration recommendations

To speed up migration to Azure SQL Database, you should consider the following recommendations:

Resource contention Recommendation
Source (typically on premises) The primary bottleneck during migration from the source is data file I/O and latency, which needs to be monitored carefully. Based on data file I/O and latency, and depending on whether it's a virtual machine or physical server, you might have to engage your storage admin and explore options to mitigate the bottleneck.
Target (Azure SQL Database) The biggest limiting factor is the log generation rate and latency on your database log file. With Azure SQL Database, you can get a maximum log generation rate of 96 MB/s. To speed up migration, scale up the target Azure SQL database to Business Critical Gen5 8 vCore to get the maximum log generation rate of 96 MB/s, which also provides low latency for log files. The Hyperscale service tier provides a log rate of 100 MB/s regardless of chosen service level.
Network The network bandwidth needed is equal to the maximum log ingestion rate 96 MB/s (768 Mb/s) Depending on network connectivity from your on-premises data center to Azure, check your network bandwidth (typically Azure ExpressRoute) to accommodate for the maximum log ingestion rate.

You can also consider these recommendations for best performance during the migration 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.
  • If you use BACPAC files, minimize the distance between your BACPAC file and the destination data center.
  • Disable auto update and auto create 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 database in Azure SQL Database. You can then query this historical data using elastic queries.

Post-migration

After you have successfully completed the migration stage, go through the following post-migration tasks to ensure that everything is functioning smoothly and efficiently.

The post-migration phase is crucial for reconciling any data accuracy issues and verifying completeness, as well as addressing performance issues with the workload.

Update statistics

Update statistics with full scan after the migration is completed.

Remediate applications

After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this will, in some cases, require changes to the applications.

Perform tests

The test approach for database migration consists of the following activities:

  1. Develop validation tests: To test database migration, you need to use SQL queries. You must create the validation queries to run against both the source and the target databases. Your validation queries should cover the scope you have defined.
  2. Set up test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
  3. Run validation tests: Run the validation tests against the source and the target, and then analyze the results.
  4. Run performance tests: Run performance test against the source and the target, and then analyze and compare the results.

Use advanced features

Be sure to take advantage of the advanced cloud-based features offered by SQL Database, such as built-in high availability, threat detection, and monitoring and tuning your workload.

Some SQL Server features are only available once the database compatibility level is changed to the latest compatibility level.

To learn more, see managing Azure SQL Database after migration.

Resolve database migration compatibility issues

You might encounter a wide variety of compatibility issues, depending both on the version of SQL Server in the source database and the complexity of the database you're 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:

Important

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

Related content