Voice of the DBA

Category: Blog

  • The T-SQL Tuesday #027 Roundup

    UPDATE: I found a few more posts, and have added them to the round up. Trackbacks didn’t work for some reason.

    I didn’t have quite the level of participation for T-SQL Tuesday #027 that I would have liked. I understand it was a holiday, and many people might have been otherwise occupied (a good thing), and there’s less work for my round up (a good thing), but less information out there (a bad thing). Perhaps it wasn’t a great topic, but in any case, here’s the roundup.

    In the early hours of the day, Rob Farley in Australia gave is a short preview of Big Data coming in 24 hours of PASS.

    Right behind Rob, Pinal Dave in India gave a simple explanation of Big Data that I thought made it easy to understand some of the concepts behind how Big Data and SQL Server fit together.

    Sebastian Meine wrote an interesting piece on purging data, and how large data manipulation operations can benefit from indexing.

    The SQL Philosopher talks about some of the challenges of rapidly growing data in his environment.

    Vinod Kumar has a nice summary of what the challenges of Big Data in general can be.

    Bob Pusateri has a post on why he loves Big Data. I agree with him that Big Data gives the DBA a chance to shine, but also a chance for lots of headaches. You have to decide how you feel about that.

    Mike Walsh has a post on what he’s doing with Big Data, and Hadoop, with a client of his. An interesting read on the application of large data sets.

  • Creating Your Own Certificates

    Did you know that you don’t need to go to Digicert or Thawte, or any other company to get a certificate to use in SQL Server? You can create your own certificate.

    Why you would want to do this is a longer discussion, but suffice it to say that if your environment allows for self-signed certificates, you have a couple options for creating these in SQL Server and Windows. I’ll show you how easy this can be using these two methods:

    • makecert
    • CREATE CERTIFICATE

    Please be careful if you plan on creating your own certificates. The value of a certificate and asymmetric keys comes in the hierarchy of trust for these certificates and if you do not have a strong hierarchy, you could potentially be making your security worse, rather than better.

    Makecert

    The Windows Software Development Kit (SDK) contains a number of utiltiies, one of which is makecert. It’s a command line tool that creates certificates for you, and It’s easy to use.

    I downloaded the SDK, extracted it, and then fired up a command prompt, running this: 

    makecert -sv "c:\EncryptionPrimer\MyHRCert.pvk" -pe -a sha1 -b "01/01/2012" -e "12/31/2012" -len 2048 -r -n CN="HR Protection Certificate" c:\EncryptionPrimer\MyHRCert.cer

    This code creates a private key file (MyHRCert.pvk) and a public key certificate (MyHRCert.cer)

    You can click the link and read the parameters, but it’s really that simple. When you create this certificate, you can use the FROM FILE options for CREATE CERTIFICATE to load this certificate into your SQL Server.

    CREATE CERTIFICATE

    I guess technically you are using the CREATE CERTIFICATE in either case here, but this section looks at the actual creation of the certificate by SQL Server.

    CREATE CERTIFICATE is standard DDL, like so many other commands in SQL Server. The parameters are similar to those for makecert. Here’s a statement that matches up with the one above.

    create certificate MySalaryCert
   ENCRYPTION BY PASSWORD = N'R3allyToughP@ssword4You'
   WITH SUBJECT = 'HR Protection Certificate',        
   START_DATE = '20120101',
   EXPIRY_DATE = '20121231';

    Note that you don’t need to specify the algorithm or other parameters. SQL Server handles that for your. You also don’t need to specify the two files here. The database engine stores these keys inside the database. You should make a backup of them, and you can use the BACKUP CERTIFICATE command to do this.

  • T-SQL Tuesday #027 – The Big Data Valentine’s Edition

    tsqltuesdayIt’s T-SQL Tuesday time again, and I’m honored to be able to host the party for February 2012. This is my second time hosting, with the first being #013.

    T-SQL Tuesday is the brainchild of Adam Machanic, who hosted the very first blog party. If you’d like to be a host, contact Adam on his blog. The complete list of older topics is here: T-SQL Tuesday Topics – February 2012, so you can pick a new one if you’d like to host.

    Note, this is advance notice. The party is NOT today, it’s next week, February 14, 2012.

    The Rules

    Here are the rules your post must follow.

    1. Your post must go live between 00:00:00 GMT on Tuesday February 14, 2012 and 00:00:00 GMT on Wednesday February 15, 2012.
    2. Your post must link back to this post, and the link must be anchored from the logo (found above) which must also appear at the top of your post.
    3. Leave a comment or a trackback here on this blog regarding your post.
    4. ‘T-SQL Tuesday #027’ should be included in the title of the post.
    5. If you’re on Twitter, tweet about your post using the hash tag #TSQL2sDay

    Big Data

    godivaThe party falls on Valentine’s Day this month, and I thought a little notice would let you get your post done early, and then spend the day with your loved ones

    Since the media has a love affair with Big Data right now, I thought this would make an interesting topic. Data is the lifeblood of our careers and of many organizations. Slowly governments, companies, and individuals are becoming aware of just how important data is to us, and as they do, they want more of it. More storage, more access, and more analysis.

    That creates challenges for us as data professionals. We will have to learn to better manipulate, aggregate, summarize, and handle larger volumes of data in the future. I think this means we will have lots of employment in this area, and it means tremendous opportunity for those that learn to work with data well.

    This month I want to hear what big data problems you’ve solved, or interesting ways of working with big data, challenges that you struggle with, or cool hardware tricks that mask the problems in your code.

    Think big, and let us know how you work with Big Data.

  • Do You Need a Coach?

    Most of us reach a point in our careers where we think we can tackle most of the challenges we face ourselves. We may ask questions, we may need to read, study, practice, or more, but we tend to have the self confidence to go it alone, making our own decisions and giving ourselves feedback on how things work.

    However maybe there’s a better way. I read this piece about a surgeon that tried a coach and found it to be very helpful. To be fair, I’ve read Dr. Gawande’s book and enjoyed it: The Checklist Manifesto.

    It’s an interesting idea. We often pay teachers and coaches to help us with sports, with some areas of our lives, but most of us don’t bother. I wonder if there’s something that’s worth investing in here professionally.

    I think there is, and that’s probably the easy thing. Finding someone that you trust to be a coach, and you’d be willing to pay.