What Service Pack Are Your Client Tools At?

So you have a plan to apply service packs to your SQL instances. You test them out and try to keep a regular schedule to ensure they are up to date. That is great.

What About SQL Server Management Studio?

What about the copy of SSMS that a developer is using to create scripts? This is spring of 2010 so perhaps I shouldn’t have to be inspired by this story but either I am alone and you can laugh at me or maybe you’ll double check and avoid the same issue. Some indexes recently worked their way into production in an environment that I am starting to put controls over, but hadn’t at the time. I use Michelle Ufford’s (SQLFool on twitter) index defrag script to programatically handle my index rebuilding/reorganizing and the script yelled at me and failed for a couple indexes. Apparently they were set to disable page level locking. What? This is a pretty advanced option that you’d want to only use if you suspect page level locks are causing issues and want to force SQL to maintain row level locks. In this case it was for a few columns in some very (for my organization) large dimension tables. It didn’t make sense. I asked the developer and he had no clue what I was talking about. Come to find out, he is using SSMS 2005 RTM against this SQL Server 2005 SP3 environment. Well there is a bug in the SSMS 2005 RTM version that automatically unchecks the “enable page level locks” option.

How Can I Check?

This is something you should be doing anyway. There are also other differences between versions and it is generally best to try and match (as best as possible) the versions you are using. I would say that the client tools can generally be at the latest even if you have several variations of SP/CU levels on your instances.

• E-Mail – You can try the “Please check your SQL Server Management Studio Version” e-mail option. Explain to folks how to check (Help –> About and look at the version number). You can even ask/require that everyone send you a screen shot of their findings. That might work, that might be a pain to manage unless you are in a smaller environment.
• SMS – I am not an SMS/SCCM knowledgeable person but I know these packages can inventory software on desktops and many companies use these to push software compliance. Well work with whatever team manages SMS/SCCM and have them look at this. Perhaps they can at least do reporting at first. You can work with the one offs and see if any edge cases exist. If not, perhaps an upgrade of the client tools automatically is in order, perhaps warnings/e-mails and a process to prevent connections if folks fail to change/etc.
• Programatically? – PowerShell is still on my list of technologies to get a lot more familiar with and I bet it could help here, perhaps. I can’t give you a neat script but the version of the ssms.exe file  will give you an even more detailed look at what version you are running. You’ll see the CUs as well as this post by Bob Ward at Microsoft explains.
• How do you check and enforce? I’d love to hear what other people do in their case to ensure compliance to the standard version/service pack (if you set a standard) and to ensure folks are upgrading client tools appropriately.

The Bottom Line

A code check/review would have potentially turned this up so one should have been done, they are now that this environment is more tightly controlled but perhaps that one option could have slipped through the cracks if we hadn’t already bumped into it. The bottom line here is that we may not be desktop technicians, SMS administrators, etc. but we should be aware of what tools our connections are coming from and we should get a voice in the process of determining that that is set in stone. Otherwise we risk strange issues and rat holes.

I’ll make a part two to this post and blog about various SET options and checking those periodically, setting standards. Sounds silly and nit picky but it can affect plan reuse and recompilations. Check back here or subscribe to the feed for the part two post.