Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Review: SQL Auditor

By Mike Pearson,

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.

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 Factor'
Value 5 Great value if you are a company/department doing regular SQL Development
Technical Support 5 Efficient and complete for  my needs - so I have to go with top marks.
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 full marks.
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.

 

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

Total article views: 5785 | Views in the last 30 days: 0
 
Related Articles
FORUM

Refreshing the Reporting database from Production database every 2 hour

Refreshing the Reporting database from Production database every 2 hour

FORUM

Blowing off the SAS70 auditors

Don't you have to be forthcoming with security auditors?

FORUM

Looking to buy new hardware for Production and Reporting databases and set DR.

Setting Production and Reporting Databases and also a DR site.

FORUM

report needed for auditors...

What I need is a report or spreadsheet that I can hand over to auditors that shows every permission ...

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones