Transferring a SQL Azure Database

[This article was contributed by the SQL Azure team.]

The original blog post doesn’t work as written, since the destination user executing the CREATE DATABASE must have exactly the same name as SQL Azure Portal Administrator name on the source database; i.e. you can only copy from a database where you know the SQL Azure Portal administrator name and password. I will be revisiting this topic of how to transfer a SQL Azure database between two different parties in another blog post.

Imagine that you are an Independent Software Vendor (ISV), have a purchaser for your Windows Azure application and you want to transfer a copy of the starter database to the customer. However, you don’t want to give them the Administrative login and password from the SQL Azure portal. Or perhaps you want the test team to be able to copy the production database into their service account. To copy the database to another service account on another SQL Azure server, you can work together to create a common user that has permissions to transfer the database. This blog post talks about how to create that user and complete the transfer.

Source Server

When connected to the master database of the source server as the source server administrator from the SQL Azure Portal execute this statement to create a login on the source server.

 CREATE LOGIN copylogin WITH password='1231!#ASDF!a';

Now change your connection so that you are connected to the source database and execute this statement:

 CREATE USER copyuser FROM LOGIN copylogin;

In order to copy from the source database the user needs to be the have db_owner permissions. To grant those permissions while connected to the source database execute:

 EXEC sp_addrolemember 'db_owner', 'copyuser';

Destination Server

Now that the ISV has established a new account on their machine, they need to communicate the login, password and data center to the buyer so they can establish the same account on the destination server. The destination server needs to be in the same data center as the source server. Only if the login and passwords are the same can the copy of the database work. Here is what the buyer needs to do:

When connected to the master database of the destination server as the administrator from the SQL Azure Portal execute this statement to create a login on the destination server.

 CREATE LOGIN copylogin WITH password='1231!#ASDF!a';

Now execute this statement to create the user while still connected to the master database:

 CREATE USER copyuser FROM LOGIN copylogin;

Then give the user dbmanager permissions on the destination server.

 EXEC sp_addrolemember 'dbmanager', 'copyuser';

Now copyuser is set up to copy a database to this server.

Executing the Copy

To execute the copy, the buyer logs in as copylogin to the master database of the destination server on SQL Azure. The buyer then execute this statement:

 CREATE DATABASE [AdventureWorksLTAZ2008R2] AS COPY OF [srcServer].[AdventureWorksLTAZ2008R2]

With the database names and the source server correctly filled in. The buyer then can monitor the copy by querying sys.dm_database_copies like this:

 SELECT * FROM sys.dm_database_copies

Cleaning Up

Once the database copy is completed, the buyer needs to remove the copyuser user from the database to secure the database. This is done because the ISV knows the login/password. To delete the user execute this connect on the destination database using the administrator login from the SQL Azure portal:

 DROP USER copyuser

Then connect to the master database of the destination server with the administrator login and execute this:

 DROP LOGIN copylogin

The ISV can then drop the database on the source server when the buyer confirms that they have a good copy. To secure the ISVs computer the ISV connects to the master database on the source server and executes this statement:

 DROP LOGIN copylogin

Protecting the Source Database

There is one flaw in the strategy above, you have given the buyer db_owner permissions to your database. The problem with this is that they could drop the database, or cause some other problem instead of copying it. Instead it is better to create a copy of your original database on to your server, and then grant the copylogin login db_owner permissions on the copy of the original. This protects the original and if the buyer damages the copy, you still have the original.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.