Voice of the DBA

Tag: disaster recovery

DR Priority

Those of us that act as DBAs or sysadmins for database systems know that DR is a point of emphasis for us. We usually spend plenty of time ensuring backups are working and practicing restores. The automated scripts and processes that people use are some of the most popular and discussed topics on SQLServerCentral.

However, we can’t ensure every system is protected at the same level. It’s not cost effective to cluster or build AGs with hot standbys, or even warm standbys, for many databases. Often our organization will ensure some are ready to go and others will have to be dealt with if there are issues.

William Durkin noticed recently that O’Reilly hadn’t prepared well enough for their learning site. They were affected by the fires and power outages in California and since they host some of their systems in an on-premises data center, there were issues. Certainly we might think they hadn’t prepared well for DR, and perhaps this is a fair view of their service, but perhaps they made the decision not to built out an expensive DR environment for a service that can tolerate some downtime.

This week, I wonder how some of you look at the systems you support. Perhaps you are the person that has to make decisions, or perhaps your organization doesn’t fund DR well. I’m wondering, how do you decide which systems don’t get enough DR support?

Certainly there are inexpensive, perhaps crafty ways that some people might plan for DR. I know I’ve cobbled together systems from spare parts to use for testing restores, with the idea that the hardware might need to be an emergency DR server for a single system or two in the event of an incident. If you’ve got ideas on how to be prepared even without organizational support, let us know today.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

November 22, 2019
Restoring an Expired Certificate–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One common task that you might need to handle if you work with encryption is dealing with a certificate. Whether this is for TDE, SSL, or something else, you will want to ensure that you know how to restore a certificate.

I’ve written about this before (restoring a certificate), but what about restoring an expired certificate? If you forget to replace one in TDE, does it still work?

Tl;dr yes.

Validating a Restore

I backed up an certificate from one instance that had expired. I used this in a customer demo a few years ago, and it was on my system. Here’s what I saw.

I ran the backup script shown to get the expired backup of the cert.

Next, I connected to a new instance and ran this.
```
CREATE CERTIFICATE FinanceCert

FROM FILE = N'C:\SQL\FinanceCert.cer'

WITH PRIVATE KEY

(
     FILE = N'c:\SQL\FinanceCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'

);
```
This completed, but I get the warning that this cert is expired. It’s a warning. SQL Server will still use the certificate to decrypt anything necessary, like a DEK for TDE.

This is a good reason to keep an eye on certificates, as someone might be worried about expired certs. They work, but some people (auditors) do not like to see these in use.

To change the certificate used, see Key Rotation in TDE.

SQLNewBlogger

Anything DR related is a good skill to showcase. Blog about your practicing different skills. The writing will cement the skills further in your mind and employers will appreciate you showing the way you handle things.
September 2, 2019
Dealing with Failure

This past week was a dichotomy for Coloradans. On Tuesday, it was 60F (15C) for most of the day, with my daughter and I going to volleyball practice in t shirts and shorts. Our phones chimed during practice, noting that schools had been cancelled for Wednesday, in anticipation of a winter storm. Plenty of people laughed about that at the time, and no shortage of parents were annoyed by the premature decision, especially with little wind and rain falling at 7am Wednesday.

That changed with high winds and some snow all over the area. Cars were stuck on roads, first responders overwhelmed, the airport closed, and people stuck in places between home and work. We hunkered down here at the ranch, and while horse care was hard, we survived. Power flickered for me, but I was able to work Wed and Thur without worry. I also have a generator, but we never lots power longer than the 30s it takes to kick in.

Glenn Berry wasn’t quite as lucky, though he came through the storm fine. He did lose power, and wrote about his experiences. Glenn is about 12 miles S of me, and in a rural area. I thought his thoughts on the experience were interesting, and not a lot different from the ones I had recently with another power outage. I don’t mind storing gasoline, and we have multiple generators here because of the horses, but I appreciate Glenn’s thoughts on finding a different way of storing power.

We both realize that disaster can strike and it’s important to think through the issues ahead of time. While I depend on my phone in the event of losing our Internet connection, I could easily lose that as well. The local towers probably run off batteries and in an extended outage, they’d lose power before I would. I have a tractor and can plow out our driveway, but in this case, the county roads were closed Wednesday and Thursday due to the poor conditions and number of cars stranded. Apparently Wed night visibility was inches and cars were being abandoned in the middle of roads. I’d prepared for a few days outage, but if this were still ongoing today, I’d start feeling the pinch from a lack of supplies.

It’s not often we encounter a major disaster at work, but it’s entirely possible that the secondary support plans we have in place won’t work. You might have a supplier for more diesel fuel, but if everyone else needs more, will you still get some? What about issues with a complex process at work? If it fails and you end up rebuilding some ETL flow, do you have the staff or are you expecting a “Brent” to help explain things and do some of the work? I think plenty of us have that dependency, one which we don’t think about until that person is on holiday and we need their expertise.

Disasters come in all sizes, but often they seem to come in the size that is at the limit (or just beyond) our preparations. I suggest that during some down times, take a minute and think about your preparations. Think what could go wrong. Do a little brainstorming and gaming of situations. If your plan has an issue, do you have another idea of what to do? It might be good to at least have thought about the potential issues.

Steve Jones

March 16, 2019
Someone Lost My Data

It was bound to happen. I’m sure it’s happened before, but this event was interesting to me as it wasn’t a customer issue, but a cloud vendor problem. There was an outage in Azure on Jan 29, which happens, but in this case data was lost. There was problem internal code in the Azure cloud that dropped some customer databases using the Azure Key Vault with TDE encryption. That was slightly disconcerting for me as I was setting up and testing Azure Key Vault this week.

There are frequent snapshots and Microsoft was able to restore the databases from one that was about five minutes old. Microsoft acknowledges that five minutes of data loss might be an issue and is asking customers that lost business or were affected by the drops to raise a support ticket. I have found Azure support to be pretty good about crediting my account when issues occur, and I hope they do the same here, though I wonder if they’ll compensate anything beyond the charges normally assessed to customers. They are offering credit for the restored and original databases for a few months as well.

Plenty of people are upset, and with good reason. There should not be Azure management code that drops databases. Or should there be? Would this be any different on premises?

I’ve had cleanup code that removed resources after some time. I don’t work at the scale of Azure, so I usually have things removed after a month or quarter. With the scale of Azure and potential costs, they might remove resources quicker, but I certainly have seen similar home grown, if-this-then-that code that does x when y occurs. In this case, I remove encryption keys, which might be not actually be removed for month or more. At that point, there is code that trigger a drop of databases. I’ve certainly seen users in various organizations drop, or restore over, the wrong database. In some cases they don’t realize it in five minutes, and often don’t have a way to restore from a five minute ago backup. Actually, relatively few people I know have RPOs under five minutes.

This is bad, but it isn’t necessarily out of the ordinary for complex IT environments. If this happened in an organization, the IT staff would be worried and hoping for forgiveness. Some people would want others fired, but most of the time management would understand these things happen. Perhaps not if this isn’t the first time, but usually we accept some people make mistakes. Most of us don’t have complete control of all aspects of our environment. We depend on network staff, storage people, employees that manage hardware, and more. It’s possible that any one of these people could destroy data inadvertently.

That happened here, though I don’t want to make excuses for Microsoft. They’re supposed to hire the best people and build processes that are better than what I’d expect inside an organization. Events will cascade into different areas, and there should be circuit breakers that prevent anything that could cause data loss in those events. Protect other people’s data with more care than you think you need. It’s your responsibility to do so.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

February 14, 2019