It doesn’t seem like it was that long ago that my company’s IT department was bracing for a major new line of work. Back in the mid 1990s, we were going full steam into client-server technology. At the same time, we were significantly expanding our workforce. The IT department that had spent years as an old-style mainframe shop, was suddenly inundated with requests for new workstations, network user IDs, new network domains, permission requests, and requests for application access privileges. Our lone mainframe permissions person quickly felt overwhelmed and a little baffled by all of these new privileges and provisioning needs. Within a year or two of our first client-server application, we went from one to three staffers working full-time granting access to the various applications and network resources within our environment.
Evidently, we weren’t the only big enterprise dealing with the pain of rapidly growing networks and the burden of keeping privileges organized for everyone within the organization. It was not long after we first complained about the situation to our Microsoft Technical Account Manager (TAM) that a new tool came out–Group Policy Management. With group policy management, we could simply create abstract groups of users, such as “Financial App Users” or “Field Domain Users,” and assign any new users to the appropriate groups. There was no need to manually go through all users working on an application to institute a change in permissions.
As it turns out, an analogous situation has occurred in the SQL Server world. Many large organizations have seen years of unchecked SQL Server growth within their businesses. I’ve talked with lots of enterprise IT shops that, upon doing an IT audit of all of their SQL Server assets, were surprised to find that they had over 1,000! When the time and energy that was needed to independently administrate all of these servers was calculated, most organizations realized they would save money managing them in groups rather than as individuals.
For these companies, help is on the way! SQL Server 2008 has a new feature called Policy-Based Management (PBM). PBM is a way to set behavioral policies for groups of servers and then manage the SQL Server environment through compliance with policies rather than on a task-by-task basis on individual servers. The net result is much like managing users with group policy–you can administer a lot more resources with fewer people.
One of the things that is really great about PBM is that policies, once written, can be widely shared. Visit the PBM blog at http://blogs.msdn.com/sqlpbm/default.aspx to find a number of sophisticated SQL Server “health” policies. Google “Microsoft SQL Server 2008 Feature Pack, August 2008” to find a nice set of PBM policies that implement a number of administrative best practices for SQL Server, as well as ways to avoid common pitfalls.
The PBM blog, written by the Microsoft SQL Server team for database manageability, also has lots of good advice and tips on how to get the most out of PBM. For example, there are examples and articles on how to use PBM on SQL Server 2005 instances, as well as with PowerShell and other automation techniques.
Implementing PBM in your environment will probably require more than just a few superficial changes. I strongly believe that PBM requires DBAs to change not only their daily work style, but also the way they think about getting work done with SQL Server. As a long-time DBA, I tend to think of administration on SQL Server as a series of discreet tasks. Now, with PBM, I am moving toward a “compliance” thought process and learning to treat SQL Server instances that are in or out of compliance. It is a bit awkward at first. But I’m convinced that the savings in time and energy maintaining the SQL Server IT infrastructure will be well worth it.