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
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.
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
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.
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?).
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).
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
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.
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.
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.
|Ease of Use||5||Very intuitive - hardly need the (very good) help files|
|Feature Set||5||The tips and explanations give SQLAuditor that extra bit of 'Wow|
|Value||5||Great value if you are a company/department doing regular SQL|
|Technical Support||5||Efficient and complete for my needs - so I have to go with top|
|Lack of Bugs||5||I had no errors or problems - so full marks again.|
|Documentation||5||Complete and adequate. I couldn't think of anything missing - so|
|Performance||4||Big database schemas could take a couple of minutes - so schedule these audits around|
coffee breaks 😉
|Installation||5||Very easy and straight forward|
|Learning Curve||5||If you are an experienced DBA, then SQL Auditor won't teach you much|
- but you'll appreciate it's value. For the novice and the front-ender the learning curve that SQL Auditor provides should be very beneficial
to your career 🙂
|Overall||4.5||A great product - well done to SSW.|
Web Site: http://www.ssw.com.au/ssw/SQLAuditor/
Pricing: Single Developer - US$299.00
Enterprise - US$599
Source Code - US$899