Is Your DR Plan Complete?

Kevin Hill (b|t) posted a thought-provoking item on his last week about Disaster Recovery Plans. While I am in the 10% who perform DR tests for basic functionality on a regular basis, there’s a lot more to being prepared for disaster than just making sure you can get the databases back online.

You really need to have a full-company business continuity plan (BCP), which your DR plan is an integral portion of. Here come the Boy Scouts chanting “Be Prepared!”

When disaster strikes:

  • How will you communicate it to your customers, including regular status updates?
  • How will you communicate within the company?
  • Do you have your systems prioritized so that you know what order things have to be brought online? Which systems can lag by a day or two while you get the most critical things online?
  • Do you have contingency plans for all of the disaster conditions that could impact your business or failure modes of your systems?

Let’s say you’re prepared to fail over from your primary datacenter to a DR datacenter when a catastrophe hits the primary. You’ve got that all worked out and you rehearse it monthly or quarterly. You can bring critical databases and websites online within the required time period and the steps are well-documented. That’s a great start!

You probably do this periodic test on the 2nd Tuesday of each quarter, from the comfort of your desk at work, under “normal” conditions.

  • What if your main office is unavailable due to fire, flood, or weather conditions? Can you remotely access any of your datacenters or cloud infrastructure without first connecting to the building that just got wiped off the planet by a tornado? Can you weather a wide-scale blackout?

  • Are you expecting everyone to work from home (or wherever they may be/may find convenient), or do you have a fixed location to use as a command center? Do you have a contingency plan if that “command center” is inaccessible due to unsafe travel conditions or the same problems that plague your main office?

  • Have you tested executing your DR/BCP out of those alternate locations?

  • What if you can access the office (either VPN or physically), but the connection to your offsite datacenter(s) is severed?

  • Maybe you’ve got everyone set up to work “remotely”. Are they able to work at 100% or even 50% capacity if you lose the office, or are they dependent upon a VPN endpoint in the office? How many routes to the datacenter(s) do you have? Are all the necessary tools available on laptops for remote work, or are you reliant upon a jumpbox? Is that jumpbox accessible in a true disaster scenario?

  • A modern laptop that’s only running an RDP client (aka smart terminal) can run quite a while on a full charge and is pretty responsive even when tethered to your phone’s LTE connection or a MiFi device. Are you keeping all those battery fully charged (confession: my laptop is at about 40% as it sits in its bag right now, and I don’t carry a battery pack for my phone all the time) so you can work a few hours while waiting for the lights to come back on at home?

As a DBA, I’m responsible for ensuring that we can get the necessary databases online with reasonably recent data (meeting our SLAa) and accepting connections for users. But that presumes that I can gain access to the DR site. It also presumes that communication channels are documented and followed such that my team isn’t being asked for status updates every 3 minutes, instead of allowing us to work the problem.

There are a lot of moving parts that have to be working together for your database DR plan to execute successfully, and many of them are outside the DBA’s realm or even the IT department. Testing your database recovery plan is terrific – but unless you’ve prepared and tested an end-to-end plan that encompasses everything the company needs to do to continue operating, how can you be sure that you’ll even be in a position to execute the database DR plan?

SQL New Blogger Challenge November 2015 Edition – Week 3 Digest

This week’s #sqlnewblogger posts!

Author Post
@eleightondick [T-SQL Tuesday] Data modeling: The trouble with prefixes | The Data Files
@tomsql Adventures With TomSQL, aka Tom Staab
@EdDebug Automatically name primary key constraints in SSDT |
@rabryst Born SQL on Twitter: “Temporal Tables – Under the Covers with the Transaction Log. 
@YatesSQL Community Involvement–Why Wait? | The SQL Professor
@cjsommer Identity Column Increment Value (EVEN/ODD) |
@DBA_ANDY Nebraska SQL from @DBA_ANDY: CHECKDB – The database could not be exclusively locked to perform the operation
@ALevyInROC Selectively Locking Down Data – Gracefully – The Rest is Just Code
@eleightondick SQLNewBlogger, Week 3 | The Data Files
@tomsql Being Our Collective Best
@SQLMickey T-SQL Tuesday #72 Summary – Data Modeling Gone Wrong | Mickey’s T-SQL Ponderings

SQL New Blogger Challenge November 2015 Edition – Week 2 Digest

This week’s #sqlnewblogger posts!

Author Post
@arrowdrive Anders On SQL: T-SQL Tuesday #72: Data modelling gone extremely wrong
@rabryst Time After Time – An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean
@EdDebug Deploy SSDT INSERTS in Batches |
@ALevyInROC Don’t Trust the Wizard – The Rest is Just Code
@DBA_ANDY Nebraska SQL from @DBA_ANDY: T-SQL Tuesday #72 – Implicit Conversion Problems
@eleightondick SQL New Blogger Challenge: Week 1 recap | The Data Files
@eleightondick SQL New Blogger Challenge: Week 2 ideas | The Data Files
@BeginTry SQL Server 2012 Upgrade: The RPC Server is Unavailable | It's All Just Electrons

