New book: Microsoft SQL Server 2012 Pocket Consultant

663763.inddHere it is! The Microsoft SQL Server 2012 Pocket Consultant (ISBN 9780735663763; 592 pages) is a concise and easy-to-use reference from William R. Stanek. As so many of you know, William is just the best at delivering what you need to know without all the frills. If you are a SQL Server administrator, this book will help you save time, minimize system downtime, and get the job done!

Below is an excerpt from Chapter 3: "Implementing Policy-Based Management."


Implementing Policy-Based

  • Introducing Policy-Based Management 77
  • Working with Policy-Based Management 80
  • Configuring Central Management Servers 88
  • Managing Policies Throughout the Enterprise 92

Policy-Based Management is an extensible and scalable configuration ­framework that you can use to manage servers, databases, and other objects in your data environments. As an administrator, you need to be very familiar with how Policy-Based Management technology works, and that’s exactly what this chapter is about. If you haven’t worked with Policy-Based Management ­technology before, one thing you’ll notice immediately is that the technology is fairly advanced and has many features. To help you manage this complex ­technology, I’ll start with an overview of Policy-Based Management and then explore its components.

Introducing Policy-Based Management

Just about every administrative task you perform can be affected by the ­policy-based framework in some way. The policy-based framework provides the ability to define policies that apply to server instances, databases, and other objects in your data environments. You use these policies to help you ­control and manage the configuration of data services throughout the ­enterprise. Through ­intelligent monitoring and proactive responses, you can prevent changes that deviate from the configurations you specify and want. You also can scale ­management across multiple servers, which makes enforcing consistent ­configuration policies easier.

Within the policy-based framework, you use the following objects to configure policy management:

  • Facet   Defines a management area within the policy-based framework. Each management area has a fixed set of related properties that you can configure. For example, the Backup Device facet has the following ­properties: BackupDeviceType, Name, PhysicalLocation, and SkipTapeLabel.
  • Condition   Defines the permitted states for one or more properties of a single facet. For example, you can create a condition called Limit Backup Devices to specify that for the Backup Device facet, BackupDeviceType can be set to hard disk or tape and SkipTapeLabel should always be set to True.
  • Policy  Contains a single condition that you want to enforce. For example, you can create a policy named Standard Backup Device Policy that assigns the Limit Backup Devices condition.
  • Category  Contains one or more policies that you want to enforce ­together. For example, you can create a category named Standard DB Policies that contains all the standard policies that you want to enforce within your ­Microsoft SQL Server databases.
  • Target   Defines the servers, databases, or other database objects to which policies are applied. For example, a target set could include all the databases on an instance of SQL Server.

MORE INFO   Put another way, policy-based management is explicit declarative management that you configure using facets, conditions, categories, and targets. Facets are fixed lists of things you can set policy on. Conditions determine when policy applies. Categories group policies together for enforcement. Targets determine to which objects policies apply.

You can create and manage policies in several ways. In SQL Server Management Studio, you can create policies from scratch or import existing policy files. The policy creation process includes the following steps:

1. Select a facet that contains the properties you want to configure.

2. Define a condition that specifies the permitted states of the facet.

3. Define a policy that contains the condition and sets one of the evaluation modes listed in Table 3-1.

4. Determine whether an instance of SQL Server is in compliance with the policy, and then take appropriate action.

For scripting, the Microsoft.SqlServer.Management.Dmf namespace contains classes that represent policy-based management objects. You use the root of this namespace, the PolicyStore class, to work with policies. Consider the following example:

$comp = get-content c:\data\servers.txt
$cn = New-Object Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection

$ps = new-object Microsoft.SQLServer.Management.DMF.PolicyStore($cn)

