Tuesday 19 November 2013

Can I COPY a database? Can I?

I decided to write a post about a pitfall of the WASD COPY command.  A pitfall I experienced during what should have been a pretty routine DBA task, albeit in the cloud.  Retracing the steps however, led me to a surprising result.

For those of you unfamiliar with WASD or the COPY command, it is a TSQL command used to copy a database.  It’s straightforward to use, is transactionally consistent, and makes the process of creating a database copy very easy indeed.  You can even copy the database to a different database server, providing both servers are hosted in the same datacentre.  The syntax is also easy to use (courtesy of BOL):

CREATE DATABASE destination_database_name
    
AS COPY OF [source_server_name.] source_database_name
[;]

Aside from the variable performance (due to it creating 3 replicas in total as per WASD architecture, with multi-tenant nodes meaning unpredictable network and server speeds), it is a useful tool for the DBA, and the developer for that matter.

You’ll notice from the syntax that copying a database across servers requires you to run the command on the destination server, referencing the source server.  This page, again in BOL, provides some information about permissions when copying across servers.  I’ll not regurgitate the article, but I do want to draw focus to key point - the login you use becomes the new database owner, and the SID of that login is assigned.

So let me get back to where I started, the “pretty routine” DBA task.  I was asked to copy an application database from the development server up to the test server.  Not uncommon.  There are a few ways of doing this, but both database servers were in the same datacentre, so I opted to use the COPY command.  Also not uncommon is to have different credentials across environments, and this was no exception.  As I needed at least dbmanager permissions on both servers, the next logical step was to create a login on the destination server that matched the admin account on the source.  So that’s what I did:

create login DevAdmin with password = 'MyDevPa55w0rd!';
go
create user DevAdmin from login DevAdmin;
go
exec sp_addrolemember 'dbmanager', 'DevAdmin';
go
 
Note, this account is the admin account on the source server, but needs to be added as a user in the master database on the destination server, and added to the dbmanager role.
 
Next, I log in with the new DevAdmin user (on the target server), and run the COPY command:
 
create database myAppDb
as copy of <dev server>.myAppDb;
go 

The command completes successfully, and the database is now being copied asynchronously.  We can track its progress using the following query:

select state_desc
from sys.databases
where name = 'myAppDb';

When in progress you will see a value of ‘COPYING’, and when successfully completed the state will show as ‘ONLINE’.

Being a good DBA, after the copy was complete and I had applied the test credentials, I tidied up after myself.  I dropped the user in the master database, then dropped the login.

drop user DevAdmin;
go
drop login DevAdmin;
go

Done.  Testing was underway, and I was pleased with how efficient I had been.

It was now time to copy the database into Pre-Production.  Well, it was so easy last time, why use a different method?  I created the test admin credentials on the Pre-Production server in the same way as above and started the copy:

create database PreProdAppDb
as copy of <test server>.myAppDb;
go 

But the result was NOT what I expected:

image

I intentionally changed the name of the target database here, to highlight that this error message relates to permissions on the source database.  Yup, even though we’re using the administrator credentials, we can’t copy it.  Turns out you can’t COPY a database unless you are the database owner – admin or not.  What about the same server?  Nope, that doesn’t work either, same error message.

WOW.  I mean, WOW.

<several minutes of stunned silence>

After recovering from this bombshell and regaining my composure, I headed down the thought process of the on-premises world.  I’ll change the database owner.  Yes, let’s do that:

alter authorization on database::myAppDb to TestAdmin;

But:

image

Sure enough, this is confirmed in BOL:

image

I’m going to have to recreate the user that is the database owner.  So I re-run the SQL from earlier to create the DevAdmin account.  Can I copy the database now, even on the same server?  Well, no I can’t.  Remember the point I drew attention to at the beginning of the post, about the database copy obtaining a different SID on creation?  Well recreating the login assigns a different SID, which is different to the owner SID of the database.  The following queries confirm this:

select name, sid
from sys.sql_logins
where name = 'DevAdmin';

select name, owner_sid
from sys.databases
where name = 'myAppDb';

Results:

image

image

Off to BOL again to see if we can recreate the login with the correct SID.  Awwww, no luck then:

image

And this is the point where it got interesting.  I hit this error around a year ago, and have only recently decided to blog about it.  I wanted to show the error message, and was expecting something like this:

image

But instead it completed successfully!

image

WOW again.  Seems the Azure team have added the ability to set the SID on creating a login, but not updated the documentation yet.  It ruined my attempt at publicising this pitfall, but on the other hand it gave us a solution.  Those original credentials, in my case the DevAdmin login, must be carried through to each server you wish to copy the database to, as only the owner of a database can copy it.  Other options exist as a workaround, the easiest alternative being to export/import the database to change the owner.  However this is not transactionally consistent (ironically without a database copy) and requires a little more effort as well as BLOB (or local) storage.

The COPY command is a great feature of WASD, but suffers from some difficulties around the permissions required to perform the operation.  In an attempt to improve the user experience, I have raised 3 connect items, please up-vote them!

Allow the server admins and users in the dbmanager role to COPY a database, regardless of owner:

https://connect.microsoft.com/SQLServer/feedback/details/808957/wasd-allow-admin-or-users-in-dbmanager-role-to-copy-a-database-regardless-of-owner

Allow the server admin to change the owner of a database:

https://connect.microsoft.com/SQLServer/feedback/details/808958/wasd-allow-the-database-owner-to-be-changed

Update BOL to show that CREATE LOGIN..WITH SID is now available:

https://connect.microsoft.com/SQLServer/feedback/details/808955/wasd-bol-incorrect-create-login-with-sid-now-works

Thanks for reading.