SQL New Blogger Challenge, November Edition, Week 1 Digest

Ed Leighton-Dick has renewed his New Blogger Challenge this month. Here are all (I think) the posts for this week after Ed posted his announcement. If I’ve missed any, please let me know and I’ll update.

Author Post
@arrowdrive Anders On SQL: First Timer Summit impressions.
@EdDebug Deploy SSDT INSERTS in Batches |
@EdDebug Looking at SSDT upgrade scripts |
@DBA_ANDY Nebraska SQL from @DBA_ANDY: PASS Summit 2015 Recap
@eleightondick PASS Summit 2015 Highlights | The Data Files
@OliverAsmus PASS Summit 2015: My Experience |
@EdDebug ScriptDom Visualizer |
@eleightondick SQL New Blogger Challenge: Looking back… and a new challenge! | The Data Files
@Clem1029 Tearing down the wall | SQLDEV@Clemsplace
@ALevyInROC Why Ask Why? – The Rest is Just Code
@rabryst The SQL Server Family

SQL New Blogger Challenge Digest – Week 4

This week marks the end of Ed Leighton-Dick’s New Blogger Challenge. It’s terrific seeing everyone sticking with the challenge all month and I’m looking forward to catching up with all the posts. Great job, everyone! Keep going!

Author Post
@MtnDBA #SQLNewBlogger Week 4 – My 1st SQLSaturday session | DBA With Altitude
@Lance_LT “MongoDB is the WORST!” | Lance Tidwell the Silent DBA
@ceedubvee A Insider’s View of the Autism Spectrum: Autism and Information Technology: Big Data for Diagnosis
@Jorriss A Podcast Is Born
@toddkleinhans A Tale of SQL Server Disk Space Trials and Tribulations |
@arrowdrive Anders On SQL: First “real” job with SQL.
@arrowdrive Anders On SQL: Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment
@way0utwest April Blogger Challenge 4–Filtered Index Limitations | Voice of the DBA
@ALevyInROC Are You Backing Everything Up? | The Rest is Just Code
@DesertIsleSQL Azure Data Lake: Why you might want one |
@EdDebug BIML is better even for simple packages |
@tpet1433 Corruption – The Denmark of SQL Instances – Tim Peters
@eleightondick Creating a Self-Contained Multi-Subnet Test Environment, Part II – Adding a Domain Controller | The Data Files
@MattBatalon Creating an Azure SQL Database | Matt Batalon
@pshore73 Database on the Move – Part I | Shore SQL
@pmpjr Do you wanna build a cluster?! | I have no idea what I’m doing
@DwainCSQL Excel in T-SQL Part 1 – HARMEAN, GEOMEAN and FREQUENCY | dwaincsql
@AalamRangi Gotcha – SSIS ImportExport Wizard Can Kill Your Diagrams | SQL Erudition
@toddkleinhans How Do Blind People Use SQL Server? |
@DBAFromTheCold In-Memory OLTP: Part 4 – Native Compilation | The DBA Who Came In From The Cold
@AaronBertrand It’s a Harsh Reality – Listen Up – SQL Sentry Team Blog
@GuruArthur Looking back at April – Arthur Baan
@nocentino Moving SQL Server data between filegroups – Part 2 – The implementation – Centino Systems Blog
@MyHumbleSQLTips My Humble SQL Tips: Tracking Query Plan Changes
@m82labs Reduce SQL Agent Job Overlaps · m82labs
@fade2blackuk Rob Sewell on Twitter: “Instances and Ports with PowerShell”
@DwainCSQL Ruminations on Writing Great T-SQL | dwaincsql
@sqlsanctum Security of PWDCOMPARE and SQL Hashing | SQL Sanctum
@Pittfurg SQL Server Backup and Restores with PowerShell Part 1: Setting up – Port 1433
@cjsommer Using PowerShell to Export SQL Data to CSV. How well does it perform? |
@gorandalf Using SSIS Lookup Transformation in ETL Packages | Gorandalf’s SQL Blog
@nicharsh Words on Words: 5 Books That Will Improve Your Writing

Are You Backing Everything Up?

We hear the common refrain among DBAs all the time. Back up your data! Test your restores! If you can’t restore the backup, it’s worthless. And yes, absolutely, you have to back up your databases – your job, and the company, depend upon it.

But are you backing everything up?

Saturday night was an ordinary night. It was getting late, and I was about to put my computer to sleep so I could do likewise. Suddenly, everything on my screen was replaced with a very nice message telling me that something had gone wrong and my computer needed to be restarted.

Uh oh.

In 7 1/2 years of using OS X, I’ve had something like this happen maybe 4 times.

After waiting whet felt like an eternity, the system finished booting & I got back into my applications. I opened up PowerPoint, as I had it open before the crash so I could work on my SQL Saturday Rochester slide deck whenever inspiration struck. I opened my file, and was greeted by nothingness. I flipped over to Finder and saw zero bytes displayed as the file size.

