By: Aaron Bertrand | Updated: 2011-02-10 | Comments (10) | Related: > Policy Based Management
Problem
In many environments you may want to enforce specific
database naming conventions. For example, you may want to prevent others from
naming their databases using special characters like dash or period. These and
other poor naming decisions can wreak havoc on both internal and custom modules
that (perhaps wrongly) make assumptions about database names.
Solution
While it is not always easy to catch these issues at the time the name is implemented, Policy-Based Management can at least help identify the databases that already don't conform to your rules. A common and sensible convention for databases would likely include the following rules about the name:
- does not include any special characters (dash, period);
- does not start with a number;
- does not have embedded or trailing spaces; and,
- is no longer than 16 characters (let's pretend we're not using SharePoint).
First, let's create some sample databases that will certainly violate these rules, by running the following code:
CREATE DATABASE [1_start_number]; CREATE DATABASE [embedded space]; CREATE DATABASE [embedded.period]; CREATE DATABASE [embedded-dash]; CREATE DATABASE [trailing_space ]; CREATE DATABASE [this_dbname_is_17];
To represent the above rules in T-SQL (and to identify any violations manually), we can simply create the following LIKE construct and query sys.databases:
SELECT name FROM sys.databases WHERE name LIKE N'%[- .]%' -- contains dash, space, or period OR name LIKE N'[0-9]%' -- starts with a number OR LEN(name) > 16; -- is greater than 16 characters
But then we can quickly think of several other characters that can cause problems in different scenarios: pipe, apostrophe, quote, tilde, curly brace, backslash... the list goes on. So, as long as you do not need to support Unicode-only characters in a database name, it is likely going to be easier to identify the characters that you want to allow, rather than identifying the characters you don't want to allow. In my environments, databases can be named with US English alphanumeric characters and underscores, can't begin with a number, and must have 16 or fewer characters. In this case, the above rule can be simplified as follows:
SELECT name FROM sys.databases WHERE name LIKE N'%[^A-Za-z0-9[_]]%' -- has any char besides A-Z, a-z, 0-9, underscore OR name LIKE N'[0-9]%' -- starts with a number OR LEN(name) > 16; -- is greater than 16 characters
(If you need to support Unicode characters in the name, be prepared to manually list out all of the characters you want to allow. Also to support case sensitive collations both upper and lower case A-Z must be listed.)
Create the Condition and Policy
In Policy-Based Management, a policy is supposed to be expressed in terms of the desired state - we want policies to succeed only if the positive conditions are met, otherwise we are dealing with mixed messages if a policy only succeeds because our desired intent was not met. The above query finds all of the databases that are NOT in our desired state (do not conform to our naming convention). To make it so that it successfully finds the databases that DO conform to our naming convention, we need to change LIKE to NOT LIKE, greater than to less than or equal to, and combine the expressions using AND instead of OR - because instead of failing due to meeting ANY of our "bad" conditions, we only want it to succeed if it meets ALL of our "good" conditions. So we can re-write the above query to verify that it does not return any of the poorly-named databases we've created:
SELECT name FROM sys.databases WHERE name NOT LIKE N'%[- .]%' -- does not contain dash, space, or period AND name NOT LIKE N'[0-9]%' -- does not start with a number AND LEN(name) <= 16; -- is less than or equal to 16 characters
When we create the condition and policy, since we want each database to adhere to our naming convention, we should call the condition something like, "Database adheres to naming convention." In Object Explorer, go to Management > Policy Management, right-click Conditions, and select "New Condition..."
In the Create New Condition dialog, enter "Database adheres to naming convention" in the Name: field and change the Facet: to Database. Then we will add three expressions, each representing one of the WHERE clauses from above. (Note that in Conditions, string operators do not support the leading N prefix to denote Unicode strings, nor do they support the ESCAPE sequence for LIKE / NOT LIKE predicates.)
- For expression 1, change Field to "@Name", Operator to "NOT LIKE", and enter the value:
'%[- .]%'
For expression 2, make sure AndOr is "AND", then change Field to "@Name", Operator to "NOT LIKE", and enter the value:
'[0-9]%'
Expression 3 is the trickiest one - you can't use functions like LEN() on the @Name property in a simple expression, and unlike built-in properties like @@ObjectName, there is no property such as @@DatabaseName exposed to PBM. But since the condition will be executed in the context of each database, so if you use ExecuteSql(), you can use LEN() against built-in functions like DB_NAME(). So for this expression, make sure AndOr is "AND", change Operator to "<=", Value to "16", and then click on the [...] button to enter the following expression into the Advanced Edit dialog:
ExecuteSql('Numeric', 'SELECT LEN(DB_NAME())')Click OK. The Create New Condition dialog should now look like this:
To create a policy that evaluates this condition, go back to Object Explorer, and under Management > Policy Management, right-click Policies and choose "New Policy..."
In the Create New Policy dialog, enter "All databases should adhere to naming convention" in the Name: field and change the Check condition: property to the condition you created above. The rest of the properties can remain at their defaults so that we can head straight to testing the policy. The dialog should look like this:
Click OK to save the Policy.
Evaluate the Policy
To evaluate the policy, you can go to Object Explorer, expand Management > Policy Management > Policies, right-click the Policy, and choose Evaluate. That will bring up this dialog, which includes a warning about any policy that includes functions like ExecuteSql():
Click Evaluate. This will bring up another dialog, asking once again to confirm that your scripts are trustworthy. Unless you routinely download policies from random web sites, feel free to check the "Do not show this message again" checkbox:
Click Run. If you have any of the databases above that violate your naming convention, they should appear in the list with a red X indicating failure:
The default layout of the results makes it difficult to visually parse the error conditions and identify which databases violated the policy and why. You can click on the "View..." link in each row, and see exactly which part of the condition caused each of the databases to fail. Here is the result for a database that violated the "starts with a number" clause of the condition:
And here is the result for a database that violated the "exceeds 16 characters" clause of the condition:
As you can see, the detailed view will show exactly which clause(s) of the condition caused the policy evaluation to fail - there may have been more than one issue with the database name, and the Result column will reflect those failures.
Automate the Policy
You can automate the policy by changing its evaluation mode to "On schedule" - this way, you can be alerted hourly, daily, weekly or monthly if anyone has created a poorly-named database in that time period. You can even export the policy and deploy it to multiple servers, as Edwin Sarmiento explains in his tip, Evaluating and Implementing Policies in Multiple Instances in SQL Server 2008. For more advanced automation of policies throughout your Enterprise, you can look into the CodePlex project, Enterprise Policy Management Framework.
Next Steps
- Create a condition that enforces the naming convention you want for the databases in your environment.
- Create a policy that checks that condition to alert you of databases that violate your naming convention.
- Distribute and automate the policy so that it is enforced consistently and so you can avoid evaluating it manually.
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-02-10
