Move resources to new region - Azure SQL Database

  • Article

Applies to: Azure SQL Database

This article teaches you a generic workflow for how to move your database or elastic pool to a new region.

Note

To move databases and elastic pools to a different Azure region, you can also use the recommended Azure Resource Mover.

Overview

There are various scenarios in which you'd want to move your existing database or pool from one region to another. For example, you're expanding your business to a new region and want to optimize it for the new customer base. Or you need to move the operations to a different region for compliance reasons. Or Azure released a new region that provides a better proximity and improves the customer experience.

The general workflow to move resources to a different region consists of the following steps:

  1. Verify the prerequisites for the move.
  2. Prepare to move the resources in scope.
  3. Monitor the preparation process.
  4. Test the move process.
  5. Initiate the actual move.

Verify prerequisites to move database

  1. Create a target server for each source server.
  2. Configure the firewall with the right exceptions by using PowerShell.
  3. Configure both servers with the correct logins. If you're not the subscription administrator or SQL server administrator, work with the administrator to assign the permissions that you need. For more information, see How to manage Azure SQL Database security after disaster recovery.
  4. If your databases are encrypted with transparent data encryption (TDE) and bring your own encryption key (BYOK or Customer-Managed Key) in Azure Key Vault, ensure that the correct encryption material is provisioned in the target regions.
    • The simplest way to do this is to add the encryption key from the existing key vault (that is being used as TDE Protector on source server) to the target server and then set the key as the TDE Protector on the target server since a server in one region can now be connected to a key vault in any other region.
    • As a best practice to ensure the target server has access to older encryption keys (required for restoring database backups), run the Get-AzSqlServerKeyVaultKey cmdlet on the source server to return the list of available keys and add those keys to the target server.
    • For more information and best practices on configuring customer-managed TDE on the target server, see Azure SQL transparent data encryption with customer-managed keys in Azure Key Vault.
    • To move the key vault to the new region, see Move an Azure key vault across regions.
  5. If database-level audit is enabled, disable it and enable server-level auditing instead. After failover, database-level auditing requires cross-region traffic, which isn't desired or possible after the move.
  6. For server-level audits, ensure that:
    • The storage container, Log Analytics, or event hub with the existing audit logs is moved to the target region.
    • Auditing is configured on the target server. For more information, see Get started with SQL Database auditing.
  7. If your server has a long-term retention policy (LTR), the existing LTR backups remain associated with the current server. Because the target server is different, you are able to access the older LTR backups in the source region by using the source server, even if the server is deleted.

Note

Migrating databases with existing LTR backups between regions is unsupported since this requires moving LTR backups to the target server, which is not currently possible.

Prepare resources

  1. Create a failover group between the server of the source and the server of the target.
  2. Add the databases you want to move to the failover group. Replication of all added databases is initiated automatically. For more information, see Using failover groups with SQL Database.

Monitor the preparation process

You can periodically call Get-AzSqlDatabaseFailoverGroup to monitor replication of your databases from the source to the target server. The output object of Get-AzSqlDatabaseFailoverGroup includes a property for the ReplicationState:

  • ReplicationState = 2 (CATCH_UP) indicates the database is synchronized and can be safely failed over.
  • ReplicationState = 0 (SEEDING) indicates that the database isn't yet seeded, and an attempt to fail over will fail.

Test synchronization

After ReplicationState is 2, connect to each database or subset of databases using the secondary endpoint <fog-name>.secondary.database.chinacloudapi.cn and perform any query against the databases to ensure connectivity, proper security configuration, and data replication.

Initiate the move

  1. Connect to the target server using the secondary endpoint <fog-name>.secondary.database.chinacloudapi.cn.
  2. Use Switch-AzSqlDatabaseFailoverGroup to switch the secondary server to be the primary with full synchronization. This operation succeeds or it rolls back.
  3. Verify that the command has completed successfully by using nslook up <fog-name>.secondary.database.chinacloudapi.cn to ascertain that the DNS CNAME entry points to the target region IP address. If the switch command fails, the CNAME won't be updated.

