On Disaster Recovery and my SQL Rally 2011 Presentation

Yesterday, I saw a Twitter post regarding the speaker evaluation results from SQL Rally 2011 in Orlando, FL last May. I was surprised to see that my session was in the top 3 best sessions of the conference. I dug up the Excel spreadsheet containing my session evaluation results and began to read. I found one comment very fascinating (the only evaluation where I got very low scores) as the response pertains to the speaker’s knowledge of the subject. The comment was: “copy and paste coder.” I’ve been doing this specific presentation for almost 5 years now with a few tweaks every once in a while based on feedback from attendees. Yes, I live and breathe disaster recovery as part of my day-to-day job. However, there are several reasons why I do not type nor write code during my presentations. Here are a few of them:

1. A presentation is a performance: Many will disagree with me on this, especially experts who believe that to demonstrate their expertise, they should be writing code and doing live demos during a presentation. Whenever I go up the stage to deliver a presentation, I always think about the attendee/audience. My goal is not to display my expertise nor to brag about what I can do that the audience could not. I always remember that my presentations are not about me, but about the audience. Which is why I do a lot of preparation prior to delivery – research, writing an appropriate storyline (you got it right – storyline), selecting the right demos, building test environments, writing demo scripts, rehearsing my presentation, etc. Yes, I rehearse my presentations and I say it out loud. I do the best that I can to make sure that the audience will be entertained, engaged, enlightened, educated and encouraged. If I’m doing a presentation on disaster recovery, I even plan out what type of disaster will I be simulating. Doing this will help me make sure that I don’t go beyond the time limit that was alloted for my session while covering all of the items that I intend to. I’d be very happy if the audience will walk out of my presentation with something that they will do when they get back to their regular routine. I keep in mind what Dr. Nick Morgan, one of America’s top communication theorist and coach, always say:”The only reason to give a speech is to change the world.” So, if you’ll be attending a presentation I’m delivering in the future, I’ll assure you that you won’t be disappointed. 
2. Presentation time is limited: I hear presenters and speakers apologize for not covering the full content of their presentation. In some cases, you would see them breeze thru their slides as they get to the summary slide. If the presentation was rehearsed and scripted, they would know how long it will take to cover everything in their slide and add or remove as necessary. Copying and pasting code is my way of saying, “I value your time so much that I would rather copy and paste code so that I can move on to more important stuff than let you suffer from every typographical error I would make while typing.” As I said, many won’t agree with me on this but I need to focus on the more important content of the presentation.
3. Focus on the important: Same as the previous point. Enough said.

But what about disaster recovery? Yes, this is more than just a blog post about improving your presentation skills. The main reason why I copy and paste code, especially when doing a disaster recovery presentation is to prove a point: You want to accomplish your task with the least amount of time and the least amount of effort. This is because every minute you waste is a minute against your recovery point objective (RPO) and recovery time objective (RTO). Imagine having to recover a SQL Server database by applying the latest FULL database backup and a series of LOG backups. The more LOG backups that you need to restore, the longer it will take. Plus, if somebody is behind your back watching every move you make and asking when the database will be back online, you wouldn’t want that to last longer that it possibly can. Remember, in a disaster recovery incident, every second matters. For highly transactional databases that are being used for main line-of-business applications, every minute lost is revenue lost. Having these in mind, you would do everything you can possibly think of to recover the database as fast as you possibly can – even copy and paste code. In fact, I keep a dozen or so scripts in my repository that works as code generators – scripts that generate scripts. One of them is a script that reads thru my backup history stored in the MSDB database and creates a series of RESTORE DATABASE/LOG scripts that end up getting executed so that I don’t have to figure out when the last LOG backup ran and restore the backups in sequence. Would you call this cheating because I copy and paste code? I don’t know about you but I’d call this being creative when the rubber meets the road.

And one more thing, I will be delivering this presentation but a bit more on the non-technical side of things in the upcoming PASS Community Summit 2011 in Seattle, WA on 11-14 October 2011. If you intend to attend, drop by my session so we can talk about it more.

Let me know your thoughts. Do you copy and paste code when recovering a database?

Disaster Recovery Is More Than Just Technology Part 2: The Alphabet Soup

