Best of 2013

As 2013 comes to a close I wanted to highlight some of what I think are the best of 2013 that I enjoyed outside of work.

Best Movies

1. Gravity
2. Fast & Furious 6
3. Man of Steel
4. Pacific Rim
5. The Hobbit: Desolation of Smaug
6. Iron Man 3
7. Thor: Dark World
8. Lincoln
9. American Hustle
10. The World's End

2013 was a great year for movies and by far, Gravity was the best of the year. I was able to see Gravity in a large format digital theater with 3D and Dolby Atmos, which made for an amazing experience that I felt like I was floating right alongside Sandra and George in space! FF6 extended that franchise even more and has me looking forward to FF7 in 2015. Not sure what the death of Paul Walker with do to the series future, but I am happy to see that they are going to push ahead and finish it and release it in April of 2015. Man of Steel was the best of the superhero movies in 2013, and is the first time a good Superman movie has been done since 1978.

Best TV

1. Orphan Black
2. Doctor Who
3. Game of Thrones
4. The Walking Dead
5. The Goldbergs
6. Big Bang Theory
7. Almost Human
8. Broadchurch
9. Masters of Sex
10. The Fall

TV did have some stand out new shows this year, but the new thing for me this year was discovering 2 shows and binge watching them both online. The first one that I binge watched was Orphan Black and it shot straight up to the top of my list as the best TV show of 2013! They have done a great job in the first season of keeping us going with the whole clone storyline, if you have not seen this show from BBC America yet, then you should. Unfortunately it is not yet available on any streaming services, I ended up buying the first season from Amazon Video and watched all 10 episodes in less than 1 day, that is how hooked I was! The other one that I discovered is The Fall, which a serial killer mini-series starring Gillian Anderson (The X-Files) set in Northern Ireland and with Gillian doing her Brit accent (she lived in Britain early in her life and moved back after The X-Files finished). The Goldbergs is one of the funniest shows that I have seen since Big Bang Theory, and most of that is probably because of the setting in the early-mid 1980's. Growing up in the 80's, this show just brings back so many memories, they have done their homework and you just have to look around at the props on the set to see that. Agents of S.H.I.E.L.D. did not make my top 10, mainly because it just didn't live up to the hype, I have high hopes that it can do better in 2014.

Best Music

I don't listen to a lot of new music, but the one album that I played over and over in 2013 was Random Access Memories from Daft Punk. It has been amazing to watch them change their sound over and over and evolve from just another electronic group to a true musical group. The mix of 70's funk/disco to their music made this by far my favorite album of the year. Most of the rest are just a bunch of individual singles from different artists (JT, Katy Perry, Lorde, Miley Cyrus, etc.), but Random Access Memories was the best overall album. The rest of what I listened to in 2013 were soundtracks from all of the movies listed above and more. I think that my favorite soundtrack of the year would have to be Man of Steel. I never thought that anyone could make a better Superman theme or soundtrack than John Williams, but Hans Zimmer did for Superman what he did for Batman and created some amazing new themes. The themes in Man of Steel were so alien sounding, but yet heroic and strong at the same time, which captures exactly what Superman is.

Best Gadgets

For best tech gadget of 2013, it would be the new Apple iPad Mini with Retina. I was a long time full-size iPad user, but I found that it was too big to travel with and when Apple finally announced the new iPad Mini with Retina display, then I decided it was time to switch. I love how small and light it is and I find myself taking it with me everywhere. I also made sure to get mine with Verizon 4G, which I also had on my previous iPad and would never consider getting a tablet without 4G. It's just far too convenient to use it anywhere and not have to worry about finding wi-fi. I also love my MacBook Air that I got at the very beginning of the year, the only issue now is the iPad Mini has become my go to device, so the Air doesn't get a lot of use. I also made the big switch from iPhone to Windows Phone in 2013, when the Nokia Lumia 1020 came out. I had been thinking about this switch for awhile and when iOS7 didn't make the big change that I was hoping from I knew it was time to switch. The 41MP camera on the Lumia 1020 is amazing and I find myself taking a lot more pictures now. The Windows Phone 8 OS took some getting used to, but now I can do everything that I need to and paired with my iPad Mini, I get the best of both worlds. A couple of other great gadgets that I use with both the iPad Mini and the Lumia 1020 are the headsets, Bose QuietComfort 15 and Bluez AfterShokz. I use the Bose on the plane or anytime I need to use active noise cancelling, while the AfterShokz are great for in airport or office wear when I still want to hear what is going on around me. While I did get the new Xbox One, the verdict is still out on that one. I tried it connected to my Dish Network DVR, but since it can't control everything on the box, just change channels it was very limiting since I do watch most TV from DVR, not live. And the voice commands that they keep boasting are still not there and I find myself yelling at it because it appears to not understand me most of the time. The new Kinect camera is amazing and works much better than the previous generation.

Best Enterprise Tech

And for something more related to my work and this blog, my favorite new enterprise tech that I haven't spent nearly enough time with this year, Microsoft's Power BI! I plan to spend a lot more time learning all of the features of Power BI and hope to post a bunch of blog posts about it, as I learn how to use it.

Thanks to everyone that reads my blog, and I hope you had a great 2013 and are looking forward to an awesome 2014!

Encryption in SQL Server (Part 1)

