Voice of the DBA

Author: way0utwest

  • Time Off

    snowboarding
    I am looking forward to some time off this winter on the slopes.

    As a US citizen, I’m lucky to work for a UK company. I get a generous allotment of vacation, and desperately try to use it all each year. I haven’t been successful yet, but I’m getting closer. If we could only get a little more snow at the beginning and end of the season…..

    Last year a friend told me that they lost 6 days of vacation. Over a few years this person had accumulated extra vacation by basically working too hard and when the year ended, some of their vacation was lost because of carryover limits. This person vowed to change this year and use all their vacation, but with a busy job at a small company I’m not sure that will come true. Unfortunately, I’ve all too often seen this same behavior from many people in IT.

    This piece shows that all too often people are not taking their vacation, or they’re not taking it as a complete break from work. I’ve been in both situations, and over time I’ve learned that I’m not being more productive in either case. I’m unbalanced, and over time I become less productive. That’s especially true if I’m doing creative work, like writing, or developing code.

    There’s no shortage of poor managers out there, and there are plenty of them that will work you as hard as you allow. I don’t have any great solutions for dealing with them other than scheduling vacation every year, insisting you get to take it, limiting contact with work, and if all else fails, looking for another job.

    Life is short, too short to spend more of it as work than you have to. Take advantage of your vacation to relax and recharge, even if it’s a stay-cation at home. Getting away from work is not only important, it’s something you owe to yourself and your family.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Influence of the Cloud

    influence
    What influence will the cloud have on your career?

    To those of us working in technology, it seems that the only constant is change. Windows 8 and Windows Server 2012 were just released as their RTM, and we had new versions of SQL Server andSharepoint this year. Those technologies bring with them the need requirement that many of us upgrade our skills to work with new features over time. It’s not that most of us need to learn to work with those versions right now, but the pace of upgrades continues to roll on, whether you are looking to move to the latest version or one that’s slightly older.

    As much as new features and capabilities are a part of version, it seems that the hype and promotion of different technologies can play as much a part in the decision to upgrade as the technical merits of any change. It seems no shortage of management is influenced by the reporting in the media and conversations with other executives about some new technology.

    The “cloud” is a perfect example of this. It’s in the news constantly, being promoted by many companies, despite the fact that the label is being applied to a mix of IaaS, PaaS, SaaS and everything in between. The Friday, I wanted to take a quick poll of how this amorphous beast might be influencing your career.

    Do you think you’ll work more with the cloud over the next year?

    Define the cloud however you wish, but let us know. Do you think you’ll see more cloud services used, deployments to the cloud, perhaps even some databases in the cloud? Let us know if the cloud is influencing your job or if it’s just another fad you are hoping will fade away.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • 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.

  • 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:

    backupcert1

    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.

    backupcert2

    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:

    backupcert3

    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.