This article will address the main techniques for the efficient management of multiple instances in a SQL Server database 2014 using the Tools Data Collector and Policy-Based Management. In addition, it will be demonstrated in a practical way how to configure each of the tools, as well as how to extract and analyze the metrics collected.
With the exponential growth of the amount of data generated by applications, comes the increased complexity in managing database environments for the database administrator. With this growth, combined with the low cost of storage media, servers began a scenario in which the database administrator left to administer dozens of databases and proceeded to administer hundreds of them.
Since the main responsibilities of a database administrator is to ensure the integrity, performance and stability of all instances of SQL Server under its administration, the greater the number of instances and databases used within an enterprise, the greater the difficulty in monitoring and managing such an environment in proactive and automated fashion.
For this type of scenario, SQL Server makes it possible to centralize both the execution of routine tasks for an administrator, since the collection of performance metrics from all instances and existing databases is through the Tools Data Collector (DC) and Policy-Based Management (PBM). For example, there is the need for all databases that have the recovery model parameter set to Full to perform a backup of the log file every hour. So, instead of this policy existing only as a concept, requiring you to check manually on all database servers, you can use the PBM to create “physically” a policy and ensure that it is applied at once in all instances of SQL Server.
In order to facilitate the understanding of the management of multiple instances, the presentation of the tools will be performed in the following order: first we will look at the Policy-Based Management and then we will know the Data Collector.
What is the Policy-Based Management?
The Policy-Based Management (PBM) is a feature available starting with SQL Server 2008 that enables the creation and implementation of policies on their SQL Server instances. The PBM works similarly to the created group policy through the Active Directory.
Note: Group policies provide centralized management of applications and users, by means of rules created by system administrators and that can be applied at various levels of the directory structure defined in Active Directory.
PBM applies a policy on a particular target, for example, a database, a table, a view, or a stored procedure and then it is checked to see if the target is in accordance with the rules of this policy. If the target does not agree, it is possible to both enforce the rules of politics as raise an alert to the administrator of the database so he/she knows of this violation.
One of the great advantages of the PBM is the implementation of a policy on multiple instances of a SQL Server database at once, facilitating the Administration and management of all the infrastructure of the Corporation Bank.
Many features of SQL Server 2014, such as Resource Governor, Data Compression and In-Memory OLTP need Enterprise Edition or Developer. This is not the case for the PBM, which is available in all editions of SQL Server, including Express (although with the Express Edition is not possible to create a Central Management Server).
As soon as the instance of SQL Server is installed in 2014, it is possible to create and evaluate the policies against any existing SQL Server in your environment, including in versions prior to 2014.
Policy-Based Management Components
The PBM is composed of three main components: Policies, Conditions and Facets, as shown in Figure 1. These components are arranged in a sort of hierarchical order for using the PBM. A facet is required for creating a condition, and the condition is necessary for the creation of policies. The policies, in turn, are applied to specific targets .
Figure 1. Policy Management in SQL Server 2014.
Targets
The targets are the managed objects for a particular policy and can be of various types: servers, databases, instances, stored procedures, etc. An important detail is that you can use more than one target at the same time in a policy. For example, we have a policy which States that only object names starting with the db _ prefix are correct and perform a validation of this rule on tables, functions and stored procedures of one or more instances at the same time.
Facets
A facet is a group of properties that relate to a particular target. SQL Server 2014 has 86 facets, each containing several different properties. This allows the use of hundreds of properties in the creation of a policy.
You can view the properties of a facet expanding Facets folder and double-clicking any of the options. For example, the facet Data File has several properties, such as maximum size of the data file, number of readings and writings and if the data file is online, as shown in Figure 2.
Note: The facets are available as read-only, i.e. it is not possible to create customized facets or modifications of existing ones. Currently new facets can be included only by Microsoft, through service packs or by upgrading the version of SQL Server.
Figure 2. Facet Properties Data File.
Conditions
A condition can be described as a rule for a policy to be evaluated. Basically, the rule checks a target and, if this target is not in accordance with the policy, it fails. It is worth mentioning that a policy can evaluate only one condition, however it is possible that a condition has different rules for different properties. A condition can be viewed by expanding the Conditions folder and double-clicking any one of the available options, as shown in Figure 3.
Note: In an instance of SQL Server, a database will not exist conditions customized unless previously imported or created manually, that is, initially there will only be the conditions of system.
Figure 3. Condition created on Facet Database.
Policies
The policies are complete packages that include conditions, facets, targets, assessment modes and Server restrictions (the evaluation modes and the server are discussed in the next topic).
When created, the policies are stored in the system database msdb, but you can export them into an XML format. This portability allows database administrators with greater ease to share and compare the policies created. To view a policy it is necessary to expand the Policies folder and double-click any one of the options, as shown in Figure 4.
Figure 4. Details of a policy created
Note: In an instance of SQL Server, databases will not exist as customized policies, unless previously imported or created manually, that is, there will be only the initial policies.
Policy Evaluation Modes
The PBM has four distinct ways of performing a policy and that determines how the evaluation will occur under a predefined target. The following modes of evaluation may be available, depending on the facet used in policies:
- On Demand: This evaluation mode specifies that the implementation should occur manually. By default, any policy with this evaluation mode is disabled automatically after it is created. However, even though I disabled it it can still be evaluated at any time.
- On Schedule: By selecting this mode you can schedule the evaluation policy to be evaluated at any time. By default, you can select a schedule already created or create a new schedule that meets your needs. Creating a schedule allows you to set options such as the recurrence of execution, execution frequency per day, frequency of execution per hour and how long a policy should be executed. For example, you could run a particular policy for the next two weeks.
- On Change: Log Only: when you select this mode, the policy will be evaluated only if a change is made to the target specified. If the change violates the policy, the event will be executed and the results of the violation will be stored in the event log and in the system database msdb. This evaluation mode helps the database administrator without affecting the performance of the environment.
- On Change: Prevent: This evaluation mode is very similar to the On Change: Log Only, namely, the assessment will be the moment an event to perform any change in target. But unlike the Log Only option, Prevent performs the rollback procedure of any amendment which violates the policy.
The Figure 5 shows an example of a policy and evaluation modes available for the same.
Figure 5. Evaluation Modes.
Server Restrictions
In conjunction with the targets and the facets, the server restrictions are another way to control how a policy is evaluated. A server restriction is nothing more than a condition used to delete a particular policy server through the facet Server.
With the use of the PBM, you can create a server restriction to limit the evaluation of a policy only on instances of SQL Server using the Standard editions or Enterprise. When this policy is applied it will not be assessed by the instances that do not use these specific issues.
Management of Policies
SQL Server 2014 has some features that facilitate the management and evaluation of policies created. One of these features is the ability to create categories to group similar policies and use the Central Management Server (CMS) to execute the policies throughout the database environment.
Categories
The categories are a logical group of one or more policies that assist in the management and execution of the same. For example, you can create a policy group that will be evaluated only in test or development environments. When a policy is created, specify a category Description option, as shown in Figure 6.
Figure 6. Category definition
Central Management Server (CMS)
CMS functionality is not part of the architecture of the PBM, but has become extremely important in the use of policies in a SQL Server database consisting of multiple servers.
Through the CMS you can specify a database instance (or greater) to be a central management and store a list of registered instances that can be organized into one or more groups, as shown in Figure 7.
Figure 7. Central Management Server (CMS)
Alerts
Once the policies are configured and implemented, there is no need to constantly check the servers to make sure that they are in accordance with the conditions set out in the policies. Instead, we can use the SQL Server Agent alerts to receive notifications automatically when a policy is violated.
In the next post of this 3 part series we will learn how to create a policy and how to use it.
Discover more about our expertise in SQL Server.
No comments