In my previous blog post, I talked about high availablity and disaster recovery (HADR) and how it is more than just the underlying technology that keeps the entire strategy intact. In this blog post, I’ll describe a few acronyms – sometimes called buzzwords – that are commonly referred to in HADR projects and implementations (I know I use them a lot when addressing questions regarding HADR.) These acronyms fall under the second P in my PPT for HADR – PROCESS. Every HADR project or implementation should first be able to define these acronyms well before they even purchase the hardware, software and technologies they intend to use. Let’s get going.



Recovery Point Objective (RPO). Simply put, RPO answers the question, “How much data can we afford to lose?” Every HADR project should be able to determine the acceptable amount of data loss and is usually measured in units of time. For example, if a highly critical application runs 24X7 and the stakeholders have defined the RPO to be one (1) hour, if the database that stores the data for the application crashes at 5:45AM and you are running regular log backups (for SQL Server) or redo log backups (for Oracle) every hour starting at 12:30AM (I’m pretty sure the Oracle guys would jump up on me by using this as an example), the 15-minute data loss would be acceptable as you have an RPO value of one (1) hour. The 15-minute data loss was derived from hourly backups running starting at 12:30AM and that the last backup ran at 5:30AM – 15 minutes before the database crashed. Now, defining the hourly backups was not decided upon based on guesswork, although, I see a lot of DBAs simply just use it as a standard. If it was, it’s time to define your RPO and determine the amount of acceptable data loss and review your backup strategies.



Recovery Time Objective (RTO). RTO answers the question, “When is my application coming back online after a disruption?” Together with RPO, RTO is also measured in units of time. Looking back at the earlier example, if the stakeholders have defined the RTO to be two (2) hours, the database, the application and whatever is necessary to use the application should be back online by 7:45AM.

 Service Level Agreement (SLA). As Wikipedia defines it, SLA is a part of a service contract where the level of service is formally defined. In my experience, this is commonly agreed upon by a customer and a service provider. You might be thinking that if you have an internal IT management team, chances are that you won’t have to deal with SLAs. However, bear in mind that in order for a computer application to be online, it relies on hardware which needs to be covered by a vendor warranty with associated service levels should the hardware needs to be serviced or replaced during a disaster, a media on which it can be accessed – either via the Internet or your local network – which also needs to be covered by vendor or in-house service level agreements. This is a very important item in your HADR project as anything external to your team will definitely affect your RPO and RTO. For example, if your highly critical application has been restored within two hours and the data loss was less than an hour, you may have met your RPO and RTO but if the Internet connection that allows your users to access the application is still not restored after two hours, forget about achieving your RPO and RTO. Technically, from the point of view of users, your application is still not accessible. Which is why when you’re dealing with vendors or service providers, make sure that your agreed upon SLAs meets your RPO and RTO.



I’ve only scratched the tip of the iceberg on the different components that make up the PROCESS part of an HADR project. What I find surprising is that whenever I start asking about RPO/RTO/SLA values from customers asking for an HADR solution, they immediately respond with “I want zero downtime and zero data loss for my application.” They simply think that their application deserves an RPO and RTO value of zero (0). What they don’t realize quite yet is that as RPO and RTO approaches zero (borrowing jargons from integral calculus that, as your limits approach zero), the cost exponentially increases. And when we start talking about costs, customers start re-evaluating their HADR strategies the way they are supposed to be. This is where I really like the discussions to go because they will look at each application and the corresponding database differently and categorize them accordingly – from not-so-critical to highly critical. And they start crunching numbers to determine how highly critical an application can be and if it does merit a near-zero RPO and RTO. Take for instance an e-commerce site that generates an average of 50 transactions per minute (which is a relatively low volume these days) at US$10 per transaction. That is equivalent to US$500 per minute. Losing an hour’s worth of data due to downtime or data loss would mean US$30,000. Having an HADR solution in place should be justifiable enough to protect a US$30,000 worth of transactions in an hour. Your strategy would also consider if the transactions are only coming in between 5:00AM until 9:00PM as you wouldn’t want to be investing a lot for a solution that doesn’t protect anything after those hours.

 So when you plan your next HADR project, think about these concepts and define your RPO/RTO/SLA. It will definitely keep your perspectives right and plan accordingly. In my next blog post, I will be talking about high availability implementations, more examples and how they should address your RPO/RTO/SLA. Keep posted. Plus, if you’re in the Washington DC area this weekend, catch me do a presentation on SQL Server Disaster Recovery Techniques for SQL Saturday #61

Disaster Recovery is More Than Just Technology Part 1

