Voice of the DBA

Author: way0utwest

  • Slicing and Dicing

    This piece was originally published on Aug 12, 2009.

    I like music, often listening on headphones while I’m working. We often have an iPod in the car when traveling and take turns picking songs, which prevents arguments and lets us all get a feel for what the rest of the family listens to. I’ve even heard some interesting songs that way that I might not have heard otherwise. My son also has a Zune that he uses at night and while cutting grass, and while he likes picking his songs, he’s also used the FM radio quite a bit to listen to a local station he likes, getting a wider variety of music than he otherwise might hear..

    Recently Apple announced that they were looking to re-invent the album with the release of liner notes and booklets along with music. It looks like a way to give more value to consumers, and perhaps convince them to buy whole albums instead of particular songs. I think it’s interesting, and I am one of the few that misses browsing through albums and seeing the cover art that used to go with them. CD liners notes aren’t the same, and digital downloads seem far removed. For those of you that don’t remember what an album is, I’ve included a photo 😉

    I remember awhile back the AC/DC released a new album only at Wal-Mart, specifically because they thought that the album deserved to be kept alive, and they didn’t like singles being sold. They felt it reduced the value of the experience. I can see that point of view, and some record company executives think that the iTunes model of singles is hurting the business.

    I think this isn’t just a music issue, but more and more a digital information issue. There are lots of companies and business models that depend on bundling things together in order to grow value. In fact, a lot of what we do as database people is find ways to put information together and get more value. There might be some minimum effort (and cost) required to do so, and if we had to slice and dice each section of information up and sell it separately, many pieces of data might not be worth selling or even gathering.

    Think of television. If the Golf channel had to be purchased separately, it might not exist. But by bundling the Food channel, the Golf channel and a few more in with other selections, they can all get a slice of the revenue and find ways to survive. The same thing might happen with data marts in your company. The inventory mart might not be able to justify its existence if it had to be built separately at its own cost, but if it’s a spin-off of a larger data warehouse, the incremental cost can exist.

    In fact, many IT departments might have problems justifying their costs if they had to exist as a separate entity. We might see more outsourced IT companies if that was the case. By building them into the company and absorbing the cost, the company gets some benefits and synergies that they might not get from an outsourced environment.

    I am not sure if iTunes is bad for the music business, or if albums are good or bad, but I do like that the model exists and companies are trying to evolve it to work better. I hope that other digital sources of information remember that they don’t just exist to server the market today, but they need to evolve and find ways to server it better in the future.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, po

  • Traveling in 2013

    It was a busy year for me. As I look back at my speaking CV for 2013, here’s what I see:

    What a year. I also had two trips for Red Gate in here that didn’t involve any speaking.

    For the year, this gives me totals of

    • 30 events
    • 20 trips (2 non-speaking)
    • 58 talks

    Wow. The busiest year by far for me. Almost 60k miles in the air, which is the most I’ve ever flown. I know some people do a lot more, but this was quite a bit for me.

    The upsides of the year were meeting lots of neat people, getting to 6 or 7 new cities I’ve never seen, and not traveling in January, and only a short, business trip in August.

    I don’t know what next year will bring. In the first quarter of 2014, I’ve already got 3 or 4 trips tentatively planned along with a couple local events. I suspect I may end up with more trips next year, but likely no more events or days gone from home as I aim for shorter trips whenever possible.

  • Powershell in a Month– Day 4 and 5 – Providers

    This is part of my Powershell Challenge, to learn more about PowerShell (PoSh) using the Learn Windows Powershell 3 in a Month of Lunches book by Don Jones.

    Day 4, and 5. This wasn’t a long chapter, but it came right around travel and vacation, and as a result, I ended up taking about 2 hours across multiple days. Mostly because I had to re-read things and re-type them. As a result, I’m marking this as two days. Plus, this will now line up the chapters.

    Providers are the “drivers”, I guess for Powershell. They allow you to interact with various systems through the Powershell commands. Another way of putting it is that they take some item and make it look like a file system. This means you can navigate the registry (with the registry provider) like your disk with CD, DIR, etc. It’s also why you can move through SQL Server in a folder-like hierarchy.

    This was a short chapter, and I’m not sure why it took so long, but I moved slow going through the exercises to creates files and folders, learning to use the New-Item or Set-Location commands instead of the mkdir or cd ones.  I did find this interesting.

    The lab exercises were easy, and once I could focus, I got through them quickly.

    The next chapter is pipelines, which is where I think the power of PowerShell starts to come through.

  • Symmetric Keys

    One of the encryption options in SQL Server is symmetric key encryption. This is the type of encryption most people think about when they consider encrypting data. In symmetric key encryption, we use a key to encrypt data, and then also use a key to decrypt data.

    The key used for encryption and decryption is the same in Symmetric Key Encryption. This is why we call this symmetric. Just like with a house lock,

    image

    the key that locks (encrypts) also unlocks (decrypts). This is a picture of my front door and the lock uses a single key.

    In SQL Server, we create a symmetric key and use that to encrypt data and also decrypt it. Here’s a simple example:

    DECLARE @plain VARCHAR(200), @cipher VARBINARY(5000), @decrypt VARCHAR(200)

SELECT @plain = 'This is the plain text.'

-- encrypt
SELECT @cipher = ENCRYPTBYKEY(key_guid('MyFirstSymKey'),cast(@plain as NVARCHAR(200)));

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher

-- decrypt
SELECT @decrypt = CAST( DECRYPTBYKEY(@cipher) AS nVARCHAR(200))

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher
     , 'decrypt' = @decrypt

     

    That’s it. If you run it, you see the original text, the encrypted text, and the decrypted text.

     

    symkey1

     

    In another post, I’ll go into more options that are available for symmetric key encryption.