Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 20. Defining Policies

Policy-Based Management is a new feature in SQL Server 2008 that helps with SQL Server administration. It enables database administrators to manage SQL Server instances by intent through clearly defined policies, thereby reducing the potential for administrative errors. Policies can be applied against a group of servers, thus improving the scalability of monitoring and administration.

In this chapter, we will use the same class to create two exemplary policies and install them on the SQL Servers in our environment. These two policies actually implement the standard that was defined in Chapter 14. One of the policies we will discuss enforces a stored procedure naming convention. The other policy ensures that both the Auto_Shrink and the Auto_Close options are set to off. This chapter goes on to discuss the value of both of these policies.

Stored Procedure Naming Convention Policy

Chapter 12 explained how to define policies using the SMO class Microsoft.SqlServer.Management.Dmf. We created a condition called "No server access" and then we defined a policy called Domain guest cannot access server, whereby we applied the condition to prevent the domain guest login POWERDOMAIN\Guest from accessing SQL Server. We are going to use the same class to define policies and conditions in this chapter.

In Chapter 14, one of the SQL Server standards that we defined involves the stored procedure naming conventions. Remember that stored procedure names should describe the ...