LLI Wiki — My Digital Knowledge Base

Azure SQL Database: database copy and the General Purpose to Hyperscale limit

Use CREATE DATABASE AS COPY OF, monitor the copy, and understand error 45557 when targeting Hyperscale.

In Azure SQL Database, CREATE DATABASE ... AS COPY OF copies a database to another database on the same or another server, letting you choose the edition and service objective of the copy. Copying from the General Purpose tier to the Hyperscale tier is, however, not possible.

Limitation: General Purpose to Hyperscale

This attempt is rejected:

CREATE DATABASE [AppDb_copy] AS COPY OF [AppDb]
(
    EDITION = 'Hyperscale',
    SERVICE_OBJECTIVE = 'HS_S_Gen5_4'
);
Msg 45557, Level 16, State 1, Line 17
Database copy operations from the GeneralPurpose service tier to the Hyperscale service tier are not supported.

Copy to General Purpose

Copy while staying in General Purpose, with an explicit service objective:

CREATE DATABASE [AppDb_copy] AS COPY OF [AppDb]
(
    EDITION = 'GeneralPurpose',
    SERVICE_OBJECTIVE = 'GP_Gen5_4'
);

The maximum size can also be set:

CREATE DATABASE [AppDb_copy] AS COPY OF [AppDb]
( EDITION = 'GeneralPurpose', SERVICE_OBJECTIVE = 'GP_Gen5_4', MAXSIZE = 200 GB );

Available service objectives (for example GP_Gen5_4) are listed in the vCore resource limits for single databases.

Monitor and check the copy

Edition and service objective of the copy:

SELECT d.name, slo.*
FROM sys.databases d
JOIN sys.database_service_objectives slo
  ON d.database_id = slo.database_id
WHERE d.name = 'AppDb_copy';

Status of copy operations (here, termination of the continuous copy):

SELECT * FROM sys.dm_operation_status
WHERE major_resource_id IN ('AppDb_copy')
  AND operation = 'TERMINATE CONTINUOUS DATABASE COPY'
  AND last_modify_time > (SELECT MAX(last_modify_time)
                          FROM sys.dm_operation_status
                          WHERE major_resource_id IN ('AppDb_copy')
                            AND operation = 'CREATE DATABASE COPY');

To remove the test copy:

DROP DATABASE [AppDb_copy];

Notes

Mastodon LLIMastodon SocialFosstodon