Voice of the DBA

Author: way0utwest

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

  • Marketing Data is Exploding

    GM Customer Service Facebook Cnoversation
    It would be nice to have this be the norm rather than the exception with customer service.

    The last few years have seen the rise, and explosion, of social media. While fundamentally social media isn’t introducing much that’s different from actions in the real world, it is making the reach and speed at which we can communicate grow exponentially. Facebook will surpass a billion users soon and Twitter has exploded in the world. Even in the tiny SQL world, Twitter use has grown exponentially in the few years since I’ve used it. It you’re unsure of what’s happening there with regards to SQL Server, search the #sqlhelp hash tag and see what you find out.

    All kinds of companies are starting to try and integrate this data into their systems. If you haven’t yet, I suspect you might have to some time in the next few years. There’s a short piece in how this data is in use at GM, and it’s interesting to read. I don’t know how seriously other companies take this, but I do see so many people “following the herd” and as more managers read about other managers implementing projects like this one, we’ll see more adoption.

    What’s interesting here is that it gives us data professionals a number of new opportunities. From the sheer OLTP-type work of collecting and managing this data in real-time, or near real-time, to the need to add BI analysis to trends of data. I can see a lot of new projects, and new employment in this area. It wouldn’t hurt to beef up your skills in this area, and maybe get a better understanding of how social media can work, as well as how you can manage the data.

    Steve Jones

    The Voice of the DBA Podcasts

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

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

  • Learning C

    Head First C
    I think C is important to teach the basics of programming.

    Someone else gets it. Besides me, I mean. We should be teaching people the C programming language. An interview with David Griffiths, author of Head First C, says that learning C is a good idea. I concur, and I stand by my statement.

    It’s not that I think everyone should be a C expert, but I’d like to see more programmers spend 5 months, a semester, early in their programming careers. If you’ve written your own iOS app, this would be a piece of cake. If you’re a Python guru, it would broaden your horizons. Getting closer to hardware helps you write code in a more thoughtful way.

    I’d like to see a C programming course as a semester requirement for quite a few fields that might need to develop programs. Not only computer science majors, but also scientists that might need to deal with building their own algorithms. In the same vein, perhaps it’s worth a SQL class for everyone to better understand how to work with sets of data. A query class could also include some of the other query languages, perhaps MDX and XQuery would make sense as well.

    I know this sounds like busy work, but as we become more and more dependent on data and scripting for all sorts of jobs I think it makes sense to start setting a baseline of expected skill for knowledge workers. Those people that knew programming could potentially pass a C test,  and they would be exempt. As we evolve into the future, especially as we may move to more vocational type training, I think it would be good to still require some basics of people as they begin their journey to working with code and computers.

    Steve Jones

    The Voice of the DBA Podcasts

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