Don’t Forget About DR for Your DR

Here’s a scenario:

Let’s say you’re a small- or medium-sized company with either an on-premises data center in your office/building or in a “regular” co-lo nearby in the same metro area. You’ve got a mission-critical online presence, so in order to handle either a large-scale disaster for your geographic area or one just in your server room, you’ve written, implemented, and tested a disaster-recovery plan. Another co-lo a couple of states over is set up to be able to step in if needed, and this process can even be completed by non-technical resources in a couple of hours.Need a Plan C

This is a fairly-sound plan. However, what’s Step 2 after Something Bad™ happens to the primary data center and everything fails over to the DR site? What if Something Bad™ is long-term? You’re back to square one, with a single data center. Or where do you put the quorum file share for your AG?

Or, another situation: What if something happens to your DR site? Then what?

Almost Been There, Done That

One of our clients–who has a really good DR plan similar to the one described above–had a brush with this scenario earlier in the year. Their DR data center is in the Houston area, and in the aftermath of Hurricane Harvey, there were some concerns about the status of the DC. The DC itself was fine, but key support personnel would not have been able to get to the site for a number of days if there were such a need.

This situation did a good job of spurning conversations centered around what to do in this situation and what Plan C might look like.

Now What?

The point of this post is mostly to get you thinking about this scenario. Getting DR in place can be enough of a battle itself (I know), but ensuring that what happens next after a potential disaster is considered and planned for is another important step.

What this plan may look like is likely dependent upon what the “first stage” DR plan looks like. Not everyone can afford an additional site, especially if it’s a smaller company. And, let’s be honest: we could sit here all day and what-if burning data centers, but at some point, the return on this investment will become very questionable.

Although this looks/smells like a shameless plug for cloud/Azure, the public cloud is an excellent option to consider here. Even if your company is 100% on-premises with a classic hardware/virtualization platform, keeping a copy of critical systems’ backups up-to-date and available in the cloud is relatively inexpensive.  This “cold DR” process is a very easy-to-implement step to safeguard against a multi-phase or long-term disaster. In the event that these backups are needed, there’s the option of spinning up a group of VMs in the cloud to restore to. At the very least, this cold backup solution will be more-accessible than your current offsite backups if new on-prem servers are stood up somewhere to get the lights back on.

How can I get old SQL Server CUs?

When one maintains SQL Servers/Instances for a living, one of the things we do on a semi-regular basis (or at least should) is install major updates to said instances. But, because usually we aren’t in a position to mash the latest and greatest one out to everything we support, where to find the specific update we’re looking for isn’t immediately obvious. This is because on all of the existing–say–Cumulative Updates pages, all of the download links to a generic “latest CU” page, where that’s all you can get.

But what if I need an older one?

Behold! Via the Microsoft Update Catalog, all updates that have been released for SQL Server, [almost] ever: www.catalog.update.microsoft.com/Search.aspx?q=SQL%20Server

From this site, you’ll be able to find what you’re looking for and pull it down. Easy peasy. Use the search box at the top to get a little more focused, keeping in mind that “Service Pack” and “Cumulative Update” are spelled out. If you’re looking for a specific update, it’s probably best to use the KB number to search for it (“KB4024305” for SQL 2016 SP1 CU4, for example).

The main page of the site is pretty awesome in a 1997 sort of way (it even looks like it’s from 1997). When you go to www.catalog.update.microsoft.com, basically all you get is a little welcome message, some feedback and a FAQ link, and a search box. That’s it. It’s great.

This isn’t just for SQL Server, either. Theoretically all updates for all mostly-recent MS products are available–for example, Windows XP SP3 is available for download, were you to need that. If you do, we need to have a talk. It also looks like you can’t get SQL 2000 SPs anymore, but all of SQL Server 2005’s are.

Anyway, this is a good Site/URL to keep handy, especially for times like when you’re just about to download SQL 2016 SP1 CU4, and “ah crap, CU5 just got released an hour ago and all of the download links changed.”

Hey, you! Don’t Forget to Enable DB Mail in Agent

Raise your hand if you’ve been there: You set up a new SQL Server instance, configure Database Mail (test it), and then set up a nice Agent job to back up your databases. You configure it to send mail on job completion (so you can keep an eye on it not matter what), but it’s not sending mail. You test DB mail again, and it’s working. What gives?

This is kind of a gimme, but a few weeks ago I configured a new maintenance job (NOT a Maintenance Plan 😉 ) on a new-ish non-production server that didn’t have any other jobs on it yet. When it wasn’t sending mail, I stood around for a lot longer than I’d like to admit before I figured out what was going on.

The kicker is that you have to enable the use of database mail within the SQL Server Agent–this isn’t on by default.

As with most things in SQL Server, there’s a couple ways to turn this on. First is the GUI. The Alert System page of the SQL Agent’s properties dialog is shown here, and you can see right on the top where the main “Enable” checkbox is, along with dropdowns for the DB Mail settings you want to use. Flip that on, pick your desired mail settings (probably only have one setup), restart the Agent service, and your agent jobs will start sending mail as-expected.

Agent Properties dialog showing Mail Settings

There’s also the T-SQL route, which is useful for adding this configuration to a general “initial” script (such as ours: https://github.com/DC-AC/SQL2016_Scripted_Install) so you don’t have to worry about this on new instances that you install/setup. It’s a quick SP call to enable mail:

EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N’Main Profile’

Assign the mail profile you want to use, and go. The UI by default and greyed out (at least on a few 2016 instances that I’ve checked recently) checks the “Save copies of the sent messages in the Sent Items Folder” option. This option can be driven with the email_save_in_sent_folder parameter on the proc. Set it to 1 to turn on that option. True story: I have no idea where that mail gets saved on a SQL Server; I assume it goes to the “Sent Items” folder in the mailbox the profile is configured to use, but I’ve never actually configured this with a mailbox that I have access to to see.

This T-SQL step assumes SQL Server on Windows. If you’re doing this on Linux…well, it’s different. I’m not going to reproduce that work here, because it may change since SQL 2017 is still in RC at this point. So, if you’re doing this on Linux, check out the official docs for that process.

Moral of the story here: Don’t be a dumbass like me; turn on DB Mail in the Agent!