SQL Server with Mr. Denny


October 12, 2012  5:23 PM

Recommended reading from mrdenny for October 12, 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Denny

October 10, 2012  9:00 AM

Encrypting data in the same column

Denny Cherry Denny Cherry Profile: Denny Cherry

I wrote a little while ago about the fact that sensitive data needs to be encrypted within the database for all applications.  This is the first technique that is available to you to encrypt data in a database with as little outage as possible.

In this technique we’ll encrypt the data using just a single column.  This technique requires butting some additional logic within the application to figure out if the value is encrypted or not, but other than that logic, which you can leave in and strip out later the changes to the application are pretty minimal as the column stays the same, so that means that the stored procedures don’t need to be changed.

The first thing to remember is that the encrypted data will be larger, possibly much larger than the plain text version of the data.  Because of this you’ll need to increase the size of the field which you’ll be putting the data into.  Now the good news is that if this column isn’t indexed this change should be pretty quick and easy as it should just be a meta change which tells the SQL Server that the column size can be bigger without having to actually change the pages.  You can see this by making some changes to the [HumanResources].[Employee] table within the AdventureWorks database.  By turning on STATISTICS IO and using the ALTER TABLE statement we see that there is no IO generated when we change the size of the LoginID column from nvarchar(256) to nvarchar(512).

set statistics io on
alter table MyTable
alter column LoginID nvarchar(512)

Once the column is made larger the .NET code needs to be modified to see if the data is compressed for not.  Now there is no sure fire way to check to see if a value has been encrypted or not, but a pretty good test is to look at the last two characters of the value.  If they are both an equal sign (==) then it is probably safe to assume that the value is encrypted.  To don’t want to just attempt to decrypt the data and look for an error message, and if there is an error assume that the encrypted value is in plain text, throwing and catching error messages in .NET is very expensive, especially compared to simply checking to see if the last two characters are an equal sign.  This isn’t to say that you shouldn’t have TRY/CATCH logic around the code that decrypts the values as someone could easily enough put two equal signs at the end of their password.

At this point either a .NET app or a T-SQL script can loop through the values in the table which aren’t encrypted and then encrypt them, updating the rows which aren’t already encrypted.

Look for more blog posts in this series on how to encrypt data which already exists within your applications database.

Denny


October 5, 2012  5:08 PM

Recommended reading from mrdenny for October 05, 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Denny


October 3, 2012  2:00 PM

SQL PASS Summit First Timers Webcast is in 2 Weeks

Denny Cherry Denny Cherry Profile: Denny Cherry

Don’t forget that my SQL PASS Summit First Timers Webcast is going to be in just two short weeks on October 17th 2012 at 1pm Pacific / 4pm Eastern (find your local time).  This is the second annual SQL PASS Summit First Timers Webcast as the one I did last year was so well received.

During this webcast I’ll be telling you a lot of what you’ll need to know about the Seattle Convention Center, how to get around town, how to get to the hotels from the airport, where to find places for dinner, how to find your way around the convention center, and much more.

If you’ve been to the PASS Summit in Seattle before I’d recommend watching anyway as there will be some new information for you.  Most importantly where breakfast and lunch will be served as the lunch hall is moving.

You do need to fill out my annoying registration form to get the meeting info.  The form is painless I promise.

Denny


October 2, 2012  2:37 PM

The Hotel Excelsior Dubrovnik needs to actually provide what they offer

Denny Cherry Denny Cherry Profile: Denny Cherry

So for the last couple of weeks Kris and I have had the pleasure of being in Croatia, first for a SQL Conference second to spend a few days on vacation for our 13th Wedding Anniversary.

The SQL conference in Zagreb was great, I don’t have a single complaint about the hotel, the conference, the organizers, etc.

What I’d like to bitch about today is the hotel which we were supposed to stay in for 4 nights in Dubrovnik, Croatia.  The hotel that we were at was the Hotel Excelsior Dubrovnik which is right on the beach in Dubrovnik.  In case the link stops working, or is changed here’s what was listed on the website which I booked through.  The hotels actual website shows the exact same description, so I’ve only included a screenshot of one I used to book.

Now as I read this when I booked a room I could expect a room with a stunning vistas (which I assumed mearons a view) of the island of Lokrum and Old Town.  A quick look at Google Maps (below) tells me that for all 158 rooms to have this view, they all have to face the water.  You can easily enough see the hotel (the red mark on the right of the map) with the island to the south and the city to the west.  However when we were shown to our room it wasn’t on the south side of the building.  IT was instead on the north side of the building facing away from the island and old city.

