Voice of the DBA

Category: Blog

Home Backup for the DBA

I’ve been giving a Prepare for When Disaster Strikes talk at SQL in the City this year, and it’s designed to get you to think about problems before they happen and take steps to mitigate issues. It’s important at work, but it’s also important at home.

If you read the story of the Amazon/Apple hack, you might worry about the security of your information, and you should. However I was more concerned over the data loss, specifically pictures, when I read that account. I take a lot of pictures, all of them digital these days, along with various writings and videos, and I worry about preserving those for my own memories and for my kids. I don’t trust Facebook/Google/etc, to store them, so I needed my own solution.

I installed Windows Home Server (WHS) a few years back on an old Dell Dimension E520. It was a good workstation, and plenty powerful to run the previous version of WHS (prior to WHS 2011) on its’ AMD CPU. It worked well, but across two years I had 3 boot drive failures. Each time I needed to not only replace the drive, but reinstall WHS and then copy off and back my files from the data drives. A PIA.

I’d looked at Drobo, and other NAS type solutions, but none were very automated, rather expensive. I had a desktop, a laptop, my wife’s laptop, the kid’s iMac, and sometimes another machine to back up. After my 3rd boot drive failure, and the server sitting idle for a couple months, I decided to virtualize my WHS.

I had planned on using Win7 for a host, but the older desktop didn’t seem to want to install it with my new RAID card in it. Rather than futz with it, since it wasn’t critical and isn’t connected to the Internet on my home network, I installed WinXP and then Virtual Box. Inside of Virtual Box, I installed the older WHS software, putting my data on separate virtual drives that were protected by RAID. I used 2 separate arrays, which should give me some protection if any of them fail. The host WHS drive is also on a RAID array, which should give me some protection from drive failures.

And if my boot drive fails, I replace it, install WXP, Virtual Box, and then run my VM without messing with data.

I like WHS as a central place for us all to share pictures, video and music, and for backups. I’ve recovered a few files from the system that I had accidently deleted, and I know all our machines are protected right now.

It’s not perfect, and I really need an offsite solution for a second backup since I’m a DBA. I get a little paranoid about restoring things, and I know one copy isn’t good enough.

August 17, 2012
Where’s My Certificate Backup?
If you’re like me, you take advantage of the default backup paths in SQL Server. It makes my code cleaner, and if I need to move the instance somewhere else, all my code works. No pathing issues.

A certificate backup might look like this for me:
```
USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
GO
```
If I run this, and immediately go to my backup folder, sorting by the last modified date for files, I see this:

No certificate backup file. What happened?

The answer is actually documented, and you should be aware of this. In the BACKUP CERTIFICATE page, it says this: “The default is the path of the SQL Server DATA folder. “

That’s interesting, and it makes sense to me. This folder is more likely to be secured than the backup folder, where developers and who knows who else may have access to the folder. By limiting it in the data folder, you provide a little obfuscation, perhaps more protection, and you force the administrator, the DBA, to get the files.

However the files are also ACL protected. If I go to my data folder, I see the files.

If I select the certificate and CTRL+C (copy) it, and then go to the backup folder and try a CTRL+V (paste), I get this:

The service account has permissions to this file, not administrators by default. This action invokes the UAC command to require me to make a conscious decision to make this copy.

Of course, I can just provide a path to make sure I can find the file.
```
BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go
```
Whatever you do, make sure you backup your certificate files and keep them safe. If they go, you do lose data.
August 16, 2012
The Home Server is Back

After reading about the Amazon/Apple attack this morning, I grabbed a second cup of coffee and went in search of a monitor cord. We’d given a monitor to a friend recently, which was the one rarely used for the Home Server. It wasn’t a big deal since we always log into the Home Server remotely, but we had a power outage and the server went down. Since it’s virtualized, I needed a console to get it started again.

After getting a monitor connected and my virtual server set back up, I immediately kicked off a backup. It’s been down a few weeks, and I’d hate to lose a bunch of work if something happened to the other machines on my network.

I also enabled remote desktop access, so that I can more easily restart things in the future.

Back up your systems. You never know when disaster will strike, which could something as cruel as a hacker or virus wiping your system. As data professionals, we should be more cognizant of the impact of losing information.

August 15, 2012
T-SQL Tuesday #33 – Trick Shot

It’s T-SQL Tuesday time again, and this is my post for #33. The host is Mike Fal and his topic is Trick Shots, which is an interesting one. I’m not a tricky guy, and I tend to lean towards common, practical approaches to problems. I’m not sure this post will be great, but I like participating, and so I will.

The T-SQL Tuesday blog party takes place every month, and if you’d like to host, contact the originator, Adam Machanic(b|t) .

Tricks Shots

Once upon a time, I was a DBA. I worked with a number of developers who were, how can I say this politely, not terribly careful about which objects they changed or added. It was understandable since they had jobs and work to get done. However I was responsible for deploying their changes to our QA, and ultimately production, servers.

Not knowing what to deploy is a pain. It leads to mistakes, broken features, and more importantly, long hours from me trying to determine what changed objects went with which features. Since I also want to deploy the same code to production as QA, I want to smooth out this deployment process as much as possible.

Back in 2000/2001, we didn’t have SQL Source Control tracking changes by individuals. We had to manually check in and out of our VCS for all database changes, which was not a habit most developers had built. As a result, we would constantly have new objects appear, and old ones changed as developers needed to meet new requirements. I tried to handle all the database development work, but there were times I couldn’t keep up.

As you might expect, when deployment time came, we had a lot of objects in the development database that weren’t in the production database. SQL Compare made it easy to find out which objects were different, but the problem we faced was that not all changes would be deployed at once. We needed specific code changes linked to specific objects, which wasn’t a simple task with 10-12 developers.

A few months of mad scrambles to track down objects and try to meet our weekly QA and deployment goals had me working on a better solution. There had to be a way in the SQL Server metadata to track changes to objects. I dug around the SQL Server 2000 sysobjects views and found a creation date, but not an alteration date. However I did find a version number that was undocumented, but incremented on ever ALTER of an object.

Using this information, I build a process that would capture the state of all objects in a table, and then compare this to the current state of sysobjects, returning differences to me. I built this as an hourly report, and had it send changes to me. This didn’t prevent changes, but it allowed me to quickly track down what had changed, and send a note to the developer to link this to a particular item in our project plan. A few minutes an hour (with no changes many hours), let me break out the database changes into a deployment project for the next week.

The Trick

The trick in this case was finding information I needed from SQL Server that wasn’t documented. It doesn’t apply any more and the metadata in SQL Server 2005 and later has grown so much that you can more easily find changes.

What I Learned

I learned a few things here. First, I could build my own systems on the SQL Server platform to help me out. I didn’t need to depend on what Microsoft provided, if I needed something different. This led me to view the management and administration of the instance as just another application. One I built on top of the platform the same as my developers.

This also taught me that I needed to be like the reed, flexing and bending to survive in situations. My developers were willing to work with me, but they were human, and they had other priorities. I needed to work with them and get along, adapting some of my ideas and needs to work with them. I did get them to work on manual check ins and outs, but they slipped up, and my system helped both catch those mistakes, and remind them in a gentle way. My emails asking to link an object change to the project never complained they missed something, but they realized the reason I sent it and it helped reinforce the habit of checking objects out of VCS before editing them.

August 14, 2012