Resources and Q&A from our SQLConnections pre-conference workshop: Disaster Recovery – from Planning to Practice to Post-Mortem

Here are a couple of links from Paul’s blog – related to Sunday’s session (#1 was related to the DB Maintenance pre-pre-conference workshop):
Conference Questions Pot-Pourri #2: Database mirroring
CHECKDB From Every Angle: Why would CHECKDB run out of space?

So, have fun and we both look forward to seeing you tomorrow during the official Connections sessions.

The rest of this post contains random but helpful stuff (and in NO particular order at all). And, there’s still more coming!

SQL Server 2005 Data Encryption
SQL Server 2005 added many new features around security and data protection – one of which is data encryption. Data encryption protects the data from being accessed by those who cannot “decrypt by key” (based on security rights/certificates used when the data was encrypted)… Well, there’s a lot more to it than I really want to get into here BUT, Bob has done some great Security posts on his blog (http://www.SQLskills.com/blogs/BobB) and in his Security Best Practices whitepaper.

What I want to detail here are the administrative repercussions of having encrypted data in a database that’s backed up and restored to a DIFFERENT server. See, data encryption is based on a database master key (DMK) this database master key is used to encrypt all data within the database and also used as a level of abstraction from the SMK (Service Master Key – which is tied to the server). However, the DMK only works (by default) with the service (SMK) with which it was created. If a database is backed up and then restored to another server – the DMK has to be opened and re-associated with the SMK of the new server… a very easy thing to do – IF you know the password that was used when the original DMK was created. When you backup and restore to the new server, use these commands to re-associate the DMK with the new server’s SMK:

USE DBWithEncryption
go

— Open the DMK with the SAME password used when created:
OPEN MASTER KEY
DECRYPTION BY PASSWORD = ‘strong password that is not easily guessed or even remembered…yes, you might even need to write these down and store them in a safe!!’
go

— Re-associate the DMK with the SMK of the new instance:
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY
go

As an alternative, you could backup the SMK from the source server and restore it as a new SMK for the destination server… but, that implies:
1) you still have access to the source? (some DR situations this might not be possible)
2) you don’t mind using the same SMK for multiple servers (which reduces the overall level of security in the data on these servers.

Now, all of this also has an impact on Database Mirroring since Database Mirroring requires that you “prepare” the mirror before you can establish the mirroring partnership (which is a backup/restore across machines). So, a logical question is, what happens with encrypted data if you failover? The answer is that you must provide the automatic decryption of data on the mirror using the sp_control_dbmasterkey_password procedure. Read more about it here: “Managing Metadata When Making a Database Available on Another Server Instance“. Or, you could MANUALLY (and only when a failover occurs), re-associate the DMK on the new server. However, this would impact your application and effectively create downtime if someone wanted to access encrypted data before the DMK has been reassociated with the new server’s SMK.

Information, Entities, and Objects That Are Stored Outside of User Databases
And, in addition to encryption, there are many other issues that you could run into when dealing with backup/restore, log shipping and/or database mirroring – when you’re doing this to a different server. As for a quick list – how are you going to migrate the following to your secondary server:

• 
  
  SQLCLR enabled (sp_configure and/or any other settings that could impact database functionality)


• 
  
  Logins (see KB#246133: How to transfer logins and passwords between instances of SQL Server)


• 
  
  Linked Server settings…


• 
  
  External Batch files used by maintenance, ETL, SQLCLR, etc.


• 
  
  SQL Agent Jobs (this can be easily scripted – but what if they change?)


• 
  
  SSIS Packages (same… as jobs)

The books online section titled: Managing Metadata When Making a Database Available on Another Server Instance has an excellent section that details many of the things to look out for… Start with these lists and BOL topics and then be sure to thoroughly test your application both during regular operations AND off-hours batch/maintenance operations (you’d be surprised at what you might find when you do a large index rebuild or defrag OR some large/complex ETL processA) AND, be sure to test your application on BOTH the Principal AND the mirror AFTER failover (when the former mirror becomes the new principal).

SQL Server 2005 Resources

• “SQL Server 2005 Failover Clustering” by Allan Hirt
  
• Pro SQL Server 2005 High Availability (Hardcover) by Allan Hirt

SQL Server 2000 Resources

• “SQL Server 2000 Failover Clustering” by Allan Hirt
  
• Microsoft SQL Server 2000 High Availability (Paperback) by Allan Hirt, Cathan Cook, Kimberly Tripp, and Microsoft Corp. For this book, Chapter 9 can be downloaded for free from SQLskills. This chapter can be really useful for detailing concepts and technical details about Recovery Models, Performance and Recovery in “Database Environment Basics for Recovery” which still *largely* applies to SQL Server 2005 as well!
  
• Support WebCast: Microsoft SQL Server 2000: Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering, from February 7, 2003

And, so that’s it for this entry. Yes, there’s still more to go (from a few questions that I’m waiting on from other folks). So, I do hope to have a few more posts this week AND a post or two what the sessions I saw today (for example Richard Waymire’s session on Management Tools and Sunil Agarwal’s session on Data Compression).

See you tomorrow!
kt