Uh oh.

“But Andy,” you say, “you use CrashPlan, right? Can’t you just recover the file from there?” Well, you’re half right. I do use CrashPlan. I even have a local, external hard drive (two, actually) that I back up to in addition to CrashPlan’s cloud service. But I couldn’t recover from any of those.

CrashPlan configuration - oops

Because Dropbox is already “in the cloud”, I had opted to not back it up with CrashPlan when I first set it up. After all, it’s already a backup right? It’s not my only copy, it’s offsite, it’s all good.

Not so fast. When my system came back up, Dropbox dutifully synced everything that had changed – including my now-empty file.

Dropbox - 0 bytes

So, now what? Fortunately, Dropbox allows you to revert to older versions, and I was able to select my last good version and restore it.

Screenshot 2015-04-26 21.04.48

Lessons Learned

I broke The Computer Backup Rule of Three and very nearly regretted it. For my presentation:

  • I had copies in two different formats – Dropbox & my local (internal) hard drive
  • I had one copy offsite (Dropbox)
  • I only had two copies, not three (local and Dropbox).

Even scarier, if Dropbox didn’t have a version history or it had taken me more than 30 days to realize that this file had been truncated, I’d have lost it completely.

Everything else on my computer was in compliance with the Rule Of Three; I just got lazy with the data in my Dropbox and Google Drive folders. I’ve since updated my CrashPlan settings to include my local Dropbox and Google Drive folders so that my presentation should now be fully protected:

  • Five copies
    • Local drive
    • Two external drives w/ CrashPlan
    • CrashPlan cloud service
    • Dropbox/Google Drive (different content in each)
  • Three formats
    • Spinning platters in my possession
    • Dropbox/Google Drive
    • Crashplan
  • Two copies offsite
    • CrashPlan cloud
    • Dropbox/Google Drive

And don’t forget to test those backups before you need to use them. Dropbox, Google Drive and other online file storage/sync solutions are very useful, but you cannot rely upon them as backups. I don’t think you’ll ever regret having “extra” backups of your data, as long as that process is automatic.

SQL New Blogger Digest – Week 3

Here are the posts collected from week three of the SQL New Blogger Challenge. It’s been compiled the same way previous weeks’ posts were. Everyone’s doing a great job keeping up with the challenge!

Author Post
@MtnDBA #SQLNewBlogger Week 3 – PowerShell Aliases | DBA With Altitude
@ceedubvee A Insider's View of the Autism Spectrum: Autism and Information Technology: New Efforts for Kids to Code
@arrowdrive Anders On SQL: Stupid Stuff I have done. 2/?. Sometimes even a dev server is not a good dev environment
@way0utwest April Blogger Challenge 3 – Filtered Indexes | Voice of the DBA
@eleightondick Creating a Self-Contained Multi-Subnet Test Environment, Part I – Networking | The Data Files
@ceedubvee Empower Individuals With Autism Through Coding | Indiegogo
@MattBatalon EXCEPT and INTERSECT… | Matt Batalon
@cjsommer Follow the yellow brick what? My road to public speaking. |
@DBAFromTheCold In-Memory OLTP: Part 3 – Checkpoints | The DBA Who Came In From The Cold
@MattBatalon Introduction to Windowing Functions | Matt Batalon
@nocentino Moving SQL Server data between filegroups – Part 1 – Database Structures – Centino Systems Blog
@Lance_LT My first year as a speaker | Lance Tidwell the Silent DBA
@MyHumbleSQLTips My Humble SQL Tips: Tracking Page Splits
@ALevyInROC Padding Fields for Fixed-Position Data Formats | The Rest is Just Code
@tpet1433 Sir-Auto-Completes-A-Lot a.k.a. how to break IntelliSense, SQL Prompt and SQL Complete – Tim Peters
@pmpjr stats, yeah stats. | I have no idea what I'm doing
@DwainCSQL Stupid T-SQL Tricks – Part 3: A Zodiacal SQL | dwaincsql
@cathrinew Table Partitioning in SQL Server – Partition Switching – Cathrine Wilhelmsen
@gorandalf The MERGE Statement – One Statement for INSERT, UPDATE and DELETE | Gorandalf's SQL Blog
@SQLJudo The Road to SQL Server 2014 MCSE | Russ Thomas – SQL Judo
@GGreggB T-SQL Tuesday #65: FMT_ONLY Replacements | Ken Wilson
@AalamRangi What is the RetainSameConnection Property of OLEDB Connection in SSIS? | SQL Erudition
@EdDebug What Permissions do I need to generate a deploy script with SSDT? |
@_KenWilson Windowing using OFFSET-FETCH | Ken Wilson
@DesertIsleSQL What Does Analytics Mean?
@DesertIsleSQL Azure ML, SSIS and the Modern Data Warehouse
@DesertIsleSQL Musing about Microsoft’s Acquisition of DataZen and Power BI
@GuruArthur Check for database files not in default location