Policy Based Management

This monthly blog post I like to introduce another overlooked SQL Server build-in feature, Policy Based Management (PBM), which is very useful indeed especially if you are a server/infrastructure DBA of a big corporation who need to administer many SQL Server instances. This feature can help us to evaluate and enforce some policies on database servers, such as naming conventions, file locations, and many configuration settings on different objects including server, database, file, login, table, etc. You can just create one set of policies, and apply the same set of policies to multiple server instances. Here I will demonstrate how to create policies to check whether the data files and log files of user databases are placed in the intended disk drives, another policy to enforce database enabling RCSI.

1. In SSMS Object Explorer, expand Management > Policy Management > right-click Conditions > New Condition. Create four conditions:
Name: Not System Databases. Facet: Database. Expression: @IsSystemObject = False
Name: Data File in M drive. Facet: Data File. Expression: @FileName LIKE 'M:%'
Name: Log File in N drive. Facet: Log File. Expression: @FileName LIKE 'N:%'
Name: Database RCSI enable. Facet: Database. Expression: @IsReadCommittedSnapshotOn = True

2. Right-click Policies > New Policy. Create three policies:
Name: Data Files Location. Check condition: Data File in M drive. Against targets: Every File, in Every FileGroup, in Not System Databases Database.
Name: Log Files Location. Check condition: Log File in N drive. Against targets: Every LogFile, in Not System Databases Database.
Name: Database RCSI enable. Check condition: Database RCSI enable. Against targets: Not System Databases Database.
As you can see, PBM condition can be used as Checking condition, and also be used as Filtering condition on target.

3. Now you are done on creating policies, let's evaluate them on local server instance. Right-click the Policies folder in object explorer > Evaluate > tick your tailor-made policies > press Evaluate button.

4. In the result, you can see which targets are violating your policies, and viewing the details.

5. For the Database RCSI enable policy, you can also apply the policy on the target, which essentially set the database RCSI option on, i.e. it runs ALTER DATABASE [UserDB] SET READ_COMMITTED_SNAPSHOT ON for you.

6. You can also create all policies in a central server instance, and evaluate the centralized policies on another server instances. In the Evaluate Policies window, you can select the Source, where you can select the central server.

BPM is easy to use. You even don't need to type one line of coding in order to create your own set of custom conditions and policies.