Cross Subscription Copying of Databases on Windows Azure SQL Database

Question

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 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 vica verca) assuming we use a consistent login?

Answer 1

You can just do a backup (Export) to blob storage and then Import it in the new subscription.

http://msdn.microsoft.com/en-us/library/f6899710-634e-425a-969d-8db1267e9471

Update: If you can use SSMS, this answer is right. I only want to add some details.

1. You can export the source database into storage in Azure Portal.
2. After exporting, you can find the bacpac file.
3. Open SSMS, and connect to the destination server.
4. Right click the node Database and select Import Data-tier Application
5. Then you can choose import the database from local disk or Azure storage.
6. After that, you have copied the database from source to destination.

Answer 2

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.

Answer 3

I have created copies of databases across Azure subscriptions successfully. Here are the steps -

1. On the target Azure subscription create a database server (if you haven't already created one), and on that create a new DB (any name, doesn't matter) but with the same password as the source database on your source Azure subscription. For me it didn't work with different passwords, so I just went ahead with using the same, but I am sure there is a way to make it work with different passwords as well.

2. Run this on the newly created database in your target Azure -

CREATE DATABASE NEWDBNAME
AS COPY OF [Source Azure Server Name here].[source DB]

Let Azure handle the new DB pricing tier (Basic, Standard etc), because you can immediately change it from the portal after the DB is created. In my case the target DB was created with the same pricing tier as the source DB. Also, the server names in azure are usually - NAME.database.windows.net. So in your source name above, just put NAME.

3. Now on your target Azure subscription you will have 2 databases on the new DB server. One which was created in step 1 and the other in step 2 which is the actual copy. You can go ahead and safely delete the one which you don't need.

4. If you want to copy other source DBs to the same target server created in 1 above, just run the same command again.

Answer 4

I guess you already have a solution, however for anyone landing here, you can use the Azure PowerShell API's to Create a new server in the source subscription, create a copy and switch over the new server to the destination subscription

Sample code is available on technet

The code is self explanatory, however in the interest of SO best practices,

Key portions of the code are

Create a new server:

$newserver = New-AzureSqlDatabaseServer -Location $targetServerLocation -AdministratorLogin $targetServerLoginID -AdministratorLoginPassword $targetServerLoginPassword 

Create a database copy:

Start-AzureSqlDatabaseCopy -ServerName $sourceServerName -DatabaseName $sourceDatabaseName -PartnerServer $newserver.ServerName  -PartnerDatabase $targetdatabaseName  

Transfer the server

$uri = "https://management.core.windows.net:8443/" + $sourceSubscriptionID + "/services" + "/sqlservers/servers/" + $newserver.ServerName + "?op=ChangeSubscription" 

Invoke-RestMethod -Uri $uri -CertificateThumbPrint $certThumbprint -ContentType $contenttype -Method $method -Headers $headers -Body $body 

Answer 5

You can do this in SSMS on the target server using

CREATE DATABASE db1 AS COPY OF sourcesrv.db1

to copy from sourcesrv.database.windows.net which is in a different subscription.

However, you must first check you can connect in SSMS to the SOURCE server too, or you will get a totally confusing error message which hides the actual problem.

The source server may be one you regularly connect to, but not from the IP address you're currently on. In that case you must add the IP to the server's firewall rules. This is easily done using the dialog that appears when you try to connect from SSMS:

Leave the default radiobutton checked ("Add my client IP") and press OK.

If you omit this check and it fails to authenticate you, instead of telling you the correct reason as above, it tells you you can't make a copy on the SOURCE server!

--In SSMS connected to targetsrv:

CREATE DATABASE db1 AS COPY OF sourcesrv.db1

--Here it should say, "Your client IP address does not have access" to sourcesrv, like when
--you try to connect in SSMS. Instead, it says you can't copy to the SOURCE, even though you 
--told it to copy FROM the source to the TARGET:

--Msg 45137, Level 16, State 1, Line 7
--Insufficient permission to create a database copy on server 'sourcesrv'.

Note that at the time of writing, the two servers need to be configured with the same admin credentials, otherwise the CREATE DATABASE command will fail with this same confusing error message.

Answer 6

I understand that this is quite old question and still wanted to add yet another option.

If you want to have this task automated, you do not want to do it manually (Export-Import), you want to copy the database to the existing server (and not create a new temporary one that will be moved across subscriptions) and you do not want to have same credentials on source and target servers because of security considerations, you can use ARM.

There is a option to create database as copy ("createMode": "Copy",) and it will make it across subscriptions! Simple example:

{
  "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
  },
  "resources": [
    {
      "apiVersion": "2014-04-01-preview",
      "location": "australiaeast",
      "name": "{DESTINATION-SERVER-NAME}/{DESTINATION-DATABASE-NAME}",
      "properties": {
        "createMode": "Copy",
        "sourceDatabaseId": "/subscriptions/{SOURCE-SUBSCRIPTION-ID}/resourceGroups/{SOURCE-RESOURCE-GROUP-NAME}/providers/Microsoft.Sql/servers/{SOURCE-SERVER-NAME}/databases/{SOURCE-DATABASE-NAME}",
        "requestedServiceObjectiveName": "S2"
      },
      "type": "Microsoft.Sql/servers/databases"
    }
  ]
}  

Two things to note - the service principal that will be executing this deployment will need to be have a contributor access on source and the sourceDatabaseId is a full resource id.

If you do it from Azure DevOps using "Azure resource group deployment" task - it will create a SP for subscription. You will need give Contributor access to it. SP can be found in Project Settings -> Service Connections.

Answer 7

There is a more simple solution, which maybe wasn't available when this question was answered. No SMSS or PowerShell needed. It can all be done in the portal. Go to the source SQL Database and click Export. This will create a .bacpac file in Azure Storage. Go to the target SQL Server and click Import. Done.

Note 1: if the target SQL Sever is in a different account/subscription that cannot access the source account's Azure Storage, just manually download the file from the source Azure Storage and upload it to an Azure Storage instance that the target can access.

Note 2: the imported database will have a name that includes the export date. You can change the name by running ALTER DATABASE [dbname] MODIFY NAME = [newdbname] on the target database. You can even do this in the portal using the new Query Editor.