Powershell and Policy Based Management

If you haven’t looked at Policy Based Management (PBM), and you manage multiple instances, you ought to really look at it. Even if you manage just a few, having your systems automated just saves you a lot of time and effort.

Allen White is the Powershell guy for me, and he’s showing how you can automate the PBM setup with Powershell. Powershell is another tool that every administrator probably should spend some time working with since it just allows you to repeat the work you need to do with minimal effort.

This is a good session that shows the basics of what PBM is and how it is structured. What’s a policy, a facet, a condition. As an example, Allen shows how to make sure that your database (facet) has autoshrink = False (condition) with a policy that checks this. Loading these types of policies into all your instances can prevent any strange issues that might come about from having differing setups.

A smart DBA will probably have whole sets of these policies, perhaps even different groups for development and production, that are applied to ensure that the knobs are tweaked to your particular environment.

Powershell allows you to script actions. For things you do once, there’s no benefit, but I’ve learned in IT that if you do something once, likely you’ll have to do it again. So having the ability to script things that you might repeat is useful.

Allen shows how to load assemblies in Powershell, connect to SQL Server, and then access objects properties and methods. Most of what I see done here is done in many Powershell sessions. It’s basic programming, but so often people don’t understand basic programming if they are sysadmins. Developers ought to pick this stuff up instantly since it’s the kind of thing they do often. It’s just interpreted, not compiled.

One nice tip from Allen, if you use single quotes, then everything is literally. If you use double quotes, you can have variable substitution.

Building policies with Powershell helps you understand the SMO objects, and it you have a fairly repeatable process. These scripts are something you can easily carry to the next environment, which isn’t as easy in the GUI.

All kinds of things Allen shows, reading the registry, deploying to multiple instances consistently, and more, is easier in Powershell. It’s confusing when you see so many variables, but if you play with Powershell a bit, you’ll find that it’s a great way to manage your systems.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.