T-SQL Tool House

An Introduction, by Bill Wunder

Right from the start, we could all agree there is more than one way to solve any software problem. Designing, developing, architecting, or supporting databases are not exceptions. There are lot’s of good database products and several possible operating environments to choose from when beginning to solve the problem of where do I put my data. There are a variety of application development models and programming languages available to help solve the problem of how do I store and access my data. And there even countless folks knocking on the door to choose from in resolving the huge and seemingly relentless question of who should help me with my data endeavors. Always it make sense to look around and see what you already have, and give a good look toward building upon what you find before you begin a course of building anew from the ground up. The economy of any particular choice will ultimately distinguish a good solution from a not so good one. And usually, the economy of reuse of what already exists offers an obvious advantage.

I - and I assume the vast majority of you incredibly intelligent and good looking sswug members - fall into the category of people someone chose to help with their data endeavors. Still, even if a relatively late comer, it behooves me as a DBA to take stock of what already exists as I endeavor to solve the problems that an organization or client has entrusted to me. In my case it’s a safe bet that we’re already running Microsoft SQL Server (else why am I here).  It’s also better than even money that some of the work I’ve done in the past will prove useful on the SQL Server at hand. I can pull out my scripts, create a few stored procedures on a server and away we go. Off solving problems faster than an army of well meaning developers can create them. The first question I need to answer is where should I create those few stored procedures and any allied data I need to collect to effectively fulfill my role as DBA?

Keeping with the always good practice of building upon existing elements I always want to look around to see any existing possibilities. My T-SQL tools tend to be self contained stored procedures that create any table they might need in the local database if it doesn’t already exist. The data I collect tends to be cumulative historical stuff useful to show trends and plan for growth. Not particularly useful for me to centralize this data on an administrative server somewhere because of the maintenance overhead and security concerns related to making sure the involved SQL Servers can talk amongst themselves, so I eliminate that possibility early on as a matter of course for most things I do.

I know I need to find a place to house my tool kit locally on a given SQL Server. I could throw the stored procedures my scripts will create into the master database or I could try to select a user database or I could even try to keep my scripts in files somewhere and never create enduring elements in any database. I need to ask myself if any of these approaches is particularly more economical than another. If I put anything in master then I loose the ability to easily restore my objects, I may also have to extend permissions in master and worst of all I need to create tables and add data and thereby introduce growth and fragmentation in master. I prefer master to be dedicated to the autonomic aspects of the SQL Server. As with a centralized administrative SQL Server, I have yet to experience an environment where it made any particular sense to use master for anything that SQL Server did not put there.

If I put my ‘objects’ into an existing user database I need to be fully aware that everything I create becomes the property of any developer that has ‘Create Object’ access to that database. Not necessarily a good thing to have 6 months of sysperfinfo history truncated on the heavily used development box when someone gets in the spring cleaning mood or to have the new person throw in a call to the block monitoring utility with some strategically placed 1 minutes WAITFOR DELAY’s in it in the stored procedure that the login page calls each time a user connects to the intranet. Regardless of any illusion of safety in using an existing database I want to practice good database fundamentals and isolate heterogeneous data when possible. So mixing my DBA tools into a user database is risky and I decide this is no place I want to leave any of my toys.

If I try to manage my scripts on the file system I quickly discover that my network share doesn’t do me much good on the CITRIX server or that my laptop doesn’t do me much good when it has boot up troubles or that I forgot to save that last little tweak I made and don’t have a clue what I did or that the hard drive failed and there is no backup. Don’t get me wrong, saving scripts to a file system for everything you do is a good thing. How else would anything make it to the source control repository? But using the file system as the sole repository for your work, your livelihood, is something I would hope no good database professional practices. That’s what databases are for!

Time and again, I’ve found the best solution to this problem is to create a database dedicate to my efforts. I don’t have to install a SQL Server or a new instance. I don’t have to modify any permissions. The database doesn’t get large. I can backup the database with my routine backups. I can restore it with no impact to the SQL Server or the application. There is generally no question as to the purpose of the database I use to house my DBA T-SQL toolkit: the admin database.

I’ve been working on building my T-SQL Tool House for quite a while now. Obviously it will remain a work in progress. Starting in 1995 I began learning about web pages and posted some of my tools and scripts to a public internet access server at the University of Denver that was – and still is - run much like a public TV or radio station: Nyx Net. I called my little virtual directory ?bill’s vast expanse. I quickly used up my allotment of 500KB and was glad to see them expand the disk quota to 2MB where it currently stands. I actually use just a touch more than the quota, but so far I get away with it. It worked out well that just at the time I needed more space along came that rowdy upstart Stephen Wynkoop and his merry band with www.swynk.com. I put a bit of stuff on swynk.com over a period of time. Then, about when Stephen began to be less a part of swynk.com I got the cable modem and a nice sized chunk of cyber hinterland where I put up some more tools at a URL currently named home.attbi.com/~bwunder including my DDL Archive Utility that has proven quite popular. Especially after Andy, Brian, and Steve put it up on the freeware section of www.sqlservercentral.com. Later, I got a chance to see my name in the credits along with the marquee stars like Stephen and that awesome red headed goddess Sharon Dooley and all the other brilliant minds contributing to www.sswug.org. 

While writing sswug columns I discussed some of the tools I keep in the admin database. Probably never saying much directly about how to build a better application, rather to simply describe ways I try to be a more effective Microsoft SQL Server DBA. I provided many descriptions and working examples of T-SQL, DTS, VB and what ever else has helped me keep my SQL Servers running. Hope you find them useful and interesting. Certainly I’d like to hear you comments (especially if you have a better way to solve a problem) and questions.

As do all good things, my time with sswug eventually came to a close. And att@home sold out to comcast. I kept the web pages up on the comcast web server until I finally moved away from Denver. Then after several months I got that content posted back on nyx.net which had by 2006 expanded the allotment of web space to 20MB. Everything about web sites falls into that interative process that is about all software. It is interesting sometimes to sit back and look at all the changes.

I might even have to figure out this blog business if there’s enough dialog and it doesn’t prove to be too high on the technology bar for me. I think I'll resist as long as I possibly can.

Bill