Data collector and policy-based management: part 2

In my previous post we talked about the architecture of Policy-Based Management. In this post we will create and run a policy against some SQL Server Instances.

Creating a Policy

In SQL Server 2014 you are allowed to create a policy manually via T-SQL, export existing policies in an instance of a SQL Server database or import policies made available by Microsoft. When designing a policy we can use advanced conditions that allow almost unlimited amounts of validation on database objects existing in an instance.

To facilitate the understanding of all the components used in a policy and how the same interact with each other, we will create a policy from scratch. To do this, you must first define a condition and then the policy used in this condition. With the policy set, it will be possible to categorize it and define which targets should be evaluated.

In order to illustrate this in a practical way we will use PBM. From now on, we will set a policy that will evaluate if all databases in a given SQL Server instance are disabled with the AutoShrink property.

Creating a Condition

The first step in the creation of the policy is the setting for the condition, which can be accomplished through SQL Server Management Studio (SSMS) or by using T-SQL commands.

To create a condition with SSMS, we have the following steps:

1)  Using the Object Explorer, open the folder Management and then the Policy Management folder (see Figure 8):

Figure 8. Folder Policy Management.

2)  Within that folder, right-click the folder Conditions and select the New Condition.

3)  This will open the window for the creation of the new condition, as it exposes the Figure 9.

Figure 9. Condition creation window.

According to this figure, while creating the condition these were the following options:

• Name: The condition was created with the name AutoShrink False.
• Facet: The facet used was the Database.
• Expression: The property was valued @AutoShrink and its value must be equal to false.

Additionally, you can include a detailed description about the condition through the Description, as shown in Figure 10.

Figure 10. Condition description window.

The second option for creating the condition is through the system stored procedure SP_SYSPOLICY_ADD_CONDITION. The Listing 1 shows that the creation of condition AutoShrink is False, with the same options set in SSMS.

Listing 1. Creating the condition with T-SQL.

</pre>
Declare @condition_id int
EXEC msdb. dbo. sp_syspolicy_add_condition @name=N ' AutoShrink False ', @description=N ' ', @facet=N ' Database ', @expression=N ' <Operator>
<TypeClass>Bool</TypeClass>
<OpType>EQ</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>Bool</TypeClass>
<Name>AutoShrink</Name>
</Attribute>
<Function>
<TypeClass>Bool</TypeClass>
<FunctionType>False</FunctionType>
<ReturnType>Bool</ReturnType>
<Count>0</Count>
</Function>
</Operator> ' , @is_name_condition=0, @obj_name=N ' ', @[email protected]_id OUTPUT
Select @condition_id
GO

Creating a Policy

Now that we have a condition, we can define a policy that uses it. As a condition, the policy can be configured either through the SQL Server Management Studio, such as through T-SQL.

First we create a policy using the SSMS:

1)  Using the Object Explorer, open the folder Management and then the Policy Management folder (see Figure 8).

2)  Within that folder, right-click on the Policies folder and select New Policy.

3) This will open the window for the creation of the new policy, as shown in the Figure 11.

Figure 11. Window for policy creation.

According to the Figure 11, during the creation of the policy the following options were set:

• Name: The policy was created with the name Checks AutoShrink.
• Check Condition: The condition evaluated by policies is the AutoShrink False.
• Against Targets: The policy should be evaluated in any existing database on the instance of SQL Server.
• Evaluation Mode: The evaluation mode selected was the OnDemand, which determines that this policy must be performed manually.
• Server Restriction: To this policy there will be no conditions that restrict the database servers.

Note: As a target it won’t always be a database. The targets change based on the context and evaluation of the condition. For example, if we create a policy to standardize the name of new tables using the facet Tables, Against Targets option will display All Tables.

The evaluation modes available in the list depend on the facets available in the condition. All facets support the OnChange and OnSchedule, but the OnChange: Prevent depends on the possibility of using the DDL triggers to perform the transaction rollback procedure. Already the evaluation mode OnChange: Log Only is based on the capacity of changes in the facet being captured for an event.

To facilitate the maintenance and management of configured policies, we can add more details through the Description, like Figure 12.

Figure 12. Policy description window.

In addition, you can also perform the creation of policies using the system stored procedures SP_SYSPOLICY_ADD_OBJECT_SET, SP_SYSPOLICY_ADD_TARGET_SET, SP_SYSPOLICY_ADD_TARGET_SET_LEVEL and SP_SYSPOLICY_ADD_POLICY.

The Listing 2 exemplifies the creation of policy Checks AutoShrink, with the same options set in SSMS.

Listing 2. Creation of the policy with T-SQL.

Declare @object_set_id int
EXEC msdb. dbo. sp_syspolicy_add_object_set @object_set_name=N ' AutoShrink_ObjectSet_1 ' Checks, @facet=N ' Database ', @[email protected]_set_id OUTPUT
GO
Declare @target_set_id int
EXEC msdb. dbo. sp_syspolicy_add_target_set @object_set_name=N ' AutoShrink_ObjectSet_1 ' Checks, @type_skeleton=N ' Server/Database ', @type=N ' DATABASE ', @enabled=True, @[email protected]_set_id OUTPUT
GO
EXEC msdb. dbo. sp_syspolicy_add_target_set_level @[email protected]_set_id, @type_skeleton=N ' Server/Database ', @level_name=N ' Database ', @condition_name=N ' ', @target_set_level_id=0
GO
Declare @policy_id int
EXEC msdb. dbo. sp_syspolicy_add_policy @name=N ' Checks AutoShrink ', @condition_name=N ' AutoShrink False ', @execution_mode=0, @[email protected]_id OUTPUT, @root_condition_name=N ' ', @object_set=N ' AutoShrink_ObjectSet_1 ' Checks
GO