foreach ($c in $comp) { foreach ($p in $ps.Policies) {
#Invoke-PolicyEvaluation }

Here, you get a list of servers that you want to work with from a text file and then configure a connection to SQL Server. Once you’re connected to SQL Server, you access the policy store and work with the policies on each server in your list.

TABLE 3-1    Evaluation Modes for Policy-Based Management

Policy Evaluation Mode


Execution Type

On Demand

Evaluates the policy only when you ­directly execute the policy. Also ­referred to as ad hoc policy evaluation.


On Change: Log Only

Evaluates a policy when a relevant change is made and logs policy ­violations in the event logs.


On Change: Prevent

When nested triggers are enabled, uses data definition language (DDL) triggers to prevent policy violations by ­detecting changes that violate a policy and rolling them back.


On Schedule

Uses SQL Server Agent jobs to ­evaluate policies periodically. Logs policy ­violations in the event logs and ­generates a report.


NOTE All facets support the On Demand and On Schedule modes. Facets support the On Change: Log Only mode only if the change of the facet state can be captured by related system events. Facets support the On Change: Prevent mode only if there is transactional support for the DDL statements that change the facet state. Only automatic policies can be enabled or disabled.

Policy categories apply to databases and servers. At the database level, ­database owners can subscribe a database to a set of policy categories, and those policies govern the database. By default, all databases implicitly subscribe to the default policy category. At the server level, you can apply policy categories to all databases.

You can mark categories as Active or Inactive at the server or database level. Although you can classify policies into different policy categories, a policy can belong only to one policy category.

All objects defined on a SQL Server instance form a target hierarchy. Within a policy, you define a target when you apply filters to the target hierarchy. For example, a target set with a large scope could include all the databases on an instance of SQL Server, while a target set with a small scope could include only the tables and indexes owned by the Sales schema in the Customers database.

The effective policies of a target are those policies that govern the target. For a policy to be effective with regard to a target, the policy must be enabled and the target must be subject to the policy. Within your data services ­environments, you enforce Policy-Based Management by using configuration servers. A ­designated configuration server is responsible for monitoring and enforcing policies as ­assigned. By default, each instance of SQL Server acts as its own configuration server. This means that each SQL Server instance normally handles its own policy monitoring and enforcement.

REAL WORLD   To be notified when messages from automatically executed policies are written to the event logs, you can create alerts to detect these messages and perform necessary actions. The alerts should detect the messages according to their message number. Look for message numbers 34050, 34051, 34052, and 34053. You can configure alerts as discussed in the “Managing Alerts” section in Chapter 10, ­“Automating and Maintaining SQL Server 2012.”

When policies are executed automatically, they execute as a member of the sysadmin role. This allows the policy to write entries to the event logs and raise an alert. When policies are evaluated on demand, they execute in the security context of the current user. To write to the event log, the user must have ALTER TRACE permissions or be a member of the sysadmin fixed server role; otherwise, Windows will not write to the event log and will not fire an alert.

Working with Policy-Based Management

You must be a member of the PolicyAdministratorRole role in the msdb database to configure Policy-Based Management settings. This role has complete control of all policies and can create policies and conditions, edit policies and conditions, and enable or disable policies.

When working with policies, keep the following in mind:

  • A system administrator or database owner can subscribe a database to a policy or policy group.
  • On-demand policy execution occurs in the security context of the user.
  • Members of the PolicyAdministratorRole role can create policies that they do not have permission to execute on an ad hoc basis.
  • Members of the PolicyAdministratorRole role can enable or disable policies.
  • Policies that are in the On Schedule mode use SQL Server Agent jobs that are owned by the sa login.

Although you can manage policies for each instance of SQL Server, you’ll likely reuse policies you’ve defined and then apply them to other instances of SQL Server. With Policy-Based Management, you can apply policies to multiple instances of SQL Server in several ways. As discussed in the “Importing and Exporting ­Policies” ­section later in this chapter, you can export the policies you’ve defined on a ­particular instance of SQL Server and then import the policies on another instance of SQL Server. During the import process, you can specify whether policies are ­enabled or disabled and whether to preserve the exported state of the policies.

Being able to export and import policies is useful. However, you don’t ­necessarily need to move policies around to enforce the policies on multiple ­computers ­running SQL Server. Instead, you can manage policies by using a central ­management server. A central management server is a special type of ­configuration server that is ­responsible for monitoring and enforcing policy on any instance of SQL Server ­registered as a subordinate server. As discussed in the “Configuring ­Central ­Management Servers” section later in this chapter, you designate central ­management servers and their subordinates by using SQL Server Management ­Studio. Because the central management architecture is already an execution ­environment for Transact-SQL (T-SQL) statements related to policies, you can ­execute T-SQL statements on multiple instances of SQL Server at the same time from a central management server.

REAL WORLD    Generally, SQL Server does more validation in the graphical user interface (GUI) than in the application programming interface (API). As a result, you may be allowed to create a policy in T-SQL but be restricted from creating the same policy in the GUI. Why? Because SQL Server tries to prevent you from creating policies that might impact performance when working in the GUI, while making it possible for advanced users to be able to work around this.

Because SQL Server stores policy-related data in the msdb database, you should back up msdb after you change conditions, policies, or categories. Policy history for policies evaluated in the current instance of the Database Engine is maintained in msdb system tables. Policy history for policies applied to other instances of the ­Database Engine or applied to Reporting Services or Analysis Services is not retained.

As summarized in Table 3-2, SQL Server 2012 includes several sets of predefined policies, including those for the Database Engine, Analysis Services, and Reporting Services. By default, the policies are stored as XML files in the following locations, and you must import them into SQL Server:

  • Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033
  • Micros  oft SQL Server\110\Tools\Policies\AnalysisServices\1033
  • Microsoft SQL Server\110\Tools\Policies\ReportingServices\1033

NOTE   On 64-bit computers, policies are located under Program Files (x86) rather than Program Files when you install SQL Server in the default file system location. ­Surface area configuration is discussed in the “Managing SQL Server Component ­Feature Access” section in Chapter 2, “Managing SQL Server Services and Clients.”

TABLE 3-2   Important Predefined Policies for SQL Server 2012

Predefined Policy Name


Asymmetric Key Encryption Algorithm

Checks whether asymmetric keys were created by using ­1024-bit or stronger encryption. As a best practice, you should use RSA 1024-bit or stronger encryption to create asymmetric keys for data encryption.

Backup And Data File ­Location

Checks whether database files are on devices separate from the backup files. As a best practice, you should put the database and backups on separate backup devices. This approach helps safeguard the data in case of device failure and also ­optimizes the I/O performance for both the production use of the ­database and the writing of backups.

CmdExec Rights Secured

Checks an instance of SQL Server 2000 to determine whether only members of the sysadmin server role can run CmdExec and ActiveX Script job steps, which is a recommended best practice.

Data And Log File Location

Checks whether data and log files are placed on separate logical drives. As a best practice, placing the files on separate drives ­allows the I/O activity to occur at the same time for both the data and log files.

Database Auto Close

Checks whether the AUTO_CLOSE option is set to OFF. When AUTO_CLOSE is set to ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection. As a best practice, you should set the AUTO_CLOSE option to OFF on a database that is accessed frequently.

Database Auto Shrink

Checks whether the AUTO_SHRINK database option is set to OFF. Because frequently shrinking and expanding a database can lead to fragmentation on the storage device, you should set the AUTO_SHRINK database option to OFF in most instances.

Database ­Collation

Checks whether user-defined databases are defined using a ­database collation that is the same as the collation for the master and model databases, which is a recommended best practice. Otherwise, collation conflicts can occur that might prevent code from executing. You can resolve collation conflicts by exporting the data from the user database, importing it into new tables that have the same collation as the master and model databases, and then rebuilding the system databases to use a collation that matches the user database collation. Or you can modify any stored procedures that join user tables to tables in tempdb to create the tables in tempdb by using the collation of the user database.

Database Page Status

Checks for user databases that have the database status set to Suspect. The Database Engine marks a database as ­Suspect when it reads a database page that contains an 824 error. ­Error 824 indicates that a logical consistency error was ­detected ­during a read operation, and it frequently indicates data ­corruption caused by a faulty I/O subsystem component. ­Resolve this situation by running DBCC CHECKDB.

Database Page Verification

Checks whether the PAGE_VERIFY database option is set to CHECKSUM. This recommended best practice helps provide a high level of data-file integrity by forcing the Database Engine to calculate a checksum over the contents of the whole page and store the value in the page header when a page is ­written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header.

Guest ­Permissions

Checks whether the Guest user has permission to access a user database. As a best practice, you should revoke the Guest user permission to access non-system databases if it is not required. Although the Guest user cannot be dropped, the Guest user can be disabled by revoking its CONNECT permission. Execute ­REVOKE CONNECT FROM GUEST within any database other than master or tempdb.

Last Successful Backup Date

Checks to ensure that a database has recent backups. Scheduling regular backups protects a database against data loss. If there are no recent backups, you should schedule backups by using a database maintenance plan.

Public Not Granted Server Permissions

Checks whether the public server role has server permissions. Every login that is created on the server is a member of the public server role and has server permissions. As a best practice, however, do not grant server permissions directly to the public server role.

Read-Only ­Database ­Recovery Model

Checks for read-only user databases that have recovery set to Full. As a best practice, these databases should use the Simple recovery model because they aren’t updated regularly.

SQL Server ­32-Bit Affinity Mask Overlap

Checks whether the 32-bit instance of SQL Server has one or more processors that are assigned to be used with both the Affinity Mask and the Affinity I/O Mask options. Enabling a CPU with both these options can slow performance by forcing the processor to be overused.

SQL Server ­64-Bit Affinity Mask Overlap

Checks whether the 64-bit instance of SQL Server has one or more processors that are assigned to be used with both the Affinity Mask and the Affinity I/O Mask options. Enabling a CPU with both these options can slow performance by forcing the processor to be overused.

SQL Server ­Affinity Mask

Checks whether the Affinity Mask option is set to 0. This is the default value, which dynamically controls CPU affinity. Using the default value is a recommended best practice.

SQL Server Blocked Process Threshold

Checks the Blocked Process Threshold option and ensures that it is set to 0 (disabled) or to a value higher than or equal to 5 seconds. Setting the Blocked Process Threshold option to a value from 1 through 4 can cause the deadlock monitor to run constantly, and this state is desirable only when you are ­troubleshooting.

SQL Server Default Trace

Determines whether the Default Trace option is disabled. When this option is enabled, default tracing provides ­information about configuration and DDL changes to the SQL Server ­Database Engine.

SQL Server ­Dynamic Locks

Checks whether the Locks option is set to 0. This is the default value, which dynamically controls locks. Using the default value is a recommended best practice. If the maximum number of locks is reached, batch jobs stop and SQL Server generates “out of locks” errors.

SQL Server I_O Affinity Mask for Non-Enterprise Servers

Checks whether the IO Affinity Mask option is set to 0 for editions of SQL Server other than Enterprise. With this value, SQL Server disk I/O is scheduled to any of the CPUs eligible to process SQL Server threads.

SQL Server Lightweight Pooling

Checks whether the Lightweight Pooling option is set to 0. This is the default value, which prevents SQL Server from using lightweight pooling. Using the default value is a recommended best practice.

SQL Server Login Mode

Checks the login security configuration to ensure Windows authentication is being used. Using Windows ­authentication is a recommended best practice because this mode uses the ­Kerberos security protocol, provides support for account ­lockout, and supports password expiration. For Windows Server 2008, Windows authentication also provides password policy enforcement in terms of complexity validation for strong ­passwords.

SQL Server Max Degree Of ­Parallelism

Checks whether the Max Degree Of Parallelism (MAXDOP) ­option is set to a value greater than 8. Setting this option to a value greater than 8 often causes unwanted resource ­consumption and performance degradation, so you usually want to reduce the value to 8 or less.

SQL Server Max Worker Threads For SQL Server 2005 And Above

Checks the Max Worker Threads option for potentially ­incorrect settings. Setting the Max Worker Threads option to a small value might prevent enough threads from servicing ­incoming client requests in a timely manner. Setting the option to a large value can waste address space because each active thread ­consumes 512 kilobytes (KB) on 32-bit servers and up to 4 megabytes (MB) on 64-bit servers. For instances of SQL Server 2005 and SQL Server 2012, you should set this option to 0, which allows SQL Server to determine the correct number of ­active worker threads automatically based on user requests.

SQL Server Network Packet Size

Determines whether the network packet size of any logged-in user is more than 8,060 bytes. As a best practice, the network packet size should not exceed 8,060 bytes. Otherwise, SQL Server performs different memory allocation operations, and this can cause an increase in the virtual address space that is not reserved for the buffer pool.

SQL Server ­Password ­Expiration

Checks whether password expiration is enabled for each SQL Server login. As a best practice, you should use ALTER LOGIN to enable password expiration for all SQL Server logins. ­Additionally, if SQL Server authentication is not required in your environment, you should enable only Windows authentication.

SQL Server ­Password Policy

Checks whether the Enforce Password policy is enabled for each SQL Server login. As a best practice, you should enable the Enforce Password policy for all the SQL Server logins by using ALTER LOGIN.

SQL Server System Tables Updatable

Checks whether system tables for SQL Server 2000 can be ­updated. As a best practice, you shouldn’t allow updates to system tables.

Surface Area Configuration for Database Engine …

A set of related policies for determining whether various ­editions of SQL Server are using default surface area settings. By disabling unneeded features, you can enhance security.

Symmetric Key Encryption For User Databases

Checks whether encryption keys that have a length of less than 128 bytes do not use the RC2 or RC4 encryption algorithm. As a best practice, you should use AES 128 bit or larger to create symmetric keys for data encryption. If AES is not supported by your operating system, you should use 3DES encryption.

Symmetric Key For master ­Database

Checks for user-created symmetric keys in the master database.

Symmetric Key For System Databases

Checks for user-created symmetric keys in the model, msdb, and tempdb databases. As a best practice, you should not ­create symmetric keys in the system databases.

Trustworthy Database

Checks whether the dbo role for a database is assigned to the sysadmin fixed server role and the database has its ­trustworthy bit set to ON. As a best practice, you should turn off the trustworthy bit or revoke sysadmin permissions from the dbo database role. Otherwise, a privileged database user can elevate privileges to the sysadmin role and then create and run unsafe assemblies that could compromise the system.

Windows Event Log Cluster Disk Resource ­Corruption Error

Checks the system event log for EventId 1066. This ­error can ­occur when a device is malfunctioning and also as a ­result of small computer system interface (SCSI) host adapter ­configuration issues.

Windows Event Log Device Driver Control Error

Checks the system event log for EventId 11. This error can be caused by a corrupt device driver, a hardware problem, faulty cabling, or connectivity issues.

Windows Event Log Device Not Ready Error

Checks the system event log for EventId 15. This error can be caused by SCSI host adapter configuration issues or related problems.

Windows Event Log Disk ­Defragmentation

Checks the system event log for EventId 55. This error occurs when the Disk Defragmenter tool cannot move a particular data element, and as a result Chkdsk.exe is scheduled to run.

Windows Event Log Failed I_O Request Error

Checks the system event log for EventId 50. This error is caused by a failed I/O request.

Windows Event Log I_O Delay Warning

Checks the event log for error message 833. This message ­indicates that SQL Server has issued a read or write request from disk and that the request has taken longer than 15 ­seconds to return. You can troubleshoot this error by examining the system event log for hardware-related error messages. Look also for hardware-specific logs.

Windows Event Log I_O Error During Hard Page Fault Error

Checks the system event log for EventId 51. This error is caused by an error during a hard page fault.

Windows Event Log Read Retry Error

Checks the event log for SQL Server error message 825. This message indicates that SQL Server was unable to read data from the disk on the first try. You need to check the disks, disk controllers, array cards, and disk drivers.

Windows Event Log Storage ­System I_O Timeout Error

Checks the system event log for EventId 9. This message ­indicates that an I/O time-out has occurred in the storage system.

Windows Event Log System Failure Error

Checks the system event log for EventId 6008. This event ­indicates an unexpected system shutdown.

Real World   Guest Permissions policy is meant for non-system databases only. Disabling guest access doesn’t apply to master, msdb, and tempb system databases. Some SQL Server features require the guest user to be enabled in the msdb database. If guest access is disabled in msdb, you may receive error 916 when you try to connect to SQL Server using SQL Server Management Studio or other applications.

Comments (1)
  1. Renee says:

    The list was very helpful  I could not find it anywhere.  Thank you!!

Comments are closed.

Skip to main content