In many environments, it is important to enforce specific database naming conventions. Poor naming decisions can cause issues with internal and custom modules that make assumptions about database names. In this article, we will explore how to use Policy-Based Management in SQL Server to identify databases that do not conform to your naming rules.

Defining the Naming Convention

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
• Is no longer than 16 characters

To represent these rules in T-SQL, we can use the LIKE construct and query the sys.databases table:

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

However, this approach can become complex when dealing with various characters that can cause problems. Instead, it is easier to identify the characters that you want to allow. For example, if databases can be named with US English alphanumeric characters and underscores, can’t begin with a number, and must have 16 or fewer characters, the 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

Creating the Condition and Policy

In Policy-Based Management, a policy is expressed in terms of the desired state. To create a policy that evaluates the naming convention, we need to change the conditions to verify that databases do conform to the naming convention. We can use the NOT LIKE operator, less than or equal to, and combine the expressions using AND instead of OR.

To create the condition and policy, follow these steps:

1. In Object Explorer, go to Management > Policy Management, right-click Conditions, and select “New Condition…”
2. In the Create New Condition dialog, enter “Database adheres to naming convention” in the Name field and change the Facet to Database.
3. Add three expressions, each representing one of the WHERE clauses from above.
4. For expression 1, change Field to “@Name”, Operator to “NOT LIKE”, and enter the value: ‘%[- .]%’
5. For expression 2, make sure AndOr is “AND”, then change Field to “@Name”, Operator to “NOT LIKE”, and enter the value: ‘[0-9]%’
6. For expression 3, change Operator to “<=”, Value to “16”, and then enter the following expression into the Advanced Edit dialog: ExecuteSql(‘Numeric’, ‘SELECT LEN(DB_NAME())’)
7. Click OK to save the condition.
8. To create a policy that evaluates this condition, right-click Policies under Management > Policy Management, and choose “New Policy…”
9. In the Create New Policy dialog, enter “All databases should adhere to naming convention” in the Name field and select the condition you created above as the Check condition.
10. Click OK to save the policy.

Evaluating and Automating the Policy

To evaluate the policy, go to Object Explorer, expand Management > Policy Management > Policies, right-click the policy, and choose Evaluate. This will show a list of databases that violate the naming convention.

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. You can also export the policy and deploy it to multiple servers for consistent enforcement.

By using Policy-Based Management in SQL Server, you can easily enforce database naming conventions and ensure that all databases adhere to your rules.