Voice of the DBA

Author: way0utwest

  • The Year in Review – 2011

    Big Data and The Cloud were a large part of 2011

    It’s the end of the year, and I’m taking a look back at 2011 from the data perspective. As we close out the year, it’s apparent that “big data” has been catching on with many companies. It seems that every week we have some headline that talks about “big data” in some way. Even Microsoft has joined in with  their work with Hadoop and SQL Server.

    The other big push in 2011 seems to have been “the cloud”. It’s everywhere, from consumer Windows 7 commercials to the updates to the SQL Server tools to support the SQL Azure platform. I haven’t found many people that are excited about moving their databases to the cloud, but more and more people are testing the platform, and I suspect we’ll start to see some apps that don’t require high security or extremely high performance appearing in the cloud in 2012. I have embraced the cloud in many ways over the last couple years (Evernote, Dropbox, Live Mesh) and I find it to be an advantage in managing a busy life.

    We had our share of high profile data breaches as well, with some very high profile ones, including RSA, the security vendor. We could add in the Carrier IQ fiasco, which showed a little abuse of consumer trust with the debugging code they developed.

    A few other notable items in the data world for me. The first was the death of Steve Jobs. While most of us working with SQL Server don’t use Apple products, the changes that Steve Jobs brought to the world, most recently the iPhone and iPad, have altered the way we consume data in many facets of our lives. The second item, was the split of Netflix into two businesses, and the failed idea that splitting our online and physical DVD data into separate sites wouldn’t be an issue. The reversal of that decision showed me just how valuable centralization is for many consumers.

    There were many stories that touched data in our lives in 2011, and it seemed to be a busier year than ever before for me. However one large milestone for me was the 100th SQL Saturday event in Brazil. It’s hard to believe there have been 100 events, with 45 events in 2011. Seeing the SQL Server community come together to embrace, help, and inspire each other, both online and off, has been amazing. If you haven’t tried to interact with others through LinkedIn, Twitter, Google Plus, or some other social media, I urge you to try in 2012.

    I hope that you have had a great 2011, and I look forward to an even better 2012.

    Steve Jones

    The Voice of the DBA Podcasts

  • Data and Privacy

    Privacy issues definitely apply for logging and debugging code.

    Recently there’s been quite an outcry over Carrier IQ’s software, which is on many smartphones and allows keystroke logging and capture of almost anything you do on your phone and transmit. Google’s Eric Schmidt condemned the use of the software, and there are numerous lawsuits being filed.

    Whether you think this software is legitimate or Carrier IQ and the carriers had the right to deploy it, you should be aware that from a public perspective this is a failure of software. It seems as though a back door of sorts, a spy program has been developed and approved by companies that sell cellular phone services, and it is a poor reflection of their business.

    I see legitimate uses for this software. It could be incredibly useful in debugging software and finding issues. If it’s enabled and captured by the end user. Having the vendor capable of accessing this information, however, is a very bad decision. It’s the equivalent of Microsoft being able to turn on trace or extended events on your instance and have all that data sent back to them in near real time.

    Many of us develop software, and we need to implement logging, and diagnostic functions that enable us to track down issues. However we ought to respect the privacy and potential perception of our customers and limit the ways in which we implement these features. We should design the functions to allow for a reasonable expectation of privacy when appropriate, and we should fully disclose how and when we are collecting data in the application.

    The other great lesson to take from this is the way we handle potential issues if they arise. Carrier IQ could have done a much better job of working with those that had concerns if they had respected and discussed the concerns, rather than dismissing the issues and attacking the people that raised them. That’s a communication lesson we all could remember in any conflict at work.

    Steve Jones

    The Voice of the DBA Podcasts

  • Merry Christmas 2011

    Merry Christmas and Happy Holidays.

    The Voice of the DBA Podcasts

    Enjoy the blooper reel

  • Backing up the Service Master Key

    Making a backup of your Service Master Key (SMK) should be one of the first things that you do if you are enabling encryption. This key encrypts and secures your other keys, and it will be needed when you have to rebuild the server.

    Backing up the key is simple. You use the BACKUP SERVICE MASTER KEY command and it only has two arguments

    • File – The path and name of the file for the backup.
    • Password – provides security for the backup file. This is needed when restoring the file.

    That’s it. Performing a backup is as simple as running a command like this: 

    -- Backup the SMK
BACKUP SERVICE MASTER KEY
 TO FILE = 'c:\sqlbackup\MainServiceMaster.key'
 ENCRYPTION BY PASSWORD = 'S3cureP@ssword!sneeded'

    Securing this file, however, is a little harder, and managing it is really hard. Ideally you do not want this key stored with the backup files for the server, because you don’t want anyone to have this and the data. However if you must do that, and that might be the practical thing to do so that it’s available when you use this tape/disk to recover the server, I recommend you do not keep the password with this file. Store it in an admin system somewhere else, preferably a protected system with something like KeePass or Password Safe.

    A couple things to note. Just like with a backup, the service account needs write permissions on the path (local or UNC) to create the file. This command requires the CONTROL SERVER permission, so not anyone can use it.