Database Reference
In-Depth Information
How it works...
The Policy-Based Management component uses the SQL Server Agent service to evaluate the
created policies against a target. So the SQL Server Agent service must be up and running
to implement the PBM policies. The target can be an SQL Server instance or a database to
deploy. The policies can be used to enforce the compliance to best practices or to report an
out-of-compliance object.
The managed targets are entities that are managed by Policy-Based Management, such as an
instance of the SQL Server Database Engine, a database, a table, or an index. All targets in a
server instance form a target hierarchy.
All the PBM metadata management is stored in
msdb
system database with a prefix of
syspolicy
. Also, the PBM will stick to two execution modes in evaluating the defined policies
on an SQL Server instance; they are "On Demand" and "On Schedule". By default, two logins
are created on the server instance by PBM system which is disabled. These two logins are
##MS_PolicyEventProcessingLogin##
and
##MS_PolicyTsqlExectionLogin##
and within the database, users need to have permissions to view the PBM metadata. In
a nutshell, the
MS_PolicyEventProcessingLogin
will help the PBM to elevate to
sysadmin
to capture the required metadata. The
MS_PolicyTsqlExecutionLogin
is
used for TSQL processes and to construct policies; these are executed as "On Demand" model
and opened up as to run "On Schedule" with limited privileges.
As a best practice, we must add additional permission membership
in the
PolicyAdministratorRole
in the
msdb
system database
to this login with a prior testing on the development environment.
Besides, it is essential to understand the inner-workings and essential components of PBM
that are beneficial to follow the execution process, which is outlined in the following sections.
The On Demand execution mode consists of a policy engine, which is similar to the database
engine that contains system catalog views with policy store and SQL Management Objects
(SMO) to process the hierarchical object model that represents the components of the server
instance. Whenever a policy is evaluated using On Demand mode, the following methods are
used by PBM:
1.
The policy evaluation will load the policy from the storage format into memory, which
is stored as an XML file, or from
syspolicy
tables in the
msdb
system database.
2.
Once the policy is loaded into the database engine, the evaluation will begin by
retrieving the set of required target objects. Then, SMO retrieves the required
information by constructing a query against PBM catalog views.
It is worth pointing out that this is an optimization that PBM has for limiting
the number of stored procedures that are retrieved (the database returns the
requested data to SMO and the SMO constructs the collection).
Search WWH ::
Custom Search