Explore Courses Blog Tutorials Interview Questions
0 votes
in Azure by (45.3k points)

We are about to split our testing and production instances in Windows Azure into two separate subscriptions. Currently, we have 3 Windows Azure SQL Database instances that reside within the same subscription:

  • Production
  • Reporting
  • Testing

In order to completely isolate production, we are splitting these into:

  • Production Subscription
    • Production
    • Reporting
  • Testing Subscription
    • Testing

At the moment we use the CREATE DATABASE X AS COPY OF [ServerName].Y the command to copy databases from production to testing before we obfuscate the live data. This operation can be performed so long as the databases are geo-located in the same data centre and we have a shared login across the instances that created the database in the first place (As indicated by this article).

However; the article does not indicate whether the source and destination instance need to belong to the same subscription. Are we able to copy the database between the production subscription and testing subscription (And vice versa) assuming we use a consistent login?

1 Answer

0 votes
by (16.8k points)

For anyone landing here, it does appear to be possible to use CREATE DATABASE newDB AS COPY OF [server].[olddb] ( OPTION [, OPTION ...] ) even when the servers are in different subscriptions.

See more at Create Database (Azure SQL Database) - MSDN

Example from MS Docs:

CREATE DATABASE db_copy AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' ) ;

In my setup I have the admin account and password (login) the same on both servers - that probably helps. Operation will fail if you don't have admin permissions on original server.

I have found through testing that I am not able to change the Edition from Standard to Premium despite including the 'Edition' option - I'm not sure why that is.

Browse Categories