Tool to enforce or audit SQL Server standards

We are putting together a set of standards for our database. I am worried that down the road people will forget the standards or new developers will come online and not bother to use them.

I am wondering if there is a tool to audit standards and provide a report based 开发者_JS百科on the standards. I would like it to include things like naming conventions for columns to not having GUIDS as the primary key.

Apex SQL used to have a tool like this called Enforce. But they discontinued it. Is there any such tool still on the market?

You can do a lot of things like this with Policy-Based Management. For example, here are a few tips I wrote for mssqltips that describe how to do a couple of things:

• Enforce database naming conventions
• Identify SQL Servers with inefficient power plans
• Find unused indexes
• Find all columns of a specific data type

Some various tips by other authors as well:

• SQL Server Policy Based Management Tips

The sky's the limit, really. Anything you can run a SQL query to get a scalar result (and several other things as well), you can check with PBM.

For object-level stuff, you can get a good part of the way there using simple DDL triggers. For these you can simply hook onto DDL events (e.g. CREATE TABLE) and roll back if your naming conventions or other criteria are not being upheld. They work very similar to a DML trigger for modifying data in a table.

Just keep in mind that you can't always enforce everything, for example you can't rollback things that aren't "transactionable" (such as CREATE DATABASE) using either PBM or DDL triggers. And be careful where you put your "on change prevent" type of enforcement - for example rolling back a CREATE INDEX that took 12 hours isn't going to go over very well if it was rolled back only because it wasn't named correctly.

SSW of Australia also has a really nice tool for this called SQL Auditor.

They check SQL Server databases against a whole slew of "best practice" rules and give you a report on how you do according to their ruleset.