You can use policies to help you to manage one or more instances of SQL Server. You define a policy that you want to apply to objects on an instance of SQL Server, and then you can enforce that policy on one or more instances of SQL Server. Policies can range from disabling SQL Mail, through enforcing naming conventions, through restricting file growth for large data files.
Examples of Policies
The following list describes some business scenarios that can be solved by using policies:
- Many companies like to use standard naming conventions for the objects in their databases. You can create policies that define what the naming convention should be for each particular object; for example, the name of a stored procedure should begin with usp_.
- By default, the local administrators group on a server is a member of the sysadmin SQL Server role. However, in a large enterprise, it is unlikely that system administrators are responsible for database administration, so they should really be removed from the role. This task is simple, but it is equally simple for someone else to add them back into the role at a later stage. To avoid this, you can create a policy that denies access to the BUILTIN\Administrators group, evaluate the policy against your SQL Server instances, and then force any noncompliant servers to deny access to the group. You can also configure the policy to roll back any future attempts to provide access for the group.
- A new corporate policy may define that all SQL Server instances should be using Windows authentication. To manually check the authentication mode of every SQL Server instance and then possibly change it to adhere to the new policy would be a tedious process. Instead, you can create a policy that defines that LoginMode should be set to Integrated, evaluate the policy against the servers, and then enforce the policy on any noncompliant servers.
You can use the Policy Management section of SQL Server Management Studio to create conditions and policies. You must create a condition before you create the policy that uses the condition.
Note: Users must belong to the PolicyAdministratorRole role in the msdb database to create conditions and policies.
Create a policy condition
1. In Object Explorer, expand Management, expand Policy Management, right-click Conditions, and then click New Condition.
2. In the Create New Condition dialog box, on the General page, in the Name box, type a name for the condition.
3. In the Facet box, click the facet that you want your condition to use.
4. In the Expression pane, select the attribute and value for which you want your condition to test, and then click OK.
Create a policy that belongs to a new category
1. In Object Explorer, right-click the Policies folder, and then click New Policy.
2. In the Create New Policy dialog box, on the General page, in the Name box, type a name for the policy.
3. In the Check condition box, click the condition that you previously created.
4. In the Evaluation Mode box, click an evaluation mode for the policy.
5. If you are using the On Schedule evaluation mode, configure a schedule and ensure that the SQL Server Agent service is running.
6. If you are using an automated evaluation mode, select the Enabled check box.
7. In the Create New Policy dialog box, click the Description page.
8. Next to the Category box, click New.
9. In the Create new category dialog box, type a name for the new category, and then click OK.
10. In the Create New Policy dialog box, click OK.
Set the Mandate Database Subscriptions option
1. In Object Explorer, right-click the Policy Management folder, and then click Manage Categories.
2. In the Manage Policy Categories dialog box, in the Mandate Database Subscriptions column, select the check box for the relevant category, and then click OK.
Subscribe an object to a policy category
1. In Object Explorer, right-click the object that you want to subscribe, point to Policies, and then click Categories.
2. In the Categories dialog box, expand the category to which you want to subscribe.
3. Select the Subscribed check box for the category, and then click OK.
Evaluate a policy from a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. On the Evaluate Results page, examine the results of the policy evaluation.
Evaluate a policy from an object
1. In Object Explorer, right-click a target (for example, a server instance, a database, or a database object), point to Policies, and then click Evaluate.
2. In the Evaluate Policies dialog box, select one or more policies, and then click Evaluate to run the policy in evaluation mode.
3. On the Evaluate Results page, examine the results of the policy evaluation.
View evaluation history
• In Object Explorer, right-click a policy, and then click View History.
Apply a policy
1. In Object Explorer, expand Management, expand Policy Management, expand Policies, right-click a policy, and then click Evaluate.
2. When the policy evaluation is complete, select any noncompliant target, and then click Apply.
3. In the Policy Evaluation Warning message box, click Yes.
4. In the Evaluate Policies dialog box, click Close.
No comments:
Post a Comment