What steps do you go through to validate SQL Server configurations after a server build? Even a single server can take some time and is also prone to human error. Have you ever needed to validate a configuration setting across all of the SQL Servers in your environment? If your environment is big enough, doing this manually isn’t even a realistic option. Policy Based Management excels at these things. Introduced in SQL Server 2008, it can definitely help DBA’s manage their ever growing environments.
Every time I use Policy Based Management (PBM) I say to myself, “Self, you have GOT to do more of this”! Put it together with Central Management Server and you have a great platform for ensuring that your database standards are being followed across all of your SQL Servers.
This blog post will give a very high level overview of the PBM components in an effort to spark some interest in a less heralded, but very useful feature.
SQL Server ships with a standard set of policies, but they aren’t installed for you by default. To import the standard policies, right click on the Policy node in SSMS and import them. The default policies are stored in the following location on my SQL 2012 install.
C:\Program Files\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033
The path should be similar no matter what version of SQL Server you are using. The rest of this blog post I hope to explain how all of the pieces fit together. It’s not as complicated as it may look.
The example I am using is the “Backup and Data File Location” policy. It checks to ensure that the default backup and data file locations are configured to use different drives.
The objects and property values that you can test for are known as facets. The facets you have to work with come with every SQL Server installation (2008 and above). Each new version of SQL Server adds new facets for new features, and can also add to existing facets. PBM was introduced in SQL 2008, but you can use it against SQL 2000 and 2005 as well.
In the Backup and Data File Location policy there is no server restriction.
And Here are the results
Yeah, I know. My data and backup files are on the same location. Definitely not a best practice and the policy check caught me. Luckily it’s just on my laptop where I do most of my demos and blog posts, and I don’t have multiple drive letters available to me so I knew this would be the result. If this were one of my production SQL Servers I would definitely be taking some action to remedy this.
So there you have it. I successfully ran a policy check against my local server instance. This is a very basic example of what you can do with Policy Based Management. If you have not played with PBM I recommend trying it out. There is a lot more to it than I have described here, but I am hoping this post sparked your interest into giving it a try. It’s a really cool and powerful feature to ensure your databases are configured the way you want them to be.