CODEWITHSUNDEEP
azure-sql-database
mccdyl001
mccdyl001

Reputation: 182

How to set DTU for Azure Sql Database via SQL when copying?

I know that you can create a new Azure SQL DB by copying an existing one by running the following SQL command in the [master] db of the destination server:

CREATE DATABASE [New_DB_Name] AS COPY OF [Azure_Server_Name].[Existing_DB_Name]

What I want to find out is if its possible to change the number of DTU's the copy will have at the time of creating the copy?

As a real life example, if we're copying a [prod] database to create a new [qa] database, the copy might only need resources to handle a small testing team hitting the QA DB, not a full production audience. Scaling down the assigned DTU's would result in a cheaper DB. At the moment we manually scale after the copy is complete but this takes just as long as the initial copy (several hours for our larger DB's) as it copies the database yet again. In an ideal world we would like to skip that step and be able to fully automate the copy process.

Upvotes: 1

Views: 1208

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89191

According to the the docs is is:

CREATE DATABASE database_name  
    AS COPY OF [source_server_name.] source_database_name  
    [ ( SERVICE_OBJECTIVE = 
      {  'basic' | 'S0' | 'S1' | 'S2' | 'S3' | 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |  
      | 'GP_GEN4_1' | 'GP_GEN4_2' | 'GP_GEN4_4' | 'GP_GEN4_8' | 'GP_GEN4_16' | 'GP_GEN4_24' |
      | 'BC_GEN4_1' | 'BC_GEN4_2' | 'BC_GEN4_4' | 'BC_GEN4_8' | 'BC_GEN4_16' | 'BC_GEN4_24' |
      | 'GP_GEN5_2' | 'GP_GEN5_4' | 'GP_GEN5_8' | 'GP_GEN5_16' | 'GP_GEN5_24' | 'GP_GEN5_32' | 'GP_GEN5_48' | 'GP_GEN5_80' |
      | 'BC_GEN5_2' | 'BC_GEN5_4' | 'BC_GEN5_8' | 'BC_GEN5_16' | 'BC_GEN5_24' | 'BC_GEN5_32' | 'BC_GEN5_48' | 'BC_GEN5_80' |
        | { ELASTIC_POOL(name = <elastic_pool_name>) } } )  
   ]  
[;]

CREATE DATABASE (sqldbls)

You can also change the DTU level during a copy from the PowerShell API

New-AzureRmSqlDatabaseCopy

But you can only choose "a different performance level within the same service tier (edition)" Copy an Azure SQL Database.

You can, however, copy the database into an elastic pool in the same service tier, so you wouldn't be allocating new DTU resources. You might have a single pool for all your dev/test/qa datatabases and drop the copy there.

If you want to change the service tier, you could a Point-in-time Restore instead of a Database Copy. The database can be restored to any service tier or performance level, using the Portal, PowerShell or REST.

Recover an Azure SQL database using automated database backups

Upvotes: 2

Related Questions