28

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?

Luke Merrett
  • 5,724
  • 8
  • 38
  • 70
  • for the time you spent to write this question you would have tested. And I don't know the answer, but I know for sure that you can move an Azure SQL Database Server between subscriptions. Which might be the worst scenario - Move the `Server` from Sub-1 to Sub-2, copy the database, then Move the `Server` back to Sub-1. Moving a database Server between subscriptions should not invoke any downtime, as this is just a logical operation. However I am not sure whether there is a limit of how many times per billing period you can move the server around. – astaykov Jul 19 '13 at 14:16
  • 12
    "_for the time you spent to write this question you would have tested._" I'm sorry I don't understand this response; if you are indicated we could test whether it is possible, we still only have one subscription, this is something I'd like clarification on before we split the subscriptions. In regards to moving the server; it seems excessive to move a whole instance between subscriptions. Especially if the aim is to separate the environments. – Luke Merrett Jul 19 '13 at 14:29

7 Answers7

19

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. enter image description here
  2. After exporting, you can find the bacpac file. enter image description here
  3. Open SSMS, and connect to the destination server.
  4. Right click the node Database and select Import Data-tier Application enter image description here
  5. Then you can choose import the database from local disk or Azure storage. enter image description here
  6. After that, you have copied the database from source to destination.
Crazy Crab
  • 694
  • 6
  • 16
Craig
  • 36,306
  • 34
  • 114
  • 197
  • Thank you for the answer Craig; that's definitely our backup plan if `CREATE DATABASE AS COPY OF` doesn't work cross subscription – Luke Merrett Jul 22 '13 at 07:40
  • I did a backup this way. - my DTU's goes to 100% - I rescale DB twice. In future, I would rescale it at the beginning and scale down at the end.(if I will go with backpack) - My backup is some way broken - (some data are duplicated) I suppose because of rescale operation. For me it was ok. However, in future I would look for another option. I've found that it is not reccomended way to do it this way: [BACPACs are not intended to be used for backup and restore operations](https://learn.microsoft.com/en-us/azure/sql-database/sql-database-export) Just to let you know – wolszakp Aug 14 '19 at 08:12
  • 1
    This is by far the slowest method and should only be used as a last resort. – Brian Richardson Sep 10 '20 at 19:06
17

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.

Michael Freidgeim
  • 26,542
  • 16
  • 152
  • 170
Richard Hauer
  • 1,298
  • 9
  • 22
  • Great reply thank you Richard; one assumes this is restricted to a single data center? The edition flag is an interesting one; we were previously able to change edition when copying on the same subscription. – Luke Merrett Oct 25 '15 at 20:00
  • @LukeMerrett In my test scenario both SQL "servers" (we all know they aren't real servers, right?) are in the same DC, Sydney. By pinging I can see that they are actually both on the same cluster (same IP). I don't know if that's a requirement - I would hope not. The documentation doesn't specify so perhaps they have "Linked Servers" setup or maybe some special handling for "AS COPY OF" - I've not seen that syntax outside of SQL Azure DBs. – Richard Hauer Oct 26 '15 at 01:24
  • This worked perfectly for me. Note that my servers were in different subscriptions. SQL admin account and password was the same on both servers. Servers were on the same Tenant too - checked via Azure PowerShell console with (Get-AzureRmSubscription -SubscriptionName ).TenantId and (Get-AzureRmSubscription -SubscriptionName ).TenantId - and I didn't bother with specifying the Service_Objective since, as a copy, I want them to have the same service objective. – Colin K Apr 14 '18 at 04:14
  • Tip: If you don't have identical logins for the primary admin account (which you probably shouldn't) what you need to do is create a login on the source server that is identical to the target server's admin login that you are using. Add a user for that login to your source database and to the master on your source server. In master, add it to dbmanager role. In the source database, add it to db_owner role. That's it. – ron Mar 07 '19 at 15:47
  • @ron Excellent tip! For the record I changed the password on the admin accounts so they were the same for the time needed to complete the task, then put them back again. But you're quite right, don't recycle passwords people. – Richard Hauer Mar 08 '19 at 13:35
  • Does anyone know how this works with TDE? When I try it with my BYOK I just get an error message that I should use the same key material. Which is not possible because the Vault is in a different tenant. Do I need to disable it completely or is there a way around this? – Staeff Nov 18 '21 at 10:00
  • @Staeff this is a very old response now and likely many of the behaviour of these systems has since changed. I don’t have any info that might help in your circumstance I’m afraid. – Richard Hauer Nov 18 '21 at 10:46
10

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.

  1. 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.

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

Sharad Seth
  • 101
  • 1
  • 4
  • This was what we needed. Everything else was WAY too slow! Thanks for this. – Dave Jan 10 '19 at 23:25
  • It also works across Azure tenants. And it's incredibly fast. For a 18 GB database, the operation took less than five minutes. As a comparison, the import/export method took 5 hours. – dub May 21 '19 at 17:45
9

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
Akash
  • 1,716
  • 2
  • 23
  • 43
5

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.

JleruOHeP
  • 10,106
  • 3
  • 45
  • 71
  • 1
    Thanks very much for this! The T-SQL solutions are so finnicky as they are all on the data plane. This was a much better solution for my use case as it only requires the proper privileges on the control plane. – VBlades May 03 '22 at 03:41
4

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:

enter image description here

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.

Reg Edit
  • 6,719
  • 1
  • 35
  • 46
  • 1
    You should have the same account with the same password on both original and target servers to avoid "Insufficient permission to create a database copy on server 'sourcesrv'" error – Michael Freidgeim Feb 22 '18 at 02:10
  • 1
    @MichaelFreidgeim yep, that's what the last para. of my answer says :) – Reg Edit Feb 22 '18 at 19:33
  • 1
    This works for me across subscriptions without having matching SQL Server accounts. I am a member of the server active directory admin group on both source and target servers, and connecting using AD authentication with MFA. – paulH Mar 25 '19 at 11:22
2

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.

Joel
  • 15,654
  • 5
  • 37
  • 60