What is the usage of policy management in sql server 2008?

What is the usage of policy management in 开发者_StackOverflowmanagement part of sql server 2008??

Policy Based Management is a SQL Server technology feature that is used to administer servers.

The Books Online documentation provides a comprehensive overview of Policy Based Management.

Should you then have specific questions about the technology I'd be happy to help.

The Policy Based Management feature was introduced in SQL Server 2008. The purpose of the feature is to assists SQL Server administrators in creating and enforcing policies tied to SQL Server instances and their objects. The policies can be configured on one SQL Server and re-used on other SQL Server instances to provide a SQL Server security model for instance

Policy Based Management allows DBAs to define the preferred state of the SQL Server system components (e.g. instances and objects) and compare the compliance status with the preferred state. Properly declared policies ensure enforcing company rules in the SQL Server environment, and are commonly a part of the SQL Server security model

The Policy Based Management feature is built on top of the SQL Server Management Objects collection (objects that are designed for programming all aspects of managing Microsoft SQL Server) which supports SQL Server 2000 and later versions. Therefore Policy Based Management can be utilized on versions prior to SQL Server 2008, for instance via the PowerShell subsystem and SQL Server Agent

Concepts of Policy Based Management The feature has three concepts:

1. Policy management – policies are created and maintained by SQL Server policy administrators
2. Explicit administration - one or more managed targets are explicitly checked by administrators whether they comply with a specific policy, or explicitly modify the targets according to a policy
3. Evaluation modes – the modes are divided per type of the execution and the afterward action based on the policy evaluation result:

a. On demand – the policy is evaluated when explicitly specified by the administrator

b. On change: prevent - if selected, this automated mode will utilize DDL triggers in order to prevent violations of specific policy by rolling back the changes. Note that this evaluation mode requires the nested triggers option enabled on SQL Server. If that’s not the case, the evaluation will perform with errors or fail. To enable the nested triggers, open the Properties dialog for a particular SQL Server instance, navigate to the Advanced page, and set the Allow Triggers to Fire Others property to True

c. On change: log only – the policy is automatically evaluated on changes that may violate the policy rules and event notifications are created

d. On schedule – this evaluation mode uses SQL Server Agent jobs to automatically check policy violations per scheduled time

Automated evaluations may affect system performance, depending on the number of objects being evaluated against the particular policy. On the other hand, automated evaluation modes ensure consistent SQL Server security

Policy-Based Management is a policy based system for managing one or more instances of SQL Server. Use is to create conditions that contain condition expressions. Then, create policies that apply the conditions to database target objects.

Use SQL Server management Studio to create and manage policies, to:

Select a Policy-Based Management facet that contains the properties to be configured.

Define a condition that specifies the state of a management facet.

Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.

Check whether an instance of SQL Server is in compliance with the policy.

Here is the sample use case of policy based management: https://www.mssqltips.com/sqlservertip/1924/monitor-your-sql-server-linked-server-configurations-with-policy-based-management/