Azure SQL Database – A Copying “Gotcha”

Creating copies of your Azure SQL Database is a common and relatively simple process. You can issue a TSQL statement on the master database such as:

CREATE DATABASE CodeDBcopy
  AS COPY OF CodeDB

Now, I was looking at the following code.

CREATE DATABASE CodeDBP1
  AS COPY OF CodeDB ( SERVICE_OBJECTIVE = 'P1' )  ;

You would think this is okay? I did, especially with the fact that it parsed and was executing. I was thinking a copy of the CodeDB database will be created as a premium P1 database regardless of what the source database service tier was. This  source database is 0.5GB in size under the basic tier and 40 minutes later the copy was still executing. It just didn’t seem right.

StillExe

Checking the only useful DMV I know I was seeing the below in progress 50% complete messages.

SELECT * FROM sys.dm_operation_status
ORDER BY start_time DESC

inprogress

I logged into the portal to look at the scaling options, looking for a clue to try  and understand why this was behaving like this. Then I was given a hint by the portal.

hint

You can only copy a database within the same tier as the original database! So even though the TSQL I originally executed parsed correctly ( and went into an executing state) it was not going to finish….

I cancel the query resulting in:

Msg 42019, Level 16, State 4, Line 17 CREATE DATABASE AS Copy of  operation failed. Internal service error. Query was canceled by user.

Then I execute a query that works within minutes.

CREATE DATABASE CodeDBbasic
  AS COPY OF CodeDB ( SERVICE_OBJECTIVE = 'BASIC' )  ;  

copyok

 

So, the point of the post? You can only copy a database within the same tier as the original database. Be extra careful when using TSQL commands for a copy where you state a higher service tier, they will parse and execute but the query won’t do much…

 

2 thoughts on “Azure SQL Database – A Copying “Gotcha”

  1. Pingback: Dew Drop - April 25, 2018 (#2711) - Morning Dew

  2. Pingback: Copying Azure SQL Databases – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s