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.
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
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.
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' ) ;
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…