While I was at the PASS Summit 2010, I've spent a fair amount of time at the Ask-the-Experts table on high availability, disaster recovery and virtualization. Conference attendees with different requirements on high availability and disaster recovery come to these tables and ask questions.


I've spent a fair amount of time doing high availability and disaster recovery (HADR) in my previous life as a data center engineer focusing on the Microsoft platform. My previous organization sold high availability and disaster recovery solutions to customers like crazy, highlighting the fact that the solutions are more than just the technology aspect. Every time I talk about HADR in my presentations, I focus on the three main ingredients to have a successful implementation - people, process and technology (PPT). Note that technology is at the end of the list as the people and the process components should come first.


What I found out at the PASS Summit gave me insights as to how people approach HADR (and I thought I only saw these on the newsgroups and forums as I answer their questions.) Most SQL Server DBAs (and maybe even a lot of IT professionals) want a technical answer to their HADR problem. They want to know if failover clustering, database mirroring, replication or log shipping is the best solution to their requirement. What's funny is that when I ask them about what their RPO/RTO/SLAs are, they scratch their head and ask what those acronyms are. And when I start explaining these acronyms to them, they still want to hear what the best solution is for their requirement.


As I prepare for my presentation on Disaster Recovery Techniques for SQL Saturday #61 in Washington DC, I'll be writing a series of articles about disaster recovery and what RPO/RTO/SLAs are and how they fit into the whole disaster recovery strategies. Before I dive into the "technology" part of the PPT ingredient for a successful HADR implementation, I will talk about the people and the process part first. Why? Because these two will drive the technology part of the whole strategy. And if you're in the Washington DC area, feel free to drop by at the SQL Saturday event.

Why WIndows Instant File Initialization for SQL Server 2005 matters

SQL Server 2005 supports Database Instant File Initialization which skips zeroing out data pages that can reduce the time when performing operations like creating databases, adding files to an existing database, increasing the size of an existing database file manually or thru autogrowth or restoring a database or filegroup. I've written an article in MSSQLTips.com highlighting it's importance both in a disaster recovery and operational performance scenarios. I ran a few tests as well to simply answer a query in the forum regarding the topic. I ran my tests on a VMWare image on a DELL PowerEdge 2650 with 4 X 2.8 GHz CPU and 3GB RAM. The image is running Windows Server 2003 Enterprise Edition with SQL Server 2005 Enterprise Edition as this is an "Enterprise Edition only" feature. I've created a 10GB-sized database which took only 0.1 seconds using instant file initialization. Without it, the same took about 2:10 minutes. Imagine doing a restore for a 500GB-sized database. That will save you a lot of time on the restore process. Same goes with file auto growth. The difference in the amount of time it takes to do a restore would definitely matter when you're dealing with very large databases. Imagine trying to restore a 500GB-sized database. You'll probably agree after trying it out for yourself

Broken BACKUP and RESTORE sequence?

Have you ever had the to scratch your head because your database backups won't work even if you are practically sure that you have tested them properly? Imagine this - you have a regular FULL and DIFFERENTIAL database backups working together as part of your backup process. Your FULL backups run every Sunday and your DIFFERENTIAL backups run everyday. Then, all of a sudden on a particular Wednesday, your developers decided to restore a FULL backup of your production databases into your test environment to test a functionality. What happens? The backup sequence is broken. DIFFERENTIAL backups contain the changes since the last FULL backup. If you are following the sequence and working with the backups generated by your backup process, you would have done some tests using the Sunday FULL backup and the latest DIFFERENTIAL backup. Not in this case. You would need the FULL backup created on that specific Wednesday (which unfortunately was deleted after being restored on the test environment). Let me show you a sample code to demonstrate


USE master
GO

--FULL DATABASE BACKUP on Sunday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_FULL.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--FULL DATABASE BACKUP ON Wednesday CREATED BY YOUR DEVELOPERS
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10
GO

USE master
GO

--DIFFERENTIAL DATABASE BACKUP AFTER THE LAST FULL BACKUP
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH DIFFERENTIAL, INIT, FORMAT, STATS=10
GO

If you're the DBA, you might be thinking that you still have your backup sequence intact and this is what you might have done should you need to restore

--RESTORE SEQUENCE STARTS
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_FULL.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

--FAIL DUE TO INCONSISTENT RESTORE SEQUENCE
RESTORE DATABASE AdventureWorks
FROM DISK = 'D:\AdventureWorks_DIFF.BAK'
WITH REPLACE, NORECOVERY, STATS=10
GO

Now, there are a couple of ways to solve this problem. One is to create a trigger on your MSDB database to trap events written on your backupset system table and check for the type column for D values. At least you get to be notified for any FULL database backups outside of your normal backup process. In SQL Server 2005, it's as easy as using the COPY_ONLY option in the BACKUP command. This specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. Make sure that your operational DBAs and developers who work on SQL Server 2005 know how to use this option whenever thay run ad-hoc backups. Here's how to use the COPY_ONLY clause on your BACKUP commands (with emphasis on COPY_ONLY)

--FULL DATABASE BACKUP on Wednesday
BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksD.BAK'
WITH INIT, FORMAT, STATS=10, COPY_ONLY
GO

So you can recover from database snapshots...but...

SQL Server 2005 has given us the option to create database snapshots to create a point-in-time image of the database. This gives us the option to restore from a point-in-time in case of user errors, like maybe accidentally truncating a table. MSDN has provided us a procedural approach on how to restore from database snapshots. But what I am more concerned the most is that I have already lost a lot of data from the time the snapshot was created to the point in time that the error occurred. And this is where your understanding of what happened and how you implement your disaster recovery plan come into the picture. As far as Microsoft is concerned, restoring a database from a snapshot overwrites the original source database. But here's what I'd do. I'd first backup the tail of the log before I restore from the database snapshot. After restoring from the snapshot, I'd restore my backups into another copy of the database and restore the tail of the log. This will be the state of the database after the accidental error occurred. Since I am concerned about the new records that were added after the accidental error, I would retrieve those from my database copy and isert those on my restored database. This will take a lot of work as you will be comparing the tables from the original and the copy database. At least you managed to restore the records faster than restoring from a FULL backup. There is no native way to solve this but there are a lot of third-party utilities that can just rollback a specific command from the transaction log. You can use the TableDiff utility to do this although it wasn't meant to solve this type of problems

You don't trust your database backups? Use mirrored backup media sets in SQL Server 2005

What could be more frustrating than knowing that your database backups went missing? This is specifically true if you are dealing with transaction log backups which are dependent on log sequence numbers. You don't want to lose a single transaction log backup in the chain. In previous versions of SQL Server, we just execute a copy command (or even ROBOCOPY) to copy the transaction log backups to a different location. In SQL Server 2005, we have the MIRROR TO clause. This specifies a set of one or more backup devices that will mirror the backups devices specified in the TO clause, which could be a tape, disk or network location.

To use the MIRROR TO clause, see the sample script below

BACKUP DATABASE AdventureWorks
TO DISK = 'D:\AdventureWorksDB2.BAK'
MIRROR to DISK = 'F:\AdventureWorksDB.BAK'
WITH INIT, FORMAT, STATS=10
GO

This creates a mirrored copy of the database backup of the AdventureWorks database to the F:\ drive. Although this creates a mirrored copy of the backup, it will definitely take quite some time to complete as it is writing on all the mirrored media set, thereby, increasing database your backup window. I did a test on this by using the MIRROR TO DISK clause with the AdventureWorks database and it takes like 10 times longer to do a backup with this option - the speed, of course, would be dependent on where you mirror your backups and its IO performance

I'd probably still stick to using the copy command in my backups as long as I get the same result. For more information on using mirrored backup media sets, check out this MSDN article

You need to backup the mssqlsystemresource database files

So, you think you have all the necessary database backups you need? Well, think again. This might be your mindset after moving from SQL Server 2000 to SQL Server 2005. SQL Server 2005 includes a read-only, hidden database that contains all the system objects. It comes in the form of mssqlsystemresource.mdf and mssqlsystemresource.ldf files located in :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ by default. This database is required by the master database and will definitely affect how your SQL Server service behaves during restarts. If you move the master database, you should also move these files no the same location. Since it is a hidden database, SQL Server won't be able to backup this database as part of your database maintenance plan. One approach you could use is to xcopy it to the location of your database backups on a regular basis.

MSDN has a documentation on what this is all about but let me tell you one thing - you MUSt include this in your backup procedures. If you want to have an idea of how critical these files are, stop your SQL Server 2005 service and rename the mssqlsystemresource.mdf file. You won't be able to restart your SQL Server service afterwards