A Sane TSQL Script Library

Earlier this year I got to speak at SQL PASS on performing under pressure.  Leading up to that event I previewed my session at a couple SQL Saturdays and during 24 hours of PASS.  In each setting I promised at some point in the future I would do a blog series on maintaining a TSQL script library.  A great way to stay calm under duress.

Just like any other technical specialty, a good DBA is only as good as his mastery of the tools at his disposal.  In many cases these come down to scripts.

If you have a library it’s probably similar to the library maintained by every other new, mid-level, or senior DBA.

Step 1. Create a folder.

Step 2. Download like mad.

This is a copy of a script library given to me by a good friend.  A great many years of knowledge and TSQL prowess is buried in these folders.  So much so, that I have trouble using it myself.

The problem with a folder system like this is that it’s easy to loose track of everything you’ve dropped in there – as well as what version of SQL Server it’s meant for, and frankly, whether you’ve even tested it or just downloaded it because it looked really cool.  How many times have you downloaded a script that looked interesting… “for later”?  This sample folder collection has several hundred actual TSQL scripts.

Solution:

I recommend taking a more lifecycle approach to your scripts instead.

Have a folder for new, untested, or untrusted scripts – only promote them to your library once you’ve tested and understand exactly what they do.  In my own library this area is called my “Test Bench”.

From there you might have SQL edition specific folders and as new editions of SQL come out you promote from edition to edition.  Or, you might just keep a current library if you don’t do a lot of cross version support.  Either way you should only promote scripts from the test bench as you’ve ensured they work as intended.

Further, ensure that any script, whether you wrote it or not, is enhanced with top level comments that identify important issues like.

1. Where it came from
2. Who wrote it
3. What it does
4. If it modifies data or alters schema
5. If there is potential performance impacting overhead
6. What versions of SQL Server it’s meant for

If this sounds like too much work – it might be because you are collecting waaaaaay too many scripts.

Your SQL Script library shouldn’t be collected like a 12 year old searching for Pokémon cards.  You don’t “gotta get ’em all”.  If you do want to make a repository for that kind of stuff, keep it separate from your trusted library – probably even separate from your test bench.

Only fill your primary library with scripts you understand, trust, and use regularly enough, that they are worth maintaining and keeping up to date.  Or, scripts that you know you may not need to use often, but if you do need them, you’re going to need them in a big dang hurry.

Chances are, for most that other mundane stuff, the script you want is probably more easily obtained via the pre-built template section of SSMS or by just bing-googling on the fly, and testing as you need.  We’re not recreating books online here.

What falls into the “precious enough to maintain myself” category for me?

I have security scripts that find orphaned users.  Also scripts that can move users from one database instance to another – something I tend to do a lot when building failover partners, or re-creating environments during life-cycle activities.

I have a few space and index health related scripts I check all the time.  I also have some maintenance scripts that look for irregularities in a database.  Brent Ozar’s blitz script is in there.

Finally, I have a performance monitoring and tuning area.  Adam Machanic’s sp_whoisactive is in there as well as some of my own stuff.

Above when I said, I don’t just put it in any old folder on my drive.  That is because I like keeping my base library right in SSMS.  You can add your structure right into the same Template Explorer section  (CTRL+ALT+T) that already exists.

A great side effect of storing your library this way is that you get an area built right into SSMS that makes it easy to quickly grab your scripts.  It’s also in there with all the other templates that Microsoft thought you might want to have handy.  Further, you can take advantage of parameter prompts.  Tim Ford explains template prompts in detail in this article on the template explorer.  I also cover it briefly in my 31 days of SSMS series.

Going the extra mile, enhancing with prompts, makes a huge difference when running a script under duress and the GUI starts prompting you nicely for what parameters it wants in order to run correctly.  Way better than you accidentally running something you configured for “last time”.  Savvy DBAs have been doing this for awhile.

Hint:  Also give your library a name starting with AAA so it shows up first.

What about team collaboration or source control?

Awhile back I started working on a little open source project called DBA toolbox.  Something that would allow an entire team to share a library.  I discovered however that the cloud makes it un-necessary and I can keep using my preferred SSMS Template technique and still share and collaborate with others all in one effort.

In the next post I will show you how using GIT makes it possible to continue to use the template benefits of SSMS but also adds a modern source control tool right on top.  This gives you the ability to track versions, share and collaborate with others, and move those scripts from environment to environment or SQL Server version to SQL Server version.

Advertisement