Search results matching tags 'Disaster Recovery' and 'DBA'

Backup those keys, citizen

BuckWoody — Tue, 20 Apr 2010 12:14:50 GMT

Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.

The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the BACKUP SERVICE MASTER KEY command, which you can read more about here.

The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the BACKUP MASTER KEY command, which you can read more about here.

Finally, you can use the keys to create certificates and other keys – those should also be backed up. Read more about those here.

Anyway, the important part here is the backup. Make sure you keep those keys safe!

Have you backed up your keys lately?

BuckWoody — Mon, 01 Mar 2010 14:06:04 GMT

Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance.

So you should back all of those keys up periodically, and then store that backup AWAY from the server itself.

There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.

More here: http://technet.microsoft.com/en-us/library/bb964742.aspx

No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.

Plan and Prepare or Just Do It? How about Both!

BuckWoody — Thu, 07 Jan 2010 15:56:00 GMT

I'm kind of a type "A" person. OK, I'm a VERY type "A" person. I even cook by setting things up ahead of time. I'm definitely more in the "Plan and Prepare" camp than the "Just Do It" camp.

But I do realize that there are times when you just can't stop and prepare. Sure, it would be great to know that server is going to melt down just now, but it happened and you have to deal with it. Now is not the time to open the plastic on that "Troubleshooting SQL Server" DVD course you bought! You just have to dive in and get the thing fixed.

Hopefully you're not operating like that all the time. If you are, you probably need to get some help with your systems, at least temporarily, until you can get them stabilized.

So which is it? Should you be like me, a type "A" who has to have everything planned out, or a reactionary agent, fixing things as they happen? How about - Both!

I think you should aim to plan and prepare as much as you can. Your life will be more stress-free, and you'll be happier in your job. But you can't lose your head when things go wrong and demand time to plan and prepare. You just have to jump in there and fix the problem.

But I think there's a happy medium in there somewhere. I ten to plan and prepare for the times I have to "just do it". I have my scripts ready for things like backups, DBCC repairs, restores, web site links for "how to" articles and so on. I have those right by my desk so that I don't have to panic when panic hits. So in effect, I'm doing both - I'm planning to just do it.

The Dark Sides of Consolidation

BuckWoody — Thu, 10 Dec 2009 13:56:06 GMT

Consolidation, as it applies to databases, is simply putting more databases or SQL Server Instances on less hardware. This is a good thing, normally, because it allows you to save on hardware costs and use what you have at it’s highest capacity. It also saves on energy costs, floor and rack space, and in some cases even licensing and software.

But there are issues that you need to consider. When more databases or Instances there are on a single server, the higher your risk when that server is unavailable. For instance, let’s say you have 10 servers with various databases that hold your organization’s application data. If one of those servers goes down today due to a hardware failure, then one application is out of commission. Now take those 10 databases and host them on a single Instance, or set up 10 Virtual Servers on a single hardware box. If that system goes down, now 10 applications are out of commission.

And it isn’t just that. It’s the recovery time for all of those systems. Even in a single system failure, it isn’t a matter of simply restoring the last backup. Whenever an application goes down, you have to check the consistency of the system by verifying what data made it in before the failure and what needs to be re-keyed, or if the data made it in whole or in part. Now you have that on 10 systems, not 1, and that takes a lot more time.

How big a risk is this? Well, the other day I lost power to a switch (how in the world does THAT happen) and lost 5 Virtual Servers in the middle of a test run. I had a friend that same week that lost a SAN connection (driver issue) and lost a consolidated Instance of SQL Server with 25 applications on it.

So does that mean you shouldn’t consolidate? No, it just means that you need to consider that risk. Add what you need to make that system redundant as possible, and include the increased process in your Disaster Recovery planning. And you have to communicate that risk and increased time back to the business.