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

Best Practices are always the Best

By Dinesh Asanka,

I personally believe that no matter how much you are knowledgeable in the technical arena, if you are not adopting best practices you will be lost in one day. We as IT professionals best practices have become order of the day. To achieve this, there are many tools around us which will reduce the inconsistency. Today I will discuss about the practices tool for SQL Server professionals. That tool is SQL Best Practices Analyzer.

Introduction

Shown above is the best practice Analyzer icon and the About information dialog, which prevents me from having to write 5 lines of specs.

What it Does ?

In simple terms what it does is give you the warnings for the best practices that can be adopted if you are not following them. It has divided the best practices into 10 categories, shown below in the table. For each category there are the specific best practices or rules which you can select to analyze.

Group Best Practices (Rules)
Backup & Recovery Database Backups

Failed Backup Events

Master and MSDB Backup

NO_LOG log backups

Recovery Model Usage

Reuse of back file

Configuration options Affinity Mask

Allow Updates Enabled

Fiber Mode

Max Server Memory

Priority Boost Enabled

Recovery Interval

Set Working set size disabled

Database Design Tables without Primary Keys or Unique Constraints

User Object Naming

Database Administration Auto Creation of Statistics

Database Compatibility Level

Database Disk Space

Database File Compression

Database File Placement

Database SQL Options

Index Fragmentation

Log file Growth

Virtual File Growth

Deprecation CREATE DATABASE with FOR LOAD

Defaults and Rules

Deprecated Builtin Functions

Non-Ansi Outer Joins

SETUSER usage

String = Expression Aliasing

Use of sp_dboption

Full-Text Duplicate Full-Text predicates

Full-Text Change Tracking Enabled

Full-Text Background Services Optimized

Full Text BLOB Extension Type

Full Text Catalog Count

Full Text Catalog Placement

Full Text Property Store Size

MSSearch Service Account

Timestamp Column for Full-Text

General Administration AVs and Severe Errors

Error Log Size

NULL @@servername

'tempdb' Current Size

Unexpected Shutdowns

User Objects in Master

Generic Object Prefixes

Object Suffixes

T-SQL Cursor FOR UPDATE column list

Cursor Usage

Explicit Index Creation

INSERT Column List

Nested Triggers Configuration

NOCOUNT Option in Triggers

NULL Comparisons

Results in Triggers

Scoping of Transactions

SELECT *

SET Options

Temp Table Usage

TOP without ORDER BY

Use of Schema Qualified Tables/Views

SQL Server 2005 readiness Invalid User Tables

Obsolete DBCC Commands

Obsolete sp_configure Commands

Obsolete Stored Procedures

Obsolete System Tables

ORDER BY with constants

'Sys' User Schema

WITH Hint Specification

Installation

You can download this free tool from Microsoft. It will create a database called sqlbpa in your SQL Server while installing the tool. This database will be used to save the configurations of your best practices.

Creating a Best Practice Group

Above is the screen in which you create a best practices (BP) group. You can create BP for the all the servers which are registered on your PC. For one BP group, many rules can be selected from one or more categories. Just clicking the rule will take you to the description relevant to that rule.

Executing the Group

You can execute one or more groups at once. After creating BP groups you will be taken to the list of BP groups. You have an option of filtering them according to your requirements. Then select the groups that you want to execute by double clicking on them.

After selecting the BP groups Scan SQL Server Instances will allow you to execute the groups by means of a wizard and will give you an output something like this.

I don't think you will need an explanation after this. It is so simple.

Conclusion

Definitely this is a simple and handy tool to have in your toolkit. However as always the case, use this tool as a slave but not as a master. Any warnings should be taken into consideration before jumping into change them.

I didn't go into deep to discuss each an every rule which is available in the tool. Nevertheless, If you need me to discuss some more about some specific rule drop me an email to dineshasanka@ieee.org .

Total article views: 12566 | Views in the last 30 days: 10
 
Related Articles
FORUM

Database backups best practice

Database backups best practice

FORUM

Give users access to databases depending on which Active Directory group they are a member of.

Give users access to databases depending on which Active Directory group they are a member of.

FORUM

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

Database Design: Should we have Multiple Data files and Multiple File Groups – SQL Server 2005

ARTICLE

SQLServerCentral Webinar #25: Best Practices in Database Deployment (Part 1)

SQL Server Central Webinar #25: Best Practices in Database Deployment (Part 1)

BLOG

Agile Practices Meet Database Development: Intro

I think the database developer role is on the rise and that it's better suited for agile practices -...

Tags
configuring    
sql server 7    
 
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