Note: All policies created are stored in the system database msdb. After we create our policies, we have to make sure the system database msdb is part of the backup strategy used in the environment, because in this way it will be possible to rebuild all policies if there is some migration or disaster with the current environment.

Importing Policies

In order to increase the efficiency in the administration of a database environment, we can import policies made available by Microsoft during the SQL Server installation process. These policies are stored in XML format files that are located in the Tools directory where SQL Server was installed.

The import of predefined policies has some advantages, because along with the policies all necessary conditions for the correct operation are created. In addition to the predefined policies we can import an XML file that was generated based on customized policies already created by the database administrator using the PBM.

To run the import of a policy we must perform the following steps:

1)  Using the Object Explorer, open the Management folder (see Figure 8).

2)  Within that folder, right-click the Policies folder and select the option Import Policy.

3)  This will open the window to import the XML file with policy settings, as shown in the Figure 13.

Figure 13. Importing a policy.

To avoid duplication during the import, select Replace duplicates with Items Imported to overwrite any policy and condition that has the same name as the policy that is being imported. To overwrite an existing policy, the information will not be lost if the validation was already carried out within the policy.

We can also preserve the default configurations of the policy being imported, as well change them after importation policy, as Figure 14 presents.

Figure 14 . Definition of the status of the policy.

After importing the XML file containing the definitions of the policy, we can see it in the Policies folder. As shown in Figure 15, the new policy was created with the name Data and Log File Location.

Figure 15. Policy created through the import process.

It is interesting to note that unlike the previous created policy, the policy created through the import process has a constraint that limits the server validation of existing conditions only in instances of SQL Server using Enterprise or Standard editions.

Figure 16 shows the General information that was automatically inserted when imported through the policy XML file. The category, description and hyperlinks were populated, facilitating the process of documentation that states the reason why this policy has been implemented.

Figure 16. Description of the policy created through the import process.

Exporting Policies

Similarly, we can import policies using XML format files, and you can export the policies already created for files of this format. From these files can import these same policies in other SQL Server servers.

To perform the export policy procedure in XML format, there are two options:

1. Exporting an existing policy.
2. Exporting the current state of a facet.

To export an existing policy to an XML file, simply execute the following steps in SQL Server Management Studio:

1)  Using the Object Explorer, open the Management folder (Figure 8).

2)  Within that folder, open the folder Policy Management -> Policies to list existing policies (see Figure 17).

3)  Right-click the policy that you want to export and select Export Policy option (see Figure 18).

4)  According to the Figure 19, select the desired location to write the XML format file and the name of the same.

Figure 17. List of existing policies.

Figure 18. Export the policy.

Figure 19. Selecting policy storage location.

You can also export a policy by sourcing the current setting from the properties of a facet. For example, after setting up a customization for facet properties called Surface Area Configuration, you can export these settings to a file in XML format as follows:

1)  Through the Object Explorer, right-click the SQL Server instance and select Facets (see Figure 20).

2)  Select the facet you want, and then click Export Current State as Policy (Figure 21).

3)  According to Figure 22, fill in the name of the policy, the name of the condition and the export target of policy.

Figure 20. Listing the facets.

Figure 21. Exporting the current state of the facet as a policy.

Figure 22. Setting the policy storage location.

Policy Evaluation

Policy evaluation is the process in which we execute the policy in a target determined and reviewed by the same results. The PBM allows a policy to be evaluated in a single instance or a group of instances using the CMS. As the purpose of this article is the management of multiple instances, we will use the CMS to evaluate the policy Checks AutoShrink, created earlier, in two instances of the SQL Server database.

The Table 1 shows instances of SQL Server that will be used to assess the policy defined by the PBM.

Table 1. Instances used by PBM

To use the VITADB\SQLCMS instance as our management, we must perform the following steps:

1) Right-click the Central Management Servers option and select the option Register Central Management Server (see Figure 23).

2) In the dialog box New Server Registration, fill in the connection information, in accordance with the Figure 24.

3) Right-click the VITADB\SQLCMS instance and select New Server Registration (Figure 25).

4) Repeat the procedure described in step 3 and record the instances VITADB\SQLINSTANCE1 and VITADB\SQLINSTANCE2.

5) Right-click the VITADB\SQLCMS instance and select Evaluate Policies.

6) In the dialog box, select the instance that has the list of policies,  of which will be evaluated and, as shown in Figure 26, click the Evaluate button to start the validation of the selected rule.

Figure 23. Starting the creation of a central management instance.

Figure 24. Connection properties.

Figure 25. Registering a new server in the CMS.

Figure 26. By selecting the policy will be evaluated.

After the evaluation of the policy Checks AutoShrink can analyze, as in the Figure 27, there is a database named DBTeste1 on the VITADB\SQLINSTANCE1 instance and a database named DBTeste2 on the VITADB\SQLINSTANCE2 instance that are out of politics. That is to say, where the AutoShrink property is enabled, contrary to the previously set condition that determines how to correct the situation with a disabled property.

Figure 27. Result of the policy Checks AutoShrink.

As you can see, PBM has managed to create and evaluate policies in one or more database instances. In this way we have a simpler and more efficient management environment composed of multiple instances.

In the last part of this series, we will take a look at the Data Collector tool and how to use it in relation to a centralized monitoring of instances of SQL Server.

Discover more about our expertise in SQL Server.