Remove the source databases

Once the move completes, remove the resources in the source region to avoid unnecessary charges.

  1. Delete the failover group using Remove-AzSqlDatabaseFailoverGroup.
  2. Delete each source database using Remove-AzSqlDatabase for each of the databases on the source server. This automatically terminates geo-replication links.
  3. Delete the source server using Remove-AzSqlServer.
  4. Remove the key vault, audit storage containers, event hub, Microsoft Entra tenant, and other dependent resources to stop being billed for them.

Verify prerequisites to move pool

  1. Create a target server for each source server.
  2. Configure the firewall with the right exceptions using PowerShell.
  3. Configure the servers with the correct logins. If you're not the subscription administrator or server administrator, work with the administrator to assign the permissions that you need. For more information, see How to manage Azure SQL Database security after disaster recovery.
  4. If your databases are encrypted with transparent data encryption and use your own encryption key in Azure Key Vault, ensure that the correct encryption material is provisioned in the target region.
  5. Create a target elastic pool for each source elastic pool, making sure the pool is created in the same service tier, with the same name and the same size.
  6. If a database-level audit is enabled, disable it and enable server-level auditing instead. After failover, database-level auditing will require cross-region traffic, which isn't desired, or possible after the move.
  7. For server-level audits, ensure that:
    • The storage container, Log Analytics, or event hub with the existing audit logs is moved to the target region.
    • Audit configuration is configured at the target server. For more information, see SQL Database auditing.
  8. If your server has a long-term retention policy (LTR), the existing LTR backups remain associated with the current server. Because the target server is different, you are able to access the older LTR backups in the source region using the source server, even if the server is deleted.

Note

Migrating databases with existing LTR backups between regions is unsupported since this requires moving LTR backups to the target server, which is not currently possible.

Prepare to move

  1. Create a separate failover group between each elastic pool on the source server and its counterpart elastic pool on the target server.

  2. Add all the databases in the pool to the failover group. Replication of the added databases is initiated automatically. For more information, see Using failover groups with SQL Database.

    Note

    While it is possible to create a failover group that includes multiple elastic pools, we strongly recommend that you create a separate failover group for each pool. If you have a large number of databases across multiple elastic pools that you need to move, you can run the preparation steps in parallel and then initiate the move step in parallel. This process scales better and takes less time compared to having multiple elastic pools in the same failover group.

Monitor the preparation process

You can periodically call Get-AzSqlDatabaseFailoverGroup to monitor replication of your databases from the source to the target. The output object of Get-AzSqlDatabaseFailoverGroup includes a property for the ReplicationState:

  • ReplicationState = 2 (CATCH_UP) indicates the database is synchronized and can be safely failed over.
  • ReplicationState = 0 (SEEDING) indicates that the database isn't yet seeded, and an attempt to fail over will fail.

Test synchronization

Once ReplicationState is 2, connect to each database or subset of databases using the secondary endpoint <fog-name>.secondary.database.chinacloudapi.cn and perform any query against the databases to ensure connectivity, proper security configuration, and data replication.

Initiate the move

  1. Connect to the target server using the secondary endpoint <fog-name>.secondary.database.chinacloudapi.cn.
  2. Use Switch-AzSqlDatabaseFailoverGroup to switch the secondary server to be the primary with full synchronization. This operation either succeeds, or it rolls back.
  3. Verify that the command has completed successfully by using nslook up <fog-name>.secondary.database.chinacloudapi.cn to ascertain that the DNS CNAME entry points to the target region IP address. If the switch command fails, the CNAME isn't updated.

Remove the source elastic pools

Once the move completes, remove the resources in the source region to avoid unnecessary charges.

  1. Delete the failover group using Remove-AzSqlDatabaseFailoverGroup.
  2. Delete each source elastic pool on the source server using Remove-AzSqlElasticPool.
  3. Delete the source server using Remove-AzSqlServer.
  4. Remove the key vault, audit storage containers, event hub, Microsoft Entra tenant, and other dependent resources to stop being billed for them.

Next steps

Manage your database after it has been migrated.