So I did was everyone would do I asked for a room which I paid for and which faced the water (at this point I would have taken anything which pointed towards the water, read further down for a look at the “view” which we had).  The room which we had, which was the only room on our side of the building on our floor had instead of a large picture window and balcony had a window which was about 3 foot by 3 foot (about 1 meter by 1 meter).

Just by looking at the room it was clear that this room used to be a storage room.  Not exactly the 5 star hotel room which I had booked.  Sadly by the time we arrived at the hotel it was already about 10pm, so it was to late to do much about the problem.  We slept in a little the next day, and spent quite a while speaking with the hotel staff trying to get another room, any other room.  The only answer that we got was that they were totally full and that they didn’t have another room to move us to.

I eventually got totally pissed off and started looking for other hotel rooms in the city, thinking that if I booked another room and told them I was checking out early they might actually be willing to help me continue to pay them money.  Instead when I told them that we’d be checking out early and why, they still couldn’t do anything for us.  Apparently some rich jack ass showed up needing more rooms than he actually reserved so someone is his party got our room and we got screwed.

Now as you read on the website above, we were expecting a new of the water, the island and the old city.  Instead, we got this.

 

Not exactly the view that I was expecting, at all.  Needless to say that this was the end of my patience.  Now if you are thinking that I was just guessing as to what the view should have been, here’s the picture that was posted on the hotel’s website.  Given that I was booking a normal room, and this was from the webpage for the normal room I expected something similar to this.

Thankfully there’s also a Hilton in town.  It isn’t right on the water like this hotel was, but I was able to book a room there for the last two nights (we burned the entire day after check in fighting with the hotel and looking for another room).  I informed the hotel that I was moving there last minute because the current hotel that we were in wasn’t acceptable and why.  The Hilton was king enough to give us a room with this view.

 

This was a little more along the lines of what we were looking for.  The old city and a view of the ocean.  Pretty much exactly what the doctor ordered.

After all this would I go back to Dubrovnik again?  Absolutely.  Would I ever bother trying to stay at the Hotel Excelsior Dubrovnik again?  Nope, never.  Assuming that I’m lucky enough to spend time in Dubrovnik again, I’ll be booking my room at the Hilton again.  While it’s a little further away the room was so much better it’s just amazing, and the staff was more helpful (the concierge at the Hilton went out of his way to ensure that we had excursions to do on each of the three days we were there, including the afternoon after we  had checked out even offering us the showers in the spa if needed after we had checked out).

P.S. Incase you think that I wrote this while I was still there and pissed, I didn’t.  I sat on this for 4 days, not writing this up until I got back to the states.  If I had written this while still at the Hotel Excelsior Dubrovnik like I wanted to, you probably wouldn’t be able to read this at work without getting fired.


September 28, 2012  5:04 PM

Recommended reading from mrdenny for September 28, 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Denny


September 26, 2012  7:00 PM

The 2012 Dynamic Database Project Award

Denny Cherry Denny Cherry Profile: Denny Cherry

SearchOracle.com and SearchSQLServer.com are teaming up to select three finalists and one winner of the 2012 Dynamic Database Project Award; and I’m going to be one of the judges.

To enter review the Dynamic Database Project Award webpage, download the entry form, fill it out and submit it.

The three finalists will have their projects featured on SearchSQLServer.com or SearchOracle.com; and the one winner will receive a brand new Apple iPad.  (See the award webpage for full details.)

Thanks,

Denny


September 21, 2012  5:21 PM

Recommended reading from mrdenny for September 21, 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Denny


September 17, 2012  2:00 PM

SQL Saturday 157 Slide Decks and Sample Code

Denny Cherry Denny Cherry Profile: Denny Cherry

This last weekend we had our most recent SQL Saturday in San Diego, CA.  This was SQL Saturday 157 and it was a huge success.  Making this SQL Saturday even better than most was the fact that the SQL PASS Board of Directors had their quarterly meeting the days before so most of the board was able to attend the SQL Saturday as well in addition to all the local speakers and a few out of town speakers who were able to fly in.

I presented two sessions at this years SQL Saturday San Diego, and you can find the slide decks and the sample code for those two sessions below (including the slides which I didn’t get to).

SQL Server Partitioning From The Group Up

Table indexing for the .NET Developer

Even though the weather wasn’t quite what we were expecting (it’s supposed to be 80 this time of year, not 105) I still had a great time with everyone and I can’t wait to see everyone at the SQL PASS Summit in just a few short weeks.

Denny


September 14, 2012  5:23 PM

Recommended reading from mrdenny for September 14, 2012

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: