Automating SQL Server Policies Management
Create business-specific configurations on your SQL Servers with PowerShell. Yes, that easy.
- By Joshua Jones
- 11/09/2009
Last time we discussed creating a script that can retrieve configuration settings from a list of SQL Servers and placing that data into a SQL Server table. Now, let's take a look at another technology in SQL Server: Policy-Based Management.
As part of the SQL Server Management Objects (SMO), DBAs have access to the wonderful world of policies. From here, we can assess the condition of our server configurations and create policies to enforce business specific configurations, as well as apply those policies across our entire environment. And fortunately, we can use PowerShell to help us along.
Note: This article isn't an in-depth discussion of Policy Based Management (PBM), but it does touch on aspects related to managing PBM. If you aren't familiar with PBM, check out the SQL Server Books Online entry, as well as some searches on Google, to get up to speed on this cool new feature.
Now, let's look at commands that can help us look at the policies (and related objects).
First, we'll want to see what we've got out on our server. From the SQLPS command line, change to the SQLPolicy directory. You can see a list of the directories available by using the good, old-fashion command-line command DIR.
 |
| Figure 1. Type DIR to see what's on the server. |
What you'll see as a result is Fig. 1, which shows the SQLPolicy folder. So, issue a change directory command (cd SQLPolicy).
 |
| Figure 2. Inside the SQL Policy Folder. |
Now you can see the MachineName. You'll need to cd to that, as well as to the instance you wish to look at.
 |
| Figure 3. Now, looking at the Machine Name. |
Here we are -- the policy folders! Notice that you can explore all of the objects related to policies, including facets (under the ObjectSets folder), conditions, and categories.
Now, do you want to see the policies on the instance? Again, just list the contents.
 |
| Figure 4. List the contents of the policies folder again. |
Well, you can see the policy there, but it's cut off. So let's list the contents a little differently, using the correct cmdlet for the job, Get-ChildItem (in PS, dir is really just an alias for Get-ChildItem). We'll use the –Name parameter to just show us the names.
 |
| Figure 5. Doing the same thing in Fig. 4, only this time using a cmdlet. |
As you can now see in Fig. 5, this is much easier to read. Using this simple navigation you can quickly see what policies have been installed. Remember, if you want to save the list for later use, you could always pipe the output to a text file using the Out-File cmdlet:
Get-ChildItem –Name | Out-File C:\MyPath\MyPolicies.txt
That's it for now. Next time, we'll look at evaluating policies. Until then, have fun!
About the Author
Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.