Keep everyone away from breaking your database object naming convention with SQL Server Policy Based Management.

Introduction

Maintaining proper naming convention for database objects is very crucial for every database project. Think about a situation where 10-15 database developers work together.  If one of them creates a table with a name of Customers_tbl another one Tbl_Department or Salary, that could be quite  cumbersome for future maintenance and extensibility. Another issue commonly comes in case of creating stored procedure with a prefix "sp_". All of you know that creating objects with a prefix of "_sp" means a special objects for SQL Server and SQL Server always try to locate the object in master database first. So it is always recommended that object naming with a prefix of "_sp" should always be avoided. To get rid of this situation, it is very common practice now a days that every team has its own policy to maintain precise database object naming convention. Policy Based Management is one of the most splendid features of SQL server, that makes this task extremely handy for database developer or DBA as well.  

Define the policy first: 

Assume, we need some database object naming policies which will force database users to create the object names by obeying the defined naming policy. 

•  No object name will be start with a prefix sp_. 
• In terms of Table no prefix or suffix will exist with a value of "tbl".
• Must have a suffix _usp (User Defined Stored Procedure) with every Stored procedures name. 
• Must have a _udf suffix with every user defined function. 
• Every view must have a suffix of  _vw. 

In this article we will not implement all of these policies, rather I will show you how to create the following policy using SQL Server Policy Based Management.

"All stored procedures in Database AdventureWorks2012 must not start with 'sp_' and must have a '_usp' end of (suffix) every stored procedure name."  

Implementation  

Creating Conditions to satisfy the Policy

Now, we will  try to figure out the conditions from our declared policy.

• Condition 1: "All stored procedures of Database AdventureWorks2012" indicates that our policy will be applicable only for AdventureWorks2012 database.
• Condition 2: "must not start with 'sp_' and must have a '_usp' end of each stored procedure name."  this part indicates, the stored procedures must not have a prefix value sp_ and must have suffix '_usp'. 

We We have our defined conditions, let's create them now step by step.

1. Open SSMS select Management > Policy Management> Conditions 
2. Right click on Conditions node and Select 'New Condition..' 
3. Create new Condition window will appear  like below: 

Now to create Condition 1. put the  AdventureWorks2012 Database in the Name Field. Select Database from the Facet dropdown list. Finally, in the Expression Grid, put @Name in the Field cell and 'AdventureWorks2012'  in the Value cell. (Do not forget to put single quote (') in the value cell). Now press OK button. Expand the Conditions node and you will find the newly created condition now.  

To create  the second condition repeated step 2 and put the following values in the specific fields of the "Create New condition-" window.

Name= Stored Procedure Naming Condition.

Facet=Stored Procedure.

Under Expression Grid:

AndOr=Empty   Field=@Name   Operator=NOT LIKE        Value='sp_%'

AndOr=AND       Field=@Name   Operator= LIKE               Value='%_usp' 

Hit OK button to create this condition as well. Now we have our 2 conditions ready.

How it works: 

Selecting "Stored Procedure" from Faced indicates the expression  will be applicable for stored procedure only. And what configuration we have made in the expression section is just to comply with the second condition. The expression will be:

@Name(name of the stored procedure)  NOT LIKE 'sp_%'  AND @Name LIKE  '%_usp' 

Creating Policy

1. From SSMS select Management > Policy Management> Policies
2. Right click on Policies and hit on New Policy.. context menu.
3. Enter Stored Procedure Naming Policy on the Name: field. At this moment you will get Enabled: check box in disabled mode just after the Name: field.
4. Select Stored Procedure Naming Condition from Check condition: drop down.
5. From the Against targets: (looks like below) 

Click over the down arrow just before the Database, and select  AdventureWorks2012 Database condition from the context menu.

7. Select  "On Change: prevent" from Evaluation Mode: drop down list.

8. The Enabled: check box has become Enabled now, put a check mark on it. 

At this stage the entire window looks like: 

9. Press OK to finish.  

We are done! Let's test our works now.  

1. From  SSMS open a new query window and point out the database AdventureWorks2012 or Run following scripts: 
   SQL

   USE AdventureWorks2012
   GO

2. Let's try to create a Stored procedure with a sp_ prefix.

   

   Figure 6: SP Creation attempt fails with sp_ prefix 
3. Again try to create the same procedure with a sp_ prefix and _usp suffix. 

   

   Figure 7: SP Creation attempt fails with sp_ prefix and _usp suffix.

   This time our attempt also fails.  

4. Now let's try to create the Stored Procedure by fully honoring the policy such as not giving sp_ prefix and giving _usp suffix, see below: 

   

   Figure 8: SP Creation attempt successful.  

Hurray!!!  the procedure created successfully.

Now let's discuss about some terms and concepts of SQL Server  Policy Based Management. 

What is policy Based Management

Policy Based Management is a method of database administration which provides a management framework in order to automate tasks according to a set of predefined standard or policy. This standardization activities can be imposed across multiple sever.  

Policy-Based Management Scenarios

A very good MSDN article exists regarding this. Follow the link below: 

http://msdn.microsoft.com/en-us/library/bb522466(v=sql.105).aspx 

Policy Based Management Concepts and Terms

A details explanation  can be found in MSDN about this topic as well. Here you go:

http://msdn.microsoft.com/en-us/library/bb510667.aspx 

Conclusion

In term of managing security, complexity, configuration reliability and enforcing strict compliance policies across large distributed environments, undoubtedly, Policy Based Management offers wide range of facilities.