There are lists upon lists of good, common best practices on which most SQL Server professionals agree. For example, here’s one from Brad McGehee called the Sure DBA Checklist. Knowing what to check is just the start of the process. Now, you need to go and check it. Look through that list. It’s frighteningly large. Personally, I’m lazy. I don’t want do all that junk, but, I do want to be sure that my servers and my databases are well set up. If only someone made a piece of software that could do some of this work for me so that I can sit on my ever expanding bottom and relax with the knowledge that my systems are configured correctly. Well, we’re part way there. Here is SQLCop, a free tool from the people who created the lessthandot.com IT community. It runs a number of standard checks on your server, and on whatever database you assign it to. Its whole purpose is to identify common issues and inform you of them. I found it to be quick, accurate and informed. Let’s check it out.

Install

What install? Download the executable and run it: You’re done. The executable stands alone and runs as-is. No install required, which is really great. You can carry this around with you on a thumb drive and run it when you get to a new company or a new server. The only issue is, you do need internet access from the location where you run it. If you don’t have it, it will run, but you won’t get all the nifty information from the lessthandot blogs that comes up; but more on that later.

Using SQLCop

Firing it up will immediately bring up a login interface like you see below:

Provide it with the appropriate information to connect up to your server and database. No surprises. The software launches, and then presents you with a new window where you can start to explore the compliance checks as they relate to your system. The screen is divided into three pieces. Across the top is a toolbar that lets you change databases, ask a question at lessthandot.com, donate if you like the tool (yes, it’s shareware, support it), run a report showing all the issues it found, print the screen or get help:

On the left side of the screen are the categories of all the checks that SQLCop performs. You can click on a category to expand it out and see the checks it ran:

Then, the main part of the screen is where information is displayed, and oh what information you have at your fingertips. Just the startup screen is filled within information explaining what SQLCop is and what it does:

The interface is pretty clean and very easy to use. There are few functional surprises waiting for you. But, if you thought, like I did, that it performed the checks when you opened, you’d be wrong. I immediately ran the report and saw that there were zero issues with my copy of AdventureWorks2008R2, which couldn’t possibly be true (there are a couple of standard best practices violated by Microsoft, go figure). What I found was that when you drilled down on a particular check, then you saw the results. For example, one of the checks in Code is “Procedures without SET NO COUNT ON,” a pretty standard best practice. Expanding that out did two things, it found that I had several procedures without the setting and it loaded an article from lessthandot covering that particular topic. Here is my list of procedures (all of them are examples from my presentations, oops):

And in the main part of the screen is the article:

This is great stuff. It’s a wiki, so if they’ve got something wrong, you can go right in and start cleaning it up to help all the other users of SQLCop. It’s also an excellent way to help you understand what these checks are and why they’re being performed, and, most importantly, why you need to fix whatever it is that’s broken. By the way, it’s OK that my demo procs don’t have SET NOCOUNT ON, really.

I did see a couple of place where, when I clicked on a check, the web page would go into a re-load loop or something, but it was constantly refreshing which was a little annoying, but it only happened a couple of times, in fact, I had a hard time reproducing it, so I couldn’t even submit it as a bug.

Other than that, this thing worked really well. It would run the checks as advertised and let me know what it was running and why. Better still, it showed me how it was doing the check. For example, one check found a lot of problems in AdventureWorks2008R2, Tables/Views, “Missing Foreign Keys.” The check ran this script to identify columns with the phrase ID in them that are not part of a foreign key relationship:

Personally, I don’t agree with this check, although, it’s one approach for identifying where foreign keys should be, but aren’t. But what I love is the education I can receive from SQLCop because it’s showing me exactly how it’s making the best practices check that it’s informing me about. That is invaluable whether you like the check it’s performing or not.

Sometimes when there is an object that’s been identified as having an issue, you can click on the object and you’ll see the SQL code for that object. For example, here’s one of my test procs missing it’s SET NOCOUNT:

The Checks

The whole reason SQLCop exists is to perform checks on your server and your database. These are pretty standard best practices that it’s looking at and shouldn’t be very controversial or an issue for most people. Here is most of the list:

You can see that with everything expanded, the checks are green for those that passed and red for those that failed (assuming you’re not red/green color blind). I really like the majority of these checks. It’s all the simple things that you should be doing with your code and structures. Get this right, then you can concentrate on the more difficult aspects of setting up and maintaining your system. There are a few of the checks that I don’t agree with. I already mentioned that I think looking at columns with ID in them isn’t necessarily a good way to validate foreign key use, but I see the utility of it in some circumstances. I also don’t agree that all foreign keys should have indexes, so this check could be an issue for some people. But those are really nits and personal preference. Overall this is an excellent set of best practice checks that are run for me automatically. Great.

Once I’ve run all the checks, which I did do by using the “Expand all” check box on the screen, I can run a report that shows everything that failed or was tagged as requiring attention on my system. Here’s part of that report:

The way that this is laid out works well for me. You can just hand it to your boss or a system admin and tell him, fix this stuff.

Summary

I like this piece of software and the checks it performs. Are there a couple I’d ignore? Yeah, but the vast majority are excellent and worthy. This will make your life easier. It’s a fast way to validate compliance with standard checks that will help the performance and stability of your systems. It acted a little twitchy at times, but overall it was quick, stable, and above all, helpful. I recommend trying it out on your systems. I think you’ll be happy with the results (although it might generate some work for you in the near term).

It is likely that you’ll find some of these checks against your database so useful that you’re going to want to automate them in order to make them a part of your testing in development. If so then you’ll be pleased that SQLCop has teamed up with SQL Test so that they can be incorporated directly into your SQL Test regimen and automatically be a part of your development testing cycle.