Blog Post

Azure SQL Blog
2 MIN READ

Database copy for Azure SQL Hyperscale now generally available!

denzilr's avatar
denzilr
Icon for Microsoft rankMicrosoft
Aug 04, 2021

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

 

Updated Aug 02, 2021
Version 1.0
  • amitdba8606's avatar
    amitdba8606
    Copper Contributor

    Hi, May I ask you for one help,

    In below code you have used "--read-replicas 0" which create the database as copy without replica DB, I am trying to find the exact syntax  for TSQL but couldn't find

     

    CREATE DATABASE [<DB Name>] AS COPY OF [Prod DB Name] ( EDITION ='Hyperscale', SERVICE_OBJECTIVE = 'HS_S_Gen5_2', ....<syntax for read-replicas 0 >..) , can you please suggest?

     

    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

  • amitdba8606's avatar
    amitdba8606
    Copper Contributor

    My requirement is to create database as copy without replica in hyperscale module