Blog Post

Azure SQL Blog
2 MIN READ

Database copy preview on Azure SQL Database Hyperscale

denzilr's avatar
denzilr
Icon for Microsoft rankMicrosoft
Sep 21, 2020

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.

On Azure SQL Hyperscale the time taken by database copy depends on the source and destination chosen for the copy

  • If the destination is the same region as the source, then the copy is created from snapshots of blobs created by database backups. This kind of copy is a fast operation regardless of database size.
  • If the destination region for the copy is different from the source region, 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 potentially long running size of data operation.

Below are some sample durations for database copy in Hyperscale:

 

Database size

Source region

Destination region

Elapsed time (mins)

1TB

West US 2

Central US

180

1 TB

West US 2

West US 2

12

7.2TB

West US 2

Central US

269

7.2TB

West US 2

West US 2

13

 

Creating a Database Copy

Database copy can be created started with PowerShell, Azure CLI, REST API, or T-SQL. Cross subscription and cross tenant copy is only supported using T-SQL. If you do not specify a service objective for the target database, the default service objective and configurations are used, which is HS_Gen5_2 with 1 additional replica which can be used for reads.

 

Azure CLI

 

 

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

 

 

PowerShell

 

 

New-AzSqlDatabaseCopy -ResourceGroupName "sourceRG" -ServerName sourceServer  `-DatabaseName "sourceDB" `
-CopyResourceGroupName "destinationRG" -CopyServerName destServer -CopyDatabaseName "destDB"

 

 

TSQL

 

 

CREATE DATABASE Database2 AS COPY OF SourceServer.Database1;

 

 

Portal

Another way to copy the database is via the Azure portal.

Portal

Monitoring database copy:

In the master database:

select * from sys.dm_operation_status

GO

 

sys.dm_operation_status

Note: the progress status values are limited to 0/50/100%.

Current preview Limitations

  • sys.dm_database_copies  does not currently track progress of database copy, this will be addressed before GA.
  • Currently portal target database allows choice of elastic pool but the copy will fail as Hyperscale does not support Elastic pools
Updated Sep 21, 2020
Version 2.0
  • Thanks for catching that, I mistakenly flipped the rows, that was the cross region copy. In region WestUS2 - WestUS took 12 mins

  • Johannes_Vink's avatar
    Johannes_Vink
    Brass Contributor

    Hi Denzil,

     

    Are you sure that 1 TB from West US 2 to West US 2 takes 180 minutes? Because that is slower than a database copy to a different region.

     

    Regards, Johannes

  • Johannes_Vink's avatar
    Johannes_Vink
    Brass Contributor

    Now it makes more sense 😉

     

    Impressive to see that between 1 TB and 7.2 TB within the same region there is a very minor difference. That scales nicely.

     

    A copy within the same region is the same as a backup operation, except that the backup selection can be historical and the copy is from the actual state of the database?

  • sergeik's avatar
    sergeik
    Copper Contributor

    denzilr Is the Data Sync on the Roadmap for the Hyperscale? When is it planned for the preview?
    Thanks!

  • nokis's avatar
    nokis
    Copper Contributor

    Finally, "Coming soon" button was there long. Anyway, this is great, makes creating dev copies now much easier. Thanks!