SQL Server Best Practices Analyzer to be Integrated into SQL Server 2008

Posted by Jonathan Allen on Jan 23, 2008 06:38 AM

Community

.NET

Topics

Data Access ,

Code Analysis

Tags

SQL Server 2008 ,

SQL Server 2005

January brought with it a new edition of the SQL Server Best Practices Analyzer. Like previous versions, this tool helps DBAs review their existing databases for things that are not quite right. Unfortunately, it is a reactive tool that can only detect potential problems after they exist. With SQL Server 2008, Microsoft intends to change that.

The Declarative Management Framework will allow DBAs to proactively enforce design rules and constraints. Joe Young talks about some of the features in an October blog post.

So what’s DMF? In a nutshell, it’s an approach to database administration based on policies and not tasks. Many DBAs plan their work around the various tasks they need to perform which may include backing up databases, reviewing event logs, scanning for improper/unauthorized object creations, killing long running query SPIDs, etc… The list goes on for quite a bit (all the way to Christchurch my Kiwi friends say but my OZ buddies assure me there really isn’t much East of Wollongong). Bottom line is, there are a lot of tactical and sometimes reactionary items on the list which can keep the conscientious DBA busy pretty much all year around (not even counting the support calls).

With DMF, you’re suppose to determine what policies, restrictions, behaviours, etc…. the SQL Servers in your organization will enforce, define the policy using Management Studio and select one or more servers to enforce the policy. You then monitor from a central console; SSMS. The classic example (if you paid attention at TechEd or PASS Summit this year), is users are not allowed to create tables in the DBO schema. That’s handy but not particularly interesting. How about making sure all user created stored procedures begin with a USP_ prefix? Disallow the use of SQLMail? No OPENROWSET queries while we’re at it. Notice that you can define policies that have a database or a server scope? Pretty cool huh? Sure, you can change the relevant sp_configure settings for each server and db_options. You can even put all that in a script and run them against all the databases/servers in your org. Well, DMF and SSMS in Katmai makes all that a lot easier. It’s not reinventing the wheel, it’s just putting on some good tires.

Microsoft is allowing for quite a bit of flexibility when it comes to what to do about policy violations. Ravi S.Maniam outlines them,

Policy administrators can run policies on demand, or enable automated policy execution by using one of the following execution modes:

• Changes are attempted, prevent out-of-compliance. This uses DDL triggers to prevent policy violations.
• Changes are attempted, log out-of-compliance. This uses event notification to evaluate a policy when a relevant change occurs.
• On schedule, log out-of-compliance. This uses a SQL Server Agent job to periodically evaluate a policy.

• Older >