SQL Server Best Practices Analyzer

ToolKit

LANGUAGES: Any

ASP.NET VERSIONS:1.0 | 1.1

SQLServer Best Practices Analyzer

OptimizeYour Database for Performance and Efficiency

By KenMcNamee

Irecently wrote about FxCop,a tool used to analyze .NET assemblies and provide advice on improving theirperformance and security (among other things). In this article I'll introduceanother tool that provides a similar service, except it targets SQL Server 2000databases instead of .NET assemblies. The SQL Server Best Practices Analyzer(SQLBPA) is a very powerful - and free - tool from Microsoft that contains arepository of best practices rules that can be easily configured to check oneor more databases for compliance to these rules. In addition, SQLBPA providescommendable reporting and filtering capabilities, so you can quickly findpotential issues with your database.

Using SQLBPA

Thefirst thing that might strike you after installing SQLBPA is the realizationthat it actually requires its own SQL Server database to store settings and doits job. This is a marked difference from FxCop, which uses the concept oflocally saved project files and doesn't require a database. However, the SQLBPAdatabase should be seen as one of its strengths, because, being a database, ithas built-in capabilities for the storage, exporting, and reporting ofcompliance data. In fact, SQLBPA even provides some pre-canned reports that canbe easily integrated into SQL Server Reporting Services.

UnlikeFxCop, SQLBPA requires a couple of steps before the analysis can begin. First,you must set up one or more SQL Server instances to which SQLBPA will connect.Second, there is the concept of a Best Practice Group, which is simply the nameof the SQL Server instance(s) and the collection of rules against which youwould like to check compliance. The Best Practice Group screen allows you tocreate new groups and bind them to a SQL Server instance for the analysisengine to execute. The SQLBPA is flexible enough so that you can set up asimple analysis of a local database, or a highly complex analysis of multipledatabases on multiple servers, each with its own collection of rules to check.

Compliance Reporting

Theanalysis begins once you've selected the best practice group(s) to execute. Beaware that this can take several minutes for even a small, local database.Obviously this will take much longer for a more complex analysis, so you maywant to set up your best practice groups to run on a schedule during a downperiod such as the middle of the night. To make this easier, SQLBPA comes witha command-line tool that can be executed from a scheduler. The only drawback tothis is that - at this time - the command-line tool cannot use SQL authentication;it must use Windows authentication.

SQLBPAhas decent reporting and filtering capabilities from within the client. Oncethe analysis has finished, you'll be presented with a screen similar to thatshown in Figure 1, which is clean and easy to navigate. The first step Inormally take is to filter only on the items that fall under non or partialcompliance. From here I can view details about the rule that was violated, andabout the specific portion of the database that caused the violation, as youcan see in Figure 2. The information given is very good, making SQLBPA as mucha learning tool as a problem detector.

Figure 1: The SQLBPA Compliance Reportscreen provides a clean and easy-to-navigate user interface to view the resultsof a rules compliance scan.

Figure 2: The SQLBPA Scan Details dialog boxprovides helpful information about the specific portion of the database thatcaused a rule compliance violation.

Prepare for the Future

SQLServer 2005 is going to be a major upgrade to the SQL Server family, and that'sprobably an understatement. There will be many new features and changes to theold features that most ASP.NET developers are probably not going to have thetime to completely digest and understand. Therefore, one very useful SQLBPAfeature is the ability to check a database to see whether it's ready for theupgrade to SQL Server 2005. Some issues that it checks for are the use ofdeprecated DBCC commands, obsolete stored procedures, and obsolete systemtables.

Conclusion

I thinkit's fair to assume that most ASP.NET developers - myself included - areprobably not fully qualified SQL Server DBAs. Therefore, a tool like SQLBPA cango a long way toward assisting us in illuminating and fixing potential problemswith our databases, before they become real headaches several months intodevelopment (or even into production). However, SQLBPA's advice shouldn't betaken as gospel. Using a best practice recommendation is not always the bestcourse of action. A best practice is merely an approach that works best formost people in most situations. You may find your particular developmentproject requires an unorthodox approach that violates a SQLBPA rule. This iswhere good old human decision making comes in. That being said, SQLBPA is an incrediblyuseful tool, and should make a valuable addition to every SQL Serverdeveloper's toolkit.

Resources

KenMcNamee is aSenior Software Developer with Vertigo Software, Inc., a leading provider ofsoftware development and consulting services on the Microsoft platform. Priorto this, he led a team of developers in re-architecting the Home ShoppingNetwork's e-commerce site, http://www.HSN.com,to 100% ASP.NET with C#. Readers can contact him at [email protected].