Introduction

SSW SQL Auditor is an extremely useful tool which allows developers to

generate reports on design issues and areas where SQL performance could be

improved. It does this by checking the database design against common SQL Server

design rules and report on the elements that do not conform to those rules. For

an example of the kind of rules checked, have a look at the product's info page

(http://www.ssw.com.au/ssw/SQLAuditor). 

This allows those who are responsible for QA in the design process to identify

and fix problems in database design, ensure that standards are being upheld and

prevent bad practises from creeping in. 

Once this process has been completed, SQL Auditor has wizards that generate SQL

scripts to correct common problems. 

SQL Auditor works on SQL2000 and SQL7.0 databases.

Environment

SQL Auditor has been written using .NET, so as long as the target environment

is running v1.1 of the .NET Framework and the latest version of MDAC you're good

to go.  I'm running Windows XP (Tablet Edition) , and it has given me no

problems whatsoever.

Installation

When you download the product, you'll notice that SQL

Auditor v9.30 Comes in two flavours.  There is a 1.88MB download which has

no Crystal Reports files (for those who already have the Crystal Reports

Run-time), and a 8.19MB alternative which includes Crystal

Reports runtime.  The Crystal Reports version allows you to create

printable reports on the results, whereas if you opted for the 'non-Crystal'

version, you would still get the options to view the issues and generate the

necessary SQL scripts, obviously just without the reports.  The install was

dead easy, just follow the prompts and you can't go wrong.

Using SQL Auditor

Use is wizard based, so it's very intuitive to work through

and follow.  There is a sample database available that has issues which you

can then "fix as you go" as part of your evaluation of the product. I also appreciated the tips on each screen as you work through it

(Figure 1).  It goes a long way to explaining why something is a

best practice, which is - well - good practice!

Figure 1 : Note the screen tips - in this case regarding connection pooling.

As you work through the wizard you will see the key areas to 'analyze', and again there is an area that links the user back to some practice rules.  I had a read through the standards promoted in the 'Rules (web links)' section (Figure 2) and they are good. If you don't have any form of SQL standards documentation, and you are looking to create some, then this would be a good place to start.

Figure 2

In Figure 3, you will notice that I have elected to do some 'database optimization', and we get a good list of rules to check against - and you have the option as to whether or not you want to enforce/check that particular rule.  I guess that some of the rules could be subject to a bit of debate within teams - things like varchar vs nvarchar, datetime vs smalldatetime, etc - but like I said, you have the choice of whether or not you want to enforce a rule. Decisions as to the datatypes that are recommended are made by analyzing the data in the table. What this means to the user, is that if SQL Auditor returns a rule suggesting you change something, then that is based on the contents of those tables (plus any foreign

keys hanging off that field) and not just a random suggestion that "we think this is better...".  

Figure 3 : Again, note the links to more useful information on the subject.

Figure 4 shows the results of an audit on a database (AdventureWorks2000 was my target in this case).   From here I can select the rules I want to 'apply' and by selecting the wizard button SQL Auditor will take me through the steps to correct the issue.  Where it can, SQL Auditor will create the script you need (Figure 5) - you just need to run it in Query Analyzer (I trust you're savvy enough to back up your database first?).

Figure 4

Figure 5

It's really that simple.  If you require - a report of the issues found can be printed out (hence the version of SQL Auditor with Crystal Reports included if you need the runtime).

Support

The product was that intuitive (and stable) that I did not need any support.  I did have a few application related queries which I sent to SSW, and their answers were prompt and complete - plus I've dealt with SSW before and the guys are pretty on the ball.  So if you did have any issues, rest assured you are dealing with a professional outfit.

Main support is via the

SSW Knowledge Base, and also at

http://www.ssw.com.au/SSW/Products/ProdCategory.aspx?CategoryID=8SUPP

Standard Support is a service offered to all new clients (registered product users) which covers the installation of any commercial SSW software. The first three (3) support incidents are free of charge, you just need to include your Rego ID  (Australian for Registration ID) in any support correspondence. SSW offer free upgrades to the current version for 6 months from date of purchase. If you want to upgrade to the current version after 6 months, the upgrade cost is 50% of the products current purchase price.

Conclusions

If your company is doing development with SQL Server then I believe that tools like SQL Auditor should be incorporated into your 'core tools' toolkit - or at the very least you should visit SSW's website and get across the standards they evangelize.  It'll be worth your time.   The profile of SQL Auditor users varies from large corporations (like NASA) to individual developers.

Those of you who are familiar with Microsoft's Best Practice Analyzer (MSBPA) might be asking "Why SQL Auditor?" as they do appear similar on the surface. The impression I get from using both, is that SQL Auditor is aimed at more at the developer, tightening up on a the design & development of SQL databases - whereas MSBPA is aimed more at server administrators and the overall server efficiency. SQL Auditor is a more mature product and uses a different set rules for it's purpose. Personally, I found SQL Auditor a heap more intuitive and usable at the 'database design level', but the reality is that you'd want to run them both and select the rules you need for the relevant environments. These are NOT competing tools.

I found that using SQL Auditor provided a faster turn around in the QA process because it enabled me to check the common rules automatically, and I didn't have to waste time looking at the obvious things first. SQL Auditor also goes further to explain why you'd want to be making the changes it recommends - improving the learning/development process as you go. Therefore I think that if anyone is serious about best practice database design, you'd want to be making the investment.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Product Information

Web Site: http://www.ssw.com.au/ssw/SQLAuditor/

Developer: SSW

Pricing: Single Developer - US$299.00

Enterprise - US$599

Source Code - US$899