Preventing Undesired Actions Using Policies.

Applies: SQL Server 2008 (Katmai) CTP November 2007

Microsoft SQL Server 2008 introduces Declarative Management Framework, which can be used to prevent changes to the system that don't comply with standards in your organization.

Let's say we would like to implement a coding standard that all tables created from now on for a specific database should begin with the prefix 'tbl'.  Now, let's create a policy to enforce that and let's use AdventureWorks database for testing.

Let's open SQL Server Management Studio, expand Management, then expand Policy Management, right-click Condition, and then click New Condition.

In the Create New Condition dialog box, in the Name box, type DBA - Table Name Prefix.  After typing the name, select the facet Table, then choose the expression @Name like 'tbl%', and click OK
.

Remember that for testing purposes we would like to apply our new policy to the AdventureWorks database.  For that reason, we need to create a new condition specifying just the AdventureWorks database, and let's name it 'DBA - AdventureWorksDB'.

We can now create our policy.  In Object Explorer, right-click Policies, and then click New Policy.
 

In the Create New Policy dialog box, in the Name box, type DBA - Table Name Prefix Policy.  Then select the condition 'DBA - Table Name Prefix'.





Specify as target every table and select the 'DBA - AdventureWorksDB' condition for database target.






The last step for creating our policy is specifying 'On Change - Prevent' as execution mode, since we want the policy to prevent the creation of every table with a prefix different than 'tbl'.  After that, please click OK.










Let's test our policy, creating a new table that does not comply with the standard expressed in the policy.




 

Let's create a column for the table and quickly try to give a table name like 'TestTable', which does not have the 'tbl' prefix.



 

Here is when the policy comes to action, and prevent the creation of the table since it does not comply with the policy.






 

As you can see, this should bring peace of mind to many DBAs around the world, including me ... Welcome SQL Server 2008!

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.