Sunday, 13 November 2011

SQL Server Disaster Recovery to a Server with a Different Name Part 1– Logins

Being a DBA that values job security, you take regular backups of your databases. But have you really thought through how you will restore the backup and connect it to your application if the original server is lost in a disaster?

A common approach is to have some form of ‘standby instance’, on a server that has a different name and IP address. In the event of a disaster, the databases can be restored to the standby server and clients redirected. This may be automated using Log Shipping or Database Mirroring.

The problem is that a database doesn’t exist in a bubble. There is a heap of stuff going on at the instance level that you need to hook up to. This is stored in the ‘master’ database – the heart of the instance.

(Note: SQL Denali introduces ‘contained databases’, which offer an alternative approach.)

According to MSDN, the master database

…. includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

Restoring master onto a server with a different name isn’t a supported option, so it is important to have a method of recreating all the bits you need.

Let’s start by looking at logins, database ownership and default databases.  You may consider keeping logins up-to-date on the standby server by creating them at the same time as you create the login on the primary server.  However, even the most rigorous change control systems sometimes fail. You could automate the transfer of logins using the SSIS transfer logins task.  However, this has major disadvantage as it does not transfer passwords and will not synchronize role memberships. Finally, no matter what the approach, you can’t set the default database for a user until the database is actually online.

So, the final safeguard is to have all your logins scripted, so you can rebuild your logins at the same time as you restore your databases.

A good order for recovery is:
  1. Create the logins, without the default database;
  2. Restore the database (or, if using log shipping or mirroring, bring the database online);
  3. Set the default database for the login;
  4. Set the owner for the database;
Microsoft Support has published scripts that help with the scripting of logins. Here are much adapted versions, which filter out some logins that should not be transferred. These only work for SQL 2005 and above.

First,  create a stored procedure, sp_transfer_logins,  that scripts Windows and SQL Logins. It will not transfer logins based on certificates or asymmetric keys.

This will give an output similar to this for Windows logins:
-- Login: CORP\service-application 
CREATE LOGIN [CORP\service-application] FROM WINDOWS
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'bulkadmin '
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'dbcreator'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'diskadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'processadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'securityadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = 'serveradmin'
EXEC master..sp_dropsrvrolemember @loginame = 'CORP\service-application' , @rolename = N'setupadmin'
EXEC master..sp_addsrvrolemember @loginame = 'CORP\service-application', @rolename = 'sysadmin'
For SQL logins, the output will be similar to
-- Login: application
CREATE LOGIN [application] WITH PASSWORD = 0x01007618BD634A64BC0F162BD0D9B7EDA78EF461FF30CF93D893 HASHED, SID = 0x6A5A98F69F2503478556EC8A90DEA78B
ALTER LOGIN [application] WITH CHECK_POLICY = OFF
ALTER LOGIN [application] WITH PASSWORD = 0x01007618BD634A64BC0F162BD0D9B7EDA78EF461FF30CF93D893 HASHED
ALTER LOGIN [application] WITH CHECK_POLICY = OFF
ALTER LOGIN [application] WITH CHECK_EXPIRATION = OFF
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'bulkadmin '
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'dbcreator'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'diskadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'processadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'securityadmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = 'serveradmin'
EXEC master..sp_dropsrvrolemember @loginame = 'application' , @rolename = N'setupadmin'
EXEC master..sp_addsrvrolemember @loginame = 'application', @rolename = 'sysadmin'
The procedure output contains both CREATE and ALTER statements, as your logins may already exist on your standby instances. You need to set CHECK_POLICY=OFF before you reset a hashed password and then turn it back on again if necessary.


Next, create a stored procedure, sp_transfer_logins_defaultdb, which scripts the default databases for Windows and SQL logins. The output is similar to

 

ALTER LOGIN [MYDOMAIN\service-application] WITH DEFAULT_DATABASE = [applicationdb]
ALTER LOGIN [application] WITH DEFAULT_DATABASE = [master]
Finally, create a stored procedure, sp_transfer_dbowner, which scripts the database owners. This has output like:


ALTER AUTHORIZATION ON DATABASE::[ApplicationDB] TO [CORP\service-application]

Now, these stored procedures should be scheduled and the output sent to a location, such as a file share on the DR machine.

 

It is tempting to set up a SQL Agent Job step that runs the SP as a T-SQL job step that send outputs to a file on a remote share:

 

image

image




However, the output is rather unhelpfully littered with [SQLSTATE 01000] messages.

 

ALTER AUTHORIZATION ON DATABASE::"ApplicationDB" TO "CORP\service-application" [SQLSTATE 01000]

An alternative approach is to create the job step as a CmdExec task that calls SQLCMD to run the SP:
sqlcmd -E -S LOCASERVER\DAIT -d master -Q "exec master.dbo.sp_transfer_login" -o "\\REMOTESERVER\Disaster Recovery Scripts\LOCALSERVER_INSTANCE_sp_transfer_login.sql"



image


Repeat for the other two stored procedures, schedule to run nightly, and your scripts will be available on your DR server for when the disaster hits!

Scripts:

sp_transfer_logins
sp_transfer_logins_defaultdb
sp_transfer_dbowner


References:

 



2 comments:

  1. Thanks for the article, found it helpful. one issue i seem to see when rerunning the script to create the logins:

    Supplied parameter sid is in use

    Again, this is after I ran the script once alreadr on the DR box

    ReplyDelete

Please leave a comment.