31 Days of Disaster Recovery

Day 4: Back That Thang Up

Here we are at day 4 in my series focusing on disaster recovery. It’s Friday, so this will be a quick one for today. I want to talk about the importance backing up everything that needs it and point out some of the often overlooked things that you should be backing up.

System Databases

I’ve said it before (T-SQL Tuesday #19 – Beyond Backups) and I’ll say it again. Back up your system databases: master, model, msdb, and distribution. You should also be backing up your resource DB as well. You can back up the resource DB (mssqlsystemresource.mdf and mssqlsystemresource.ldf files) by just copying them to your backup location. This can save you from losing a lot of critical data. If you lost master database and you have to rebuild master, you will lose everything in msdb and model as well. It can turn a difficult situation into a terrible one. Being a lazy an efficient dba means doing simple things (like backing up the system databases) to save yourself a lot of work later down the line.

Certificates and Master Keys

If you are using certificates for creating asymmetric keys, authorizations, or securing securables (like procedures), then you need to consider the ramifications if you need to re-apply the certificate. For example, if you need to move a database with encrypted data to a new server, you may need to decrypt and re-encrypt the data. You will need the original certificate to do perform this action. You can export the certificate to a file using the BACKUP CERTIFICATE command. Likewise, you should export the database master key to a file using the BACKUP MASTER KEY command.

The backed up certificates and master keys should be immediately removed from the server and stored in a safe, secure location. I like to store them in source control that is securely protecting (in a tree only the DBAs have access to) and is itself getting backed up. Additionally, if you use a password to secure the exported files, those passwords should be stored securely using whatever secure method you are using to protect service account passwords. Preferably in something that is secure and gets backed up in it’s own right.

SSRS Encryption Key

If you lose your SSRS instance and you need to connect a new one to the existing databases, you will need to import the original encryption key to be able to read sensitive data. If you encryption key is not available, you can generate a new encryption key, but it will wipe out all existing sensitive data. I was called in to consult on a scenario where someone had done this, and their reporting server had over 600 subscriptions added by users. They lost all of the user information related to those subscriptions. They had to track down the users and then manually re-enter their email addresses to fix the subscriptions. They also lost the passwords to all of their data sources for the reports, but that was minor to fix compared to the broken subscriptions.

So please ensure that you are exporting the encryption key to a file and storing it securely. Don’t leave it on the server itself. Move it to a secure location that gets backed up like source control.

SSAS Databases

Yes, there are databases inside Analysis Services, and yes, you can AND SHOULD back those up. You use XMLA to back up the SSAS databases. To automate this process, create a SQL job in the regular database engine that connects to SSAS and executes the XMLA code. It’s actually very simple to do.

Here are sample backup and restore commands in XMLA for reference:

c:\bakAdventure Works DW 2012.abf





c:\bakAdventure Works DW 2012.abf

Adventure Works DW 2012

true

IgnoreSecurity

C:\Program FilesMicrosoft SQL ServerMSAS11.MSSQLSERVEROLAPDATA

Summary

The key point of today’s post was to get you thinking about more than just backing up your main user databases. Yes, those are key in terms of data loss potential, but keeping everything else that is critical is also key in how long you will be down if there is an outage and key in the amount of effort involved with making your system whole again. Please make sure you are backing up the system databases, certificates, master keys, encryption keys for SSRS, and the SSAS databases. If you’re lucky, an outage might not even get noticed. If that happens, you know you’re doing your job right.

Day 5: Dealing With Corruption in a Nonclustered Index

Welcome to day 5 of my series on disaster recovery. I want to start digging into some corruption scenarios. We’ll start off with the easiest form of corruption to fix, a nonclustered index.

The generic steps we will go through for any corruption scenario are as follows:

1. 
   Identify the corruption (DBCC CHECKDB)
   
2. 
   Identify the objects and types of objects involved
   
3. 
   Take the appropriate steps to correct
   

Sadly, one of the biggest mistakes people make is to jump straight to the third step and start trying to fix things without even knowing what they are up against. Different objects have to be fixed in different ways. Taking the wrong action could cause unrecoverable damage and waste a lot of time. And please, please, PLEASE do not use the repair options of DBCC CHECKDB unless everything else is not possible.

Identifying Corruption

The first thing you need to do is to identify corruption. You will probably be performing routine integrity checks or you will be responding to a specific alert or error. If you have an error message, you will have the info needed for at least the one page. You may be tempted to take action on that one page, but I advise you to take a step back and run DBCC CHECKDB on the database first. There may be additional pages corrupted that force a different plan of action.

Use DBCC CHECKDB to get the full list of errors so you can see which pages are corrupt. I like to use the No_InfoMsgs option to reduce unnecessary chatter, the All_ErrorMsgs option to make sure all errors are returned, and the TableResults undocumented option to output the results in a more readable format. For this demo, I will be running this on a corrupted version of the AdventureWorksDW2012 database.

DBCC CHECKDB(AdventureWorksDW2012)

With No_InfoMsgs, All_ErrorMsgs, TableResults;

This returns a lot of errors for the same things. So it takes a little practice to know what to look for. You need to identify which errors are the real errors and focus on those. You will want to focus on the errors that tell you an object ID, index ID, partition ID, allocation unit ID, file, and page.



DBCC CHECKDB Output — Click to Enlarge



MSDB Suspect Pages Output