Voice of the DBA

Category: Blog

Be Extra Careful When Packing

Over the last couple of years I’ve gotten myself down to two small, carry-on bags for most of my trips. It has allowed me to save time in and out of the airport, and cut down on a lot of hassles from frequent trips (I flew on 18 round trips last year).

One of the ways I’ve done this is with a couple dedicated bag: one for clothes/shoes and one for the laptop and supplies. I don’t ever really unpack them, and I just move clothes in and out when I go. That always has me a little nervous as I am counting on things to be in the bags, but it’s worked for a dozen trips lately because I keep spares of all my supplies in the bags, replacing them as needed, but not using them for other purposes.

However that can come back to bite you. I did have a trip last year where I left my presentation remote at home since I’d been using it to practice and hadn’t put it back in my bag before I left. Fortunately that’s a minor inconvenience and not a major hassle.

I leave for the UK on Saturday for my annual visit to the Red Gate office in Cambridge, and I need to pack for a week. I’m hoping to slip in enough clothes to go with just my two bags, but we’ll see. I’ll also use this chance to double check I have everything packed I need since replacing stuff in another country is less than exciting to me.

April 12, 2012
Hashing Collisions
One of the problems with hashing is that you can have collisions from values that are not very similar. This means that if you are using hashing as a way to identify similar values, you need to make further checks with the original data after the hash matches are gathered.

This post will show a few examples of the collisions that can occur if you use the CHECKSUM() or BINARY_CHECKSUM() functions.

If we examine this code:
```
-- Checksum
declare 
  @i varchar(200)
, @j varchar(200);

select @i = 'LE';
select @j = 'AAAAAAAAAAAAAAAALE';

select 
  Plaintext = @i
, checksum = CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, checksum = CHECKSUM(@j);
GO
```
This returns a result like this:

Note that these two values are the same as far as the checksum hash goes.

If we switch to BINARY_CHECKSUM(), we can get similar results.
```
-- binary_checksum is no better
declare 
  @i varchar(200)
, @j varchar(200)
, @k varchar(200);


select @i = 'LE'
select @j = 'Ou'
select @k = 'MU'

select 
  Plaintext = @i
, BINARY_CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, BINARY_CHECKSUM(@j)
UNION ALL 
SELECT
  Plaintext = @k
, BINARY_CHECKSUM(@k)
GO
```
While these two functions can be useful, you do have to be careful with the results. A matching hash from these functions does not mean that the source data is the same.
April 11, 2012
Enabling Contained Databases in SQL Server 2012
One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

Enable the Instance setting

There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

Once you click the script button, you’ll get this T-SQL:
```
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
```
This will enable containment for the instance and you’ll be halfway there.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in 2012. Again, please don’t click OK, but click script.

The code will appear as below:
```
USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
```
You can also set this value when you create a database:
```
-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
```
That’s it.

These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.
April 9, 2012
SQL Server 2012 Beta Exams
I signed up for a few of the SQL Server 2012 Beta certification exams, and have been going through them over the last few days. The exams I signed up for are:
I took the first one on Monday, and the second on Wednesday. I have the other two scheduled for Friday and next Wednesday.

I can’t really comment on what was in the exam, but I did see some evolutions of the testing software, which was nice. I like a few of the changes, and they should help people demonstrate a few more skills than the old multiple-guess exams. There are still multiple choice questions, but some new ways to ask someone to show knowledge.

I’m not really worried about being certified, but I do like to see how the certification process works, I might write some stuff to help people (I’ve worked on three cert books in the past) and most importantly, I get a rough idea of what I know on the exams. They’ll ask me a few things I might not have looked at in depth, and I certainly found a few holes in my knowledge over the last few exams.

If you have the chance to take a beta, I’d encourage you. They’re free, and the time they take can be a valuable aid in helping plan your future learning.
April 5, 2012