Recently I have been doing some work to add encryption to an existing SQL Server 2008R2 database for a client and I learned a lot about how TDE or Transparent Data Encryption works in SQL Server. The requirements for adding encryption in this case was that only certain columns in some tables of the databases would be encrypted, so I could not encrypt the entire database or tables in a database (this is possible in SQL Server though). Also one of the main requirements was to leave the datatype for the column that would be encrypted the same, and mask the data in that column. So, all of the actual encrypted columns would be new columns added to the appropriate tables. It was also required that the only way to access the decrypted values was by using new views that did the decryption.

With those requirements set I started to learn how to setup encryption in SQL Server and found many TechNet articles that helped me. To begin with some of the great resources for introduction to encryption in general are:

• Understanding Transparent Data Encryption - http://technet.microsoft.com/en-us/library/bb934049(v=sql.105).aspx
• Encryption Hierarchy - http://technet.microsoft.com/en-us/library/ms189586(v=sql.105).aspx
• SQL Server and Database Encryption Keys (Database Engine) - http://technet.microsoft.com/en-us/library/bb964742(v=sql.105).aspx

These posts helped me to get an idea of how best to setup the different keys/certificates that are required in SQL Server to make the encryption work and also provide the required security to lock down who could access the decrypted values. The important thing to realize about setting up encryption is that SQL Server is using a layered approach, you aren't just setting up one set of keys and then you are done. Instead you are setting up multiple keys that each use the previous key to build the next (see Encryption Hierarchy article for graphical representations of this).

In SQL Server you start with the Service Master Key (SMK) which is setup for your automatically when you install a SQL Server instance. This key is protected by the Windows OS itself using the Data Protection API.

The next layer of key is the Database Master Key (DMK), which is required on each database that you will be using encryption in. The DMK is created by issuing a command on the database you want to create it on.

IF NOT EXISTS
   (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
   CREATE MASTER KEY ENCRYPTION BY
   PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn'

 http://technet.microsoft.com/en-us/library/ms174382(v=sql.105).aspx

In order to create the layers of keys required to actually do the encryption of your data you must have the DMK already created in each database. There can be only one DMK per database in SQL Server and it is protected using the password supplied and the Triple DES algorithm (AES_256 is used in SQL Server 2012 and above). Since automatic decryption of the DMK is required for use by other SQL Server commands, a copy of the DMK is also protected using the SMK and stored in the database it is created in along with the master database. This copy of the DMK is the one that can be updated easily as you move the database or it's backups from server to server and still allows you access to the encrypted data without requiring you to decrypt all of the contents and then re-encrypt them again using the new DMK. Updating the DMK is done with the ALTER MASTER KEY command (http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx).

Since all encryption is dependent on both the SMK and DMK, it is also good practice to backup both of these keys to files for safe keeping, which can be done with 2 commands:

BACKUP SERVICE MASTER KEY
    TO FILE = 'C:\localhost_SMK.smk'
    ENCRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl'

http://technet.microsoft.com/en-us/library/ms190337(v=sql.105).aspx

BACKUP MASTER KEY
   TO FILE = 'C:\localhost_AdventureWorks_DMK.dmk'
   ENCRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'

http://technet.microsoft.com/en-us/library/ms174387(v=sql.105).aspx

The password that is supplied with these BACKUP commands is only used to encrypt the files that are created on the file system. In order to restore these files, the appropriate passwords will be required, so they should be kept safe for future reference (as should all passwords created for these keys).

This will get your SQL Server instance and database all setup and ready to encrypt the actual data stored in them.

In my next post I will cover what Asymmetric Keys and Symmetric Keys are and how they are created and used in SQL Server to encrypt your data.

SQLSaturday #271 Albuquerque

I'm a bit late in getting the news out on my blog, but I will be presenting at SQLSaturday #271 in Albuquerque, NM on January 25th! I will be presenting my new "Master the Date Dimension Like a Time Lord" presentation that I did for the first time at PASS Summit 2013 as a Lightning Talk.

Now you will be able to see it as a full hour long session and dig into all the details on using a single script to create a Date Dimension that can cover all of the uses you can think of for a data warehouse! And yes, if you can't tell from the title of the session, there will be references to my current favorite TV show, Doctor Who mixed in! :)

Please register to attend SQLSaturday #271 and I'm looking forward to going into Albuquerque early to enjoy the area with my family and see all of you at the event on 1/25!

Denver SQL Server User Group Holiday Party

Instead of our normal monthly meeting next Thursday (12/19), the Denver SQL Server User Group will host a Holiday Networking Party sponsored by TekSystems at Great Northern Tavern (8101 East Belleview Avenue, Denver CO 80237). We will have limited space, so if you want to come you will need to RSVP at http://denversqlugholiday2013.eventbrite.com. The party will start at 5:30pm and go until we get kicked out of the room or run of things to talk about! There will be appetizers and a limited number of drink tickets as well as some prizes to give away. It will be a great time to get together and just talk about whatever we want to, no formal presentations, just food, drink and good company! :)

As a courtesy to others that may want to come, please only RSVP if you know you can attend and if you have RSVP'd and find that you can no longer attend, please cancel your RSVP via the EventBrite page.

Also, while I'm talking about DSSUG, just wanted to send out a quick thank you to everyone that used our PASS Summit 2013 registration discount code. The group will receive $250 from PASS because of the number of people that used the code for their registration to PASS Summit 2013! We will be sure to put that money to good use for the group in 2014. Keep your eyes out in 2014 for more of this type of registration discount code for other conferences.