Database copy provides a way to create a transactionally consistent snapshot of the source database as a new database on the same server or different server. It also enables copy across regions, subscriptions, and Azure tenants using the same or different service level objective. DB copy for Hyperscale has been in preview and is now generally available.
On Azure SQL Hyperscale there are subtle differences in database copy as compared to other tiers.
- If the target region for the copy is the same as the source, then the target database is created from snapshots of blobs. This in-region copy is a fast operation regardless of database size.
- If the target region for the copy is different than the source, the copy is a size of data operation. Page server blobs are copied in parallel, so the copy time is not directly proportional to the size of data, yet it is a longer running size of data operation.
- Database copy now allows you to change storage and backup tiers giving you a choice between Locally redundant storage (LRS), Zone-redundant storage (ZRS) or Geo-redundant storage (RA-GRS). Changing the backup storage redundancy on the target database to be different for from the source results in the copy being a size of data operation like a cross region copy.
Sample database copy:
|
DB Size |
Source Region |
Target Region |
Source Storage |
Target Storage |
Elapsed minutes |
|
9.4TB |
WestUS2 |
WestUS2 |
Geo |
Geo |
12 |
|
9.4TB |
WestUS2 |
West Central US |
Geo |
Geo |
133 |
|
9.4TB |
WestUS2 |
WestUS2 |
Geo |
Local |
125 |
DMV sys.dm_operation_status now does track copy progress, specifically for underlying data copy operations.
Creating a Database copy
Database copy can be created with PowerShell, Azure CLI, REST API, or T-SQL. Cross subscription and cross-tenant copies are only supported using T-SQL.
az sql db copy --resource-group "sourceRG" --server sourceServer --name "sourceDB" --dest-resource-group destDB --dest-server destServer --dest-name destDB --service-objective HS_Gen5_2 --read-replicas 0
Portal:
We have also recently in preview introduced backup storage redundancy and having a different backup storage redundancy type than the source database results in a size of data copy even if the copy is within the same region.
To Learn more: Copy Database - Azure SQL Database docs