Implementing Policy-Based Management in Microsoft SQL Server
- 2/15/2012
- Introducing Policy-Based Management
- Working with Policy-Based Management
- Configuring Central Management Servers
- Managing Policies Throughout the Enterprise
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.
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
Microsoft SQL Server\110\Tools\Policies\AnalysisServices\1033
Microsoft SQL Server\110\Tools\Policies\ReportingServices\1033
Table 3-2 Important Predefined Policies for SQL Server 2012
Predefined Policy Name | Description |
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. |