Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Enforce SQL Server Database Naming Conventions Using Policy Based Management

MSSQLTips author Aaron Bertrand By:   |   Read Comments (9)   |   Related Tips: More > 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..."

using policy-based management to identify databases that don't conform to your rules about name

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:

    the create new condition dialog
Click OK to save the condition.

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..."

go to object explorer under 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:

all databases should adhere to naming convention

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():

evaluate the policy

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:

policy evaluation security notice

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:

any databases that violate the naming convention will appear

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:

click on the view link to see which databases violated the policy and why

And here is the result for a database that violated the "exceeds 16 characters" clause of the condition:

you can automate the policy by changinging its evaulation mode to on schedule

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


Last Update: 2/10/2011


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, February 11, 2011 - 3:19:11 AM - David McKinney Read The Tip

I'm trying - with mixed results - to implement PBM for a variety of development standards.  For example, it is a standard where I work that all accesses outside of the current database be done via synonyms.  Another policy I've set up is that there are no 3 part names with the schema missing (such as MyDB..MyTable.  Another is that stored procedure calls should not be caller dependant i.e. the schema name is always specified.  So you can implement some fairly complex standards with a little imagination.  But the first standard that we wanted to implement is that there should be no 'Select *' in our code (except perhaps in '... exists (select * FROM....').  This at first glance simple requirement has proved to be elusive to the point that I'm going to wait for someone / some vendor to tackle this for me.

In general, what surprises me most is that apparently this tool has been targeted exclusively at the DBA, with no consideration of development standards - and as a result I'm apparently alone in trying to enforce development standards in this way.

So thanks for letting me know that I'm not totally on my own!


Friday, February 11, 2011 - 8:31:54 AM - Aaron Bertrand Read The Tip

Actually, there was an article about preventing SELECT * over on SQLServerCentral this morning:

http://www.sqlservercentral.com/articles/SELECT+*/68324/

Not sure I like the implementation or the side effects, but it's one possibility.  If you want to avoid the changes to schema you could enforce access to tables through views and add an inaccessible but no-op column there.

As for parsing for SELECT * and having to allow it within EXISTS, why not establish a policy of using SELECT 1 or SELECT NULL inside of EXISTS?

Anyway, just some thoughts.


Friday, February 11, 2011 - 9:24:05 AM - David McKinney Read The Tip

 

 

The article is a neat idea, but we could never implement it.  However in the comments related to the article, i found a link to an article by Thomas LaRock which points out a column selall in sys.sysdepends, and which leads to is_select_all in sys.sql_dependencies.  This pretty much meets my requirements.  I'm gobsmacked!  What a strange coincidence!

As for "exists (select 1...", I agree with you.  I've never come across "exists (select null". Does that do something different?

Anyway, thanks for pointing me in the right direction!

http://thomaslarock.com/2010/11/why-you-cant-trust-sysdepends/#axzz1DewrUDcd

 

SELECT

 

so.name, schema_name(so.schema_id) FROM sys.sql_dependencies sd

INNER

 

JOIN sys.objects so on sd.object_Id=so.object_id

where

 

is_select_all=1


Thursday, March 15, 2012 - 1:19:44 PM - Matthew Darwin Read The Tip

Apologies for digging up a fairly old article, but currently this is the most pertinent thing close to what I'm looking at.

I've been trying to set up a policy to prevent creating tables in a similar fashion to the above policy for database names, however, I'm very much struggling to make it work when it comes to apostrophes.

The condition I've been using is identical to the second condition highlighted above - "@name NOT LIKE '%[^A-Za-z0-9[_]]%'.

The policy works perfectly if I try and run the following statement:-

CREATE TABLE dbo.[Break Policy]
(ID int identity(1,1())

However, if I add an apostrophe to the create table statement it will allow the creation statement to complete without a hitch:-

CREATE TABLE dbo.[Break' Policy]
(ID int identity(1,1())

Any idea how to make this work, or should I just be going back to DDL triggers?


Thursday, March 15, 2012 - 1:34:18 PM - Aaron Bertrand Read The Tip

Hi Matthew, I'm not sure why the apostrophe would be slipping through the existing conditions, but you could try adding an additional condition, e.g.

@Name NOT LIKE '%''%';

Thursday, March 15, 2012 - 3:07:57 PM - Matthew Darwin Read The Tip

I've had a play with that and it still seems to go through. I'm wondering if it's do with escape characters and the way the expressions are created in pbm, and perhaps the way in which the actual trigger is then built to prevent the table creation; possibly some kind of dynamic SQL nightmare? Though if that were the case you'd think it would fall over completely rather than just let it through.

I'll have a good play tomorrow and if I get it working will let you know.

Thanks!


Wednesday, October 17, 2012 - 7:26:25 AM - Ned Read The Tip

For the third expression LEN(@NAME) in the field works fine rather than the ***bersome ExecuteSQL statement.

 

Cheers!


Wednesday, October 17, 2012 - 9:14:53 AM - Aaron Bertrand Read The Tip

@Ned that's probably true, but I wanted to demonstrate the ExecuteSQL() function regardless.


Thursday, October 24, 2013 - 2:07:08 PM - Shirley Read The Tip

How do you run this policy for only new databases? I tried the batching with the Or logic and I get an email everyday that it fails. However, when I evaluate it, it is successful.

 

I have the above in the condition,  batched with:

Create Date>10/1/13

then the OR statement create date<10/01/13.

Any assistance appreciated.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.