Database copy for Azure SQL Hyperscale now generally available!

Published Aug 04 2021 08:00 AM 2,001 Views
Microsoft

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.

DenzilRibeiro_0-1627923532507.png

 

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.

 

DenzilRibeiro_1-1627923727278.png

 

To Learn more: Copy Database - Azure SQL Database docs

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2598080%22%20slang%3D%22en-US%22%3EDatabase%20copy%20for%20Azure%20SQL%20Hyperscale%20now%20generally%20available!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2598080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fdatabase-copy%3Ftabs%3Dazure-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDatabase%20copy%3C%2FA%3E%26nbsp%3Bprovides%20a%20way%20to%20create%20a%20transactionally%20consistent%20snapshot%20of%20the%20source%20database%20as%20a%20new%20database%20on%20the%20same%20server%20or%20different%20server.%20It%20also%20enables%20copy%20across%20regions%2C%20subscriptions%2C%20and%20Azure%20tenants%20using%20the%20same%20or%20different%20service%20level%20objective.%20%26nbsp%3BDB%20copy%20for%20Hyperscale%20has%20been%20in%20preview%20and%20is%20now%20generally%20available.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20Azure%20SQL%20Hyperscale%20there%20are%20subtle%20differences%20in%20database%20copy%20as%20compared%20to%20other%20tiers.%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20the%20target%20region%20for%20the%20copy%20is%20the%20same%20as%20the%20source%2C%20then%20the%20target%20database%20is%20created%20from%20snapshots%20of%20blobs.%20This%20in-region%20copy%20is%20a%20fast%20operation%20regardless%20of%20database%20size.%3C%2FLI%3E%0A%3CLI%3EIf%20the%20target%20region%20for%20the%20copy%20is%20different%20than%20the%20source%2C%20the%20copy%20is%20a%20size%20of%20data%20operation.%20Page%20server%20blobs%20are%20copied%20in%20parallel%2C%20so%20the%20copy%20time%20is%20not%20directly%20proportional%20to%20the%20size%20of%20data%2C%20yet%20it%20is%20a%20longer%20running%20size%20of%20data%20operation.%3C%2FLI%3E%0A%3CLI%3EDatabase%20copy%20now%20allows%20you%20to%20change%20storage%20and%20backup%20tiers%20giving%20you%20a%20choice%20between%20Locally%20redundant%20storage%20(LRS)%2C%20Zone-redundant%20storage%20(ZRS)%20or%20Geo-redundant%20storage%20(RA-GRS).%20Changing%20the%20backup%20storage%20redundancy%20on%20the%20target%20database%20to%20be%20different%20for%20from%20the%20source%20results%20in%20the%20copy%20being%20a%20size%20of%20data%20operation%20like%20a%20cross%20region%20copy.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3ESample%20database%20copy%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2266px%22%3E%3CP%3EDB%20Size%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22102px%22%3E%3CP%3ESource%20Region%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22114px%22%3E%3CP%3ETarget%20Region%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3ESource%20Storage%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3ETarget%20Storage%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22126px%22%3E%3CP%3EElapsed%20minutes%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2266px%22%3E%3CP%3E9.4TB%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22102px%22%3E%3CP%3EWestUS2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22114px%22%3E%3CP%3EWestUS2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3EGeo%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3EGeo%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22126px%22%3E%3CP%3E12%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2266px%22%3E%3CP%3E9.4TB%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22102px%22%3E%3CP%3EWestUS2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22114px%22%3E%3CP%3EWest%20Central%20US%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3EGeo%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3EGeo%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22126px%22%3E%3CP%3E133%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2266px%22%3E%3CP%3E9.4TB%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22102px%22%3E%3CP%3EWestUS2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22114px%22%3E%3CP%3EWestUS2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3EGeo%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22108px%22%3E%3CP%3ELocal%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22126px%22%3E%3CP%3E125%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDMV%20%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fsystem-dynamic-management-views%2Fsys-dm-operation-status-azure-sql-database%3Fview%3Dazuresqldb-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Esys.dm_operation_status%3C%2FA%3E%20now%20does%20track%20copy%20progress%2C%20specifically%20for%20underlying%20data%20copy%20operations.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DenzilRibeiro_0-1627923532507.png%22%20style%3D%22width%3A%20624px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299935iAF9640BD910623CA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DenzilRibeiro_0-1627923532507.png%22%20alt%3D%22DenzilRibeiro_0-1627923532507.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ECreating%20a%20Database%20copy%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EDatabase%20copy%20can%20be%20created%20with%20PowerShell%2C%20Azure%20CLI%2C%20REST%20API%2C%20or%20T-SQL.%20%26nbsp%3BCross%20subscription%20and%20cross-tenant%20copies%20are%20only%20supported%20using%20T-SQL.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Eaz%20sql%20db%20copy%20--resource-group%20%22sourceRG%22%20--server%20sourceServer%20--name%20%22sourceDB%22%20--dest-resource-group%20destDB%20--dest-server%20destServer%20--dest-name%20destDB%20--service-objective%20HS_Gen5_2%20--read-replicas%200%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPortal%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWe%20have%20also%20recently%20in%20preview%20introduced%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fautomated-backups-overview%3Ftabs%3Dsingle-database%23backup-storage-redundancy%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ebackup%20storage%20redundancy%3C%2FA%3E%20and%20having%20a%20different%20backup%20storage%20redundancy%20type%20than%20the%20source%20database%20results%20in%20a%20size%20of%20data%20copy%20even%20if%20the%20copy%20is%20within%20the%20same%20region.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DenzilRibeiro_1-1627923727278.png%22%20style%3D%22width%3A%20624px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F299936iC8ADE6C3542E245F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22DenzilRibeiro_1-1627923727278.png%22%20alt%3D%22DenzilRibeiro_1-1627923727278.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20Learn%20more%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fdatabase-copy%3Ftabs%3Dazure-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECopy%20Database%20-%20Azure%20SQL%20Database%20docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2598080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fdatabase-copy%3Ftabs%3Dazure-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EDatabase%20copy%3C%2FA%3E%26nbsp%3Bprovides%20a%20way%20to%20create%20a%20transactionally%20consistent%20snapshot%20of%20the%20source%20database%20as%20a%20new%20database%20on%20the%20same%20server%20or%20different%20server.%20Within%20region%20this%20copy%20is%20a%20fast%20operation%20irrespective%20of%20data%20side.%20Database%20copy%20does%20enable%20various%20scenarios%20such%20as%20getting%20a%20quick%20copy%20of%20the%20database%20to%20Dev%20test.%20DB%20copy%20for%20Hyperscale%20has%20been%20in%20preview%20and%20is%20now%20generally%20available.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2598080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Database%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Aug 02 2021 10:19 AM
Updated by: