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





5.00/5 (5 votes)
Standardization database object name 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.
- Open SSMS select Management > Policy Management> Conditions
- Right click on Conditions node and Select 'New Condition..'
- 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
- From SSMS select Management > Policy Management> Policies
- Right click on Policies and hit on New Policy.. context menu.
- 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.
- Select Stored Procedure Naming Condition from Check condition: drop down.
- 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.
- Select "On Change: prevent" from
Evaluation Mode: drop down list.
- The Enabled:
check box has become Enabled now, put a check mark on it.
At this stage the entire window looks like:
- Press OK to finish.
We are done! Let's test our works now.
- From SSMS open a
new query window and point out the database AdventureWorks2012 or Run following
scripts:
USE AdventureWorks2012 GO
- Let's try to
create a Stored procedure with a sp_
prefix.Figure 6: SP Creation attempt fails with sp_ prefix
- 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.
- 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.