In SQL Server Policy Based Management, best practices represent guidelines in a form of Policy Based Management policies that are in common SQL Server use scenarios considered as the best way to configure SQL Server instances and their objects. For example, it is considered a best practice for most SQL Server environments to use and enforce Windows Authentication mode. Violations of these policies can indicate SQL Server configurations that can result in low reliability, poor performance, increased security risks, unexpected conflicts, or other potential problems.

SQL Server Policy Based Management allows monitoring of best practices for SQL Server Database Engine. A set of policies in a form of XML files is provided within all SQL Server 2008 and later versions. These files can be imported into SQL Server instances as best practice policies, and then evaluated against a target set that includes instances, instance objects, databases, or database objects. The best practice policies can be evaluated manually, on desired schedule, or (if supported by the utilized policy facet) on a change event. For more information about evaluation modes, see the SQL Server security and Policy Based Management – Introduction online article.

The best practice policies files

The XML files which contain information about each best practice policy are by default installed with SQL Server. By default the files are installed to the C:\Program Files\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033 folder. Note that in case of a 64-bit Windows version, these XML files are installed to the C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Policies\DatabaseEngine\1033 folder in order to ensure compatibility with 32-bit applications such as some of Microsoft Best Practices Analyzer versions were.

There are 50 best practice policy files in SQL Server 2012. To import the files into a SQL Server instance using SQL Server Management Studio:

1. Expand the Management node in Object Manager and select the Import Policy option in the Policies context menu
2. Use the “…” button of the Files to import option to browse folders and select desired best practice policy files
3. Check the Replace duplicates with items imported option if needed (e.g. in case the policies were previously imported to SQL Server instance, modified, and you need to replace them with their default versions). In case you try to import the policy which already exists in SQL Server and do not use this option, an error will occur and the import process will be terminated.

4. Select the desired state of once imported policies using the Policy state dropdown. Note that all of the best practice policies are set with the disabled state in their XML files.

   

5. Click OK to complete the import process

Once the policies are imported, both the policies and their conditions are located under the Policies and Conditions nodes, respectively.

The policies can be evaluated against their default targets or modified as needed. The conditions of the imported policies can be altered to meet the requirements of a specific SQL Server environment, too. Each policy uses certain default targets based on the facet tied to the policy’s condition. In addition, a SQL Server Agent alerts can be declared for the certain policies, if the policies are set to use the On schedule evaluation mode.

The most commonly used best practice policies

Although there are a number of provided best practice policies, some of them are more often used in common SQL Server environments than others.

The Asymmetric Key Encryption Algorithm policy is defined against every asymmetric key and on every database in a SQL Server 2005 instance or later. It checks whether RSA 1024 or RSA 2048 encryption algorithms are used.

The Backup and Data File Location policy enforces the practice of storing backups and data files on separate logical volumes.

Similarly, the Data and Log File Location best practice policy evaluates whether data and transaction log files are using separate logical values. In addition, the policy checks whether the database is less than 5GB in size, is a system database, or is not online. If any of the latter three conditions is true, the database will be considered as policy compliant.

The Database Auto Close policy evaluates all databases and checks whether the AUTO_CLOSE database option is set. The option is used to release all resources used by the database after the last connection is closed and can cause serious performance issues.

Similarly to the previously described policy, the Database Auto Shrink policy checks whether all non-system and online databases use the Auto Shrink option. The option is considered as a SQL Server performance killer and should be avoided.

For compatibility and consistency purposes, the Database Collation policy evaluates whether SQL Server databases use the same collation as used by the master or model databases.

For various reasons (e.g. corruption or hardware failure) database pages can enter the Suspect mode and become unavailable for production purposes. The Database Page Status uses the following advanced condition to verify whether such suspect pages exist on SQL Server instance:

In order to ensure database reliability, it’s recommended to enforce usage of the checksum page integrity check type. The Database Page Verification policy evaluates this value against all SQL Server databases.

For the security purposes, the Guest Permissions policy checks whether the guest account has access to any user-defined and model databases.

The Last Successful Backup Date policy checks whether all SQL Server databases have recent backups, not older than one day.

For security and troubleshooting purposes, SQL Server enables the default trace during the installation by default. The SQL Server Default Trace policy checks whether the default trace is currently disabled.

One of primary security concerns for SQL Server environments is the server authentication mode. It is highly recommended to use the Windows Authentication mode for a number of reasons (e.g. weak SQL Server authentication resistance to brute-force attacks or advanced Windows account lock-out features). The SQL Server Login Mode policy checks whether the Windows Authentication mode is used on the SQL Server instance or not.

If the SQL Server and Windows Authentication mixed mode is used, the SQL Server Password Expiration and SQL Server Password Policy policies evaluate existing logins that use SQL Server Authentication. The policies check whether the Enforce password expiration and Enforce password policy settings are set on the logins.

The best practice policies provide a range of states that can be evaluated on SQL Server and its objects at any time. Whether a SQL Server instance is a new one that needs to be established by the recommendations, or an existing one that needs to be fixed by the recommendations, the best practice policies provide a good starting point.