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 (especially in the virtual environment). 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
sports tech articles. I recalled a great webinar from Jorge Segarra, where he discusses how to create a Policy to validate a server’s configuration is standard across the environment.
So you have a server in your environment that is configured exactly the way you envisioned it, it is the perfect SQL Server template. Now you build a new server and you want to validate it is configured exactly the same as your model 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 Object Explorer in 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 dropdown above.
Once you save the policy, you can open up Policy Based Management in SSMS 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.
By using Policies, you will able to see exactly what needs to be updated. This will save you a ton of time by pin-pointing the exact issues. Take the time to explore Policy Based management and learn about all the great things it has to offer!