simplifying disaster recovery with dbatools

Over the weekend, I presented a session in Glasgow called doomsday prepping with dbatools at SQLGLA, a community event hosted by Craig Porteous, sql_bob and Louise Paterson.

It was a lot of fun, even though the audience put no effort into winning the MRE give away 😂

Now I’ve got some ready-to-eat beef tacos when the zombie apocalypse hits!

down to business

When we talk about Disaster Recovery or DR, it’s often coupled with the term High Availability or HA. Here are some definitions from my graduate course on HADR.

high availability

  • Deals with minor outages, and failover solutions are automated
  • The goal is to restore full system functionality in a short time

disaster recovery

  • Deals with major outages such as natural and man-made disasters
  • Focuses on manual processes and procedures to restore systems back to their original state
  • Characterized by a phased approach to restoring the primary site

In the context of SQL Server, HA would be Availability Groups (AG), Failover Clustering (FCI), Log Shipping and more. I won’t be addressing High Availability in this post, however.

disaster recovery

why

There are a number of articles discussing the importance of disaster recovery. Here are the three I list in my presentation:

  • Certain federal regulations require development of DR plans
  • Business partners and customers often demand proof of disaster recovery plans
  • Supporting ongoing availability of IT services for business continuity

While I can’t find the original reference to cite, I once read in my HADR class that Cleveland State University did a study about the importance of DR, and they found that:

A company that experiences a computer outage lasting more than 10 days will never fully recover financially. Within five years, 50% of those companies will be out of business.

That’s insane! And a very solid reason to have a well-tested DR plan.

who

You ever read that story on reddit about the kid who accidentally dropped the production database on his first day on the job? The CTO fired him and threatened legal action. As if.

Companies like Amazon correctly recognize that such disasters are a team effort and disaster recovery is the responsibility of the entire organization.

Here’s how you can do your part.

sql server disaster recovery

Tracy Boggiano has an awesome, in-depth presentation about DR titled Disaster Recovery: Where to Begin that I recommend checking out. It was the primary source for my own research.

databases

When it comes to SQL Server and Disaster Recovery, Microsoft offers a number of options.

  • Backup/Restore
  • Replication
  • Log Shipping
  • Mirroring
  • Multi-site Failover Clustering
  • Availability Groups
Tracy’s slide deck didn’t mention it, but Bacpacs and Dacpacs are a potential option as well.
  

scenarios

the faster you want to get data back, the more you will pay

Need a quick DR solution? Use Ola Hallengren’s free and open source SQL Server Maintenance Solution to schedule your backups, then use robocopy to mirror those backups to a secondary data center or the cloud.

You can reliability recover your data this way but it won’t be immediate.

If you need to recover your data far faster, you can use Distributed Availability Groups. This method is faster but far more costly, because you’d potentially need:

  • Another data center
  • More SQL Server licenses
  • More Windows licenses
  • More resources & storage
  • More support staff

everything else

Microsoft rightly places a lot of emphasis on database DR, but what about everything else? Things like:

  • Logins
  • SQL Agent
  • Extended Events
  • Linked Servers
  • Credentials
  • Audit
  • sp_configure
  • Central Management Server
  • Database Mail
  • System Triggers
  • Endpoints
  • Custom errors
  • Replication
  • Availability Groups
How do you DR these? You can backup the required databases for some things – like msdb restores everything in Agent. Or, if it’s available, you can right-click hundreds of objects, one-by-one, and export them.
 

introducing simplified disaster recovery

dbatools can help ease your DR, all in one convenient command. No, not good ol’ Export-DbaScript which is essentially the command line equivalent of the screenshot above.

Now, dbatools offers a whole new command, written specifically for the DR presentation: Export-DbaInstance

Export-DbaInstance is a wrapper for over 50 export commands. This is similar to Start-DbaMigration which is a wrapper for a bunch of copy commands.

image

The databases export is an export of all the restore commands from the last log backup. So full, diff and logs.

Looking good!

And now for a demo

Here is a slightly modified version of the demo I gave in Glasgow, commented for your enjoyment.

Excellent! How gorgeous is that Pester test? Well, the output is hard to read, sorry. But the results are magic 🔮

image

And a YouTube link!

I also had a blast presenting this session virtually for the Portland PowerShell User Group if you’d like to see a recorded demo.

So check out Export-DbaInstance, let me know what you think 👍

If you’d like to see the output of these scripts, you can peruse this repo. I’m not a pro at everything I exported (like replication), so if you’ve got some suggestions, let me know or create a pull request on GitHub with your enhancements.

Thanks for reading,
- Chrissy

One thought on “simplifying disaster recovery with dbatools

  1. Pingback: Simplified Disaster Recovery With dbatools – Curated SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.