Standardize your SQL Servers using Policies

Seems like in the past few months, I have been getting non-stop requests to build new SQL 2008 R2 servers. All of a sudden, everyone is in the upgrade and migrate frame of mind. This is a good thing, but when it comes to configuring the servers after the builds, it gets a little redundant and tiresome. Some people like doing the same things over and over every day, but I am definitely not one of them. I need to automate things and make it easier on myself so I can do more things like write blogs and read sportstech articles. There are a few great webinars from Jorge Segarra and myself, where we discuss how to create a Policy to validate a server’s configuration is consistent across your environment. 

So you have a server in your environment that is configured exactly the way you envisioned it, it is the “perfect” SQL build. Now you build a new server and you want to validate it is configured exactly the same as your “perfect” server.

Well, this is why Policy Based Management is so awesome. Let me show you why. You can create a policy based on the configuration of your server simply by right clicking on the instance in SQL Server Management Studio (SSMS) and clicking Facets.

This will open up a window and will allow you to view 11 different Facets and all of the properties for them. These facets include: Server, Server Audit, Server Configuration, Server Information, Server Protocol Settings, Server Security, Server Selection, Server Settings and Surface Area Configuration. Once a facet is selected you can update any settings that are not ‘greyed’ out. For instance, the backup directory and the file locations for both log and data can be changed.

At this point, you can click the button at the bottom that says ‘Export Current State as Policy…’. This will prompt you to name the policy and save it locally or as an xml file. Keep in mind that each facet will be its own policy, so you will have to perform this export once for each facet in the drop-down above. If you do create more than one policy, it is a good idea to add them all to the same Category or create a new category so they can be organized in a way that is easy to recognize, not only for yourself, but for others as well. Be sure to give the policy a name that is specific to what it is. In this case, I call the policy STD_Server_Build-[facet_date].
Once you save the policy, you can open up Policy Based Management in SSMS>Management>Policy Management>Policies and locate the policy you just created. It is a good idea to review the policy you just created and clean up any facet properties that may not apply. Everyone’s environment is different and all the facet properties will have to be examined to determine if it makes sense to keep them in the policy or not. You can change, delete or add new expressions to the policy condition at any time. For instance, the computer physical name is going to be different on all your servers, so that is one property you will need to remove from the condition.

Once the policy is cleaned up, you can right click on the policy and evaluate it against your new server build. The policy will return a result set that you can review and take action on. 

By using Policies, you will be able to see exactly what needs to be modified on the SQL Server. This will save you a ton of time by pin-pointing the exact issues quickly. What are you waiting for? Start using Policies to manage your environment.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s