SQL Server 2008 policy-based Management

SQL Server 2008 lowers the management workload of the data platform by introducing scalable policy-based management capabilities. The most notable of these is the declarative Management Framework (DMF), which provides a strategic management, but this name has been enabled and is now the name of policy-based management (Policy Based Management), Let's call it PBM for short. PBM enables DBAs to develop management policies and apply these policies to servers, databases, and other objects in the data environment. A well-designed management strategy can help DBAs manage the data environment proactively.

PBM's strengths are reflected in:

On-demand management: PBM provides a logical view of the system configuration, so DBAs can predetermine the data service configurations they need without waiting for them to actually happen.

Intelligent Monitoring: PBM can continuously monitor the configuration changes of the system and prevent those configuration changes that violate the policy.

Virtual management: Through the PBM,DBA can be multiple servers for large-scale management, within the enterprise unified implementation of some mandatory configuration will become more convenient.

A framework based on policy management

The PBM framework consists of three parts:

Policy Management: Administrators make a variety of policies.

Explicit management: An administrator checks the compliance of a target against a policy by applying a policy to a specified target or target group, or, more strictly, prohibits the violation of policies on those targets.

Execution mode: PBM in SQL Server 2008 supports 4 execution modes, which determine the extent to which the policy affects the target. These four modes are:

On-demand (on Demand): Policies in this mode can have administrator-free choices about whether or not they are applied, such as an administrator can manually invoke these policies to check the compliance of the target, or subscribe to these policies through DDL trigger.

Block on update (on change-prevent): This is one of the strictest, SQL Server 2008 uses the DDL trigger method to enforce the compliance of a check operation when an operation is subscribed to a target on this policy, and if the policy is violated, roll back the operation. To achieve the effect of the enforcement policy.

Update-time record (on Change-log only): SQL Server 2008 uses the event notification mechanism to enforce the compliance of a check operation when an action is subscribed to a target on this policy, and send a message if the policy is violated. Records the violation by sending it through a queue in Service Broker.

By schedule (on Schedule): Invokes a policy to check the target object through the job of the SQL Agent.

Although PBM has more than four execution modes, belongs is actually two, one based on the on Schedule mode of the SQL Agent job, and one based on the event mechanism on the change mode. As a result, not all facet support on the change mode, and to support on shift mode, facet state changes must be possible through event capture or transactional DDL operations, of course on schedule and on demand without these mechanisms, Because these two schemas do not need to participate in the transaction of the facet State update.

Policy-based Management based architecture diagram

Policy-based Management based architecture diagram