Using Policy-Based Management
Scenario
In this exercise, you will use Policy-Based Management to enforce database server configuration standards. Policy-
Based Management provides the ability to define policies that apply to servers, databases, and other objects in your
data environment. Well-defined policies can help administrators to control and manage settings proactively within
the data services environment, and ensure server configuration compliance with corporate standards throughout the enterprise.
Tasks Detailed Steps Complete the following task on:
1. Create policy conditions.
a. Click Start, point to All Programs, click Microsoft SQL Server 2008, and click
SQL Management Studio. In the Connect to Server dialog box, ensure the
following settings are selected and then click Connect.
Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
b. In Object Explorer, expand Management, expand Policy Management, expand
Facets, and then review the preconfigured facets that are available.
c. Right-click the Surface Area Configuration facet, and then click Properties.
Review the properties that are a part of this facet, and then close the Facet
Properties – Surface Area Configuration dialog box.
Note: Each facet represents an aspect of the system that you can control with a policy.
The wide range of built-in facets ensures that you have comprehensive control over
settings that can affect SQL Server instances, databases, and other objects. The
properties of each facet determine the settings that you can manage through policies.
d. In Object Explorer, right-click Conditions, and then click New Condition.
e. In the Create New Condition dialog box, on the General page, in the Name box,
type No E-Mail
Tip: The red circle with the white X at the top of the window provides hints about how
to resolve errors in the current window.
f. In the Facet box, click Surface Area Configuration.
g. In the first row in the Expression pane, in the Field box, click @SqlMailEnabled,
verify that the Operator is set to =, and in the Value box, click False.
h. In the second row in the Expression pane, in the Field box, click
@DatabaseMailEnabled, verify that the Operator is set to =, and in the Value
box, click False.
i. Ensure the AndOr value in the second row is set to And, and then click OK
j. In Object Explorer, right-click Conditions, and then click New Condition.
k. In the Create New Condition dialog box, on the General page, in the Name box,
type View Definitions Encrypted
l. In the Facet box, click View Options.
m. In the Expression pane, in the Field box, click @IsEncrypted, verify that the
Operator is set to =, and in the Value box, click True. Then click OK.
Note: Conditions are used to set desired values for properties of a facet. In this
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Page 3 of 13
Tasks Detailed Steps
example, you have created a condition that specifies that SQL Mail and Database
Mail should be disabled, and a condition that specifies that view definitions should be
encrypted. In the following procedures, you will create policies to enforce these
conditions.
2. Create and test a SQL Server instance-level policy.
Note: Policies can be applied to different targets. In this procedure, you will create a
policy that can be applied to a Server target. In other words, the policy will take effect
at the SQL Server instance level.
a. In Object Explorer, right-click the Policies folder, and then click New Policy.
b. In the Create New Policy dialog box, on the General page, in the Name box, type
Disable E-Mail
c. In the Check condition box, click No E-Mail.
Note: The drop-down list is organized into facets. The No E-Mail condition is under
the Surface Area Configuration facet.
d. In the Create New Policy dialog box, click the Description page.
e. Next to the Category box, click New.
f. In the Create new category dialog box, type Datacenter Server Policies and then
click OK.
Note: Categories help you organize your policies.
g. In the Create New Policy dialog box, click OK.
3. Evaluate the SQL Server instance-wide policy.
Note: In this procedure, you will evaluate the policy. Evaluating a policy checks each
of the conditions in the policy against the target (in this case the local SQL Server
instance) to verify compliance.
a. In Object Explorer, expand Policies, right-click the Disable E-Mail policy, and
then click Evaluate.
b. In the Evaluate Policies - SQL Mail Disabled dialog box, review the results, and
then in the Details column, click the View hyperlink. Note that the policy verified
that the Actual Value for the @SqlMailEnabled and @DatabaseMailEnabled
fields was False and matched the Expected Value.
c. In the Results Details View dialog box, click Close, and then in the Evaluate
Policies - SQL Mail Disabled dialog box, click Close.
4. Create a database-level policy.
Note: In this procedure, you will create a policy that can be assigned to individual
databases.
a. In Object Explorer, right-click the Policies folder, and then click New Policy.
b. In the Create New Policy dialog box, on the General page, in the Name box, type
Encrypt Views.
c. In the Check condition box, click View Definitions Encrypted.
Note: The View Definitions Encrypted condition is under the View Options facet.
d. In the Against targets box, locate in Every Database on the second line, click the
arrow next to Every, and then click New condition.
e. In the Create New Condition dialog box, in the Name box, type
AdventureWorks DB and then verify that the Facet is Database.
f. In the Expression pane, in the Field box, click @Name, verify that the Operator
is set to =, and in the Value box, type ‘AdventureWorks’.
Tip: Include the single quotes around AdventureWorks.
g. Press ENTER, and then click OK.
h. In the Evaluation Mode box, click On change: prevent.
Note: By selecting the On change: prevent evaluation mode, you have specified that
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps
the policy should be enforced whenever a user attempts to make a change that does
not comply with the policy conditions.
i. Select the Enabled check box.
j. Click the Description page.
k. On the Description page, next to the Category box, click New.
l. In the Create new category dialog box, type AW Database Policies and then
click OK.
m. In the Create New Policy dialog box, click OK.
5. Evaluate the database-level policy.
a. In Object Explorer, right-click the Encrypt Views policy, and then click Evaluate.
b. In the Evaluate Policies - Encrypt Views dialog box, review the results.
Note: The result is a failure for all nonencrypted views.
c. In the Target details section, point to the Target column, and then view the
information in the screen tip that appears. Note the name of the server, database,
and views that the policy has analyzed.
d. In the second row, in the Details column, click the View hyperlink. The Actual
Value column is False, indicating that the view definition is not encrypted.
e. In the Results Detailed View dialog box, click Close, and then in the Evaluate
Policies - Encrypt Views dialog box, click Close.
6. Verify policy subscription and functionality
a. In Object Explorer, right-click the Policy Management folder, and then click
Manage Categories.
b. In the Manage Policy Categories dialog box, in the Mandate Database
Subscriptions column, verify that the check box is selected for the Datacenter
Server Policies category.
c. Clear the Mandate Database Subscriptions check box for the AWDatabase
Policies category, and then click OK.
Note: The Mandate Database Subscriptions setting will automatically apply this
policy to all target object types that are defined in the policy.
d. In Object Explorer, expand Databases, right-click AdventureWorks, point to
Policies, and then click Categories.
e. In the Categories dialog box, expand Datacenter Server Policies.
f. Notice that the policy has a check showing that the database is subscribed to the
policy, but you cannot change the Subscribed setting. This is because the check
box in the Mandate Database Subscriptions column is selected for this category.
g. Expand the AWDatabase Policies category, and then review the policy settings.
h. Select the Subscribed check box for the AWDatabase Policies category, and then
click OK.
i. In Object Explorer, right-click the AdventureWorks database, and then click New
Query.
j. In the Query Editor, type the code in the following code example, and then click
Execute.
Note: This code is also available in the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PBM\EncryptedView.sql file.
USE AdventureWorks;
GO
CREATE VIEW TestEncrypt WITH ENCRYPTION
AS
SELECT Production.Product.Name,
Production.ProductReview.ReviewDate,
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps
Production.ProductReview.ReviewerName
FROM Production.Product INNER JOIN
Production.ProductReview
ON Production.Product.ProductID =
Production.ProductReview.ProductID
k. In Object Explorer, expand the AdventureWorks database, expand Views, and
then verify the creation of the view.
l. In Object Explorer, right-click the AdventureWorks database, and then click New
Query.
m. In the Query Editor, type the code in the following code example, and then click
Execute.
Note: This code is also available in the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PBM\UnencryptedView.sql file.
USE AdventureWorks;
GO
CREATE VIEW TestNoEncrypt
AS
SELECT Production.Product.Name,
Production.ProductReview.ReviewDate,
Production.ProductReview.ReviewerName
FROM Production.Product INNER JOIN
Production.ProductReview
ON Production.Product.ProductID =
Production.ProductReview.ProductID
Note: The batch is aborted because it violates the Encrypt Views policy.
n. In Object Explorer, right-click (local), point to Policies, and then click View.
Notice that the Disable E-Mail policy setting is not enabled because the policy is
set for on-demand evaluation.
o. In the History column, click the History link, and then review the information that
is displayed. This history was created when you tested the policy earlier in the lab.
p. In the Log File Viewer dialog box, click Close.
q. In the View Policies - MIAMI dialog box, click Close.
r. Keep SQL Server Management Studio open for the next exercise.
Note: Although you can use the GUI to disable policies, it is also possible to use a set
of system stored procedures to define and create Policy-based Management
components. Books Online provides you with an overview of the system stored
procedures and metadata used by Policy-Based Management components that are
defined and stored in the MSDB database
What's New in Microsoft® SQL Server® 2008
Scenario
In this exercise, you will use Policy-Based Management to enforce database server configuration standards. Policy-
Based Management provides the ability to define policies that apply to servers, databases, and other objects in your
data environment. Well-defined policies can help administrators to control and manage settings proactively within
the data services environment, and ensure server configuration compliance with corporate standards throughout the enterprise.
Tasks Detailed Steps Complete the following task on:
1. Create policy conditions.
a. Click Start, point to All Programs, click Microsoft SQL Server 2008, and click
SQL Management Studio. In the Connect to Server dialog box, ensure the
following settings are selected and then click Connect.
Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication
b. In Object Explorer, expand Management, expand Policy Management, expand
Facets, and then review the preconfigured facets that are available.
c. Right-click the Surface Area Configuration facet, and then click Properties.
Review the properties that are a part of this facet, and then close the Facet
Properties – Surface Area Configuration dialog box.
Note: Each facet represents an aspect of the system that you can control with a policy.
The wide range of built-in facets ensures that you have comprehensive control over
settings that can affect SQL Server instances, databases, and other objects. The
properties of each facet determine the settings that you can manage through policies.
d. In Object Explorer, right-click Conditions, and then click New Condition.
e. In the Create New Condition dialog box, on the General page, in the Name box,
type No E-Mail
Tip: The red circle with the white X at the top of the window provides hints about how
to resolve errors in the current window.
f. In the Facet box, click Surface Area Configuration.
g. In the first row in the Expression pane, in the Field box, click @SqlMailEnabled,
verify that the Operator is set to =, and in the Value box, click False.
h. In the second row in the Expression pane, in the Field box, click
@DatabaseMailEnabled, verify that the Operator is set to =, and in the Value
box, click False.
i. Ensure the AndOr value in the second row is set to And, and then click OK
j. In Object Explorer, right-click Conditions, and then click New Condition.
k. In the Create New Condition dialog box, on the General page, in the Name box,
type View Definitions Encrypted
l. In the Facet box, click View Options.
m. In the Expression pane, in the Field box, click @IsEncrypted, verify that the
Operator is set to =, and in the Value box, click True. Then click OK.
Note: Conditions are used to set desired values for properties of a facet. In this
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Page 3 of 13
Tasks Detailed Steps
example, you have created a condition that specifies that SQL Mail and Database
Mail should be disabled, and a condition that specifies that view definitions should be
encrypted. In the following procedures, you will create policies to enforce these
conditions.
2. Create and test a SQL Server instance-level policy.
Note: Policies can be applied to different targets. In this procedure, you will create a
policy that can be applied to a Server target. In other words, the policy will take effect
at the SQL Server instance level.
a. In Object Explorer, right-click the Policies folder, and then click New Policy.
b. In the Create New Policy dialog box, on the General page, in the Name box, type
Disable E-Mail
c. In the Check condition box, click No E-Mail.
Note: The drop-down list is organized into facets. The No E-Mail condition is under
the Surface Area Configuration facet.
d. In the Create New Policy dialog box, click the Description page.
e. Next to the Category box, click New.
f. In the Create new category dialog box, type Datacenter Server Policies and then
click OK.
Note: Categories help you organize your policies.
g. In the Create New Policy dialog box, click OK.
3. Evaluate the SQL Server instance-wide policy.
Note: In this procedure, you will evaluate the policy. Evaluating a policy checks each
of the conditions in the policy against the target (in this case the local SQL Server
instance) to verify compliance.
a. In Object Explorer, expand Policies, right-click the Disable E-Mail policy, and
then click Evaluate.
b. In the Evaluate Policies - SQL Mail Disabled dialog box, review the results, and
then in the Details column, click the View hyperlink. Note that the policy verified
that the Actual Value for the @SqlMailEnabled and @DatabaseMailEnabled
fields was False and matched the Expected Value.
c. In the Results Details View dialog box, click Close, and then in the Evaluate
Policies - SQL Mail Disabled dialog box, click Close.
4. Create a database-level policy.
Note: In this procedure, you will create a policy that can be assigned to individual
databases.
a. In Object Explorer, right-click the Policies folder, and then click New Policy.
b. In the Create New Policy dialog box, on the General page, in the Name box, type
Encrypt Views.
c. In the Check condition box, click View Definitions Encrypted.
Note: The View Definitions Encrypted condition is under the View Options facet.
d. In the Against targets box, locate in Every Database on the second line, click the
arrow next to Every, and then click New condition.
e. In the Create New Condition dialog box, in the Name box, type
AdventureWorks DB and then verify that the Facet is Database.
f. In the Expression pane, in the Field box, click @Name, verify that the Operator
is set to =, and in the Value box, type ‘AdventureWorks’.
Tip: Include the single quotes around AdventureWorks.
g. Press ENTER, and then click OK.
h. In the Evaluation Mode box, click On change: prevent.
Note: By selecting the On change: prevent evaluation mode, you have specified that
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps
the policy should be enforced whenever a user attempts to make a change that does
not comply with the policy conditions.
i. Select the Enabled check box.
j. Click the Description page.
k. On the Description page, next to the Category box, click New.
l. In the Create new category dialog box, type AW Database Policies and then
click OK.
m. In the Create New Policy dialog box, click OK.
5. Evaluate the database-level policy.
a. In Object Explorer, right-click the Encrypt Views policy, and then click Evaluate.
b. In the Evaluate Policies - Encrypt Views dialog box, review the results.
Note: The result is a failure for all nonencrypted views.
c. In the Target details section, point to the Target column, and then view the
information in the screen tip that appears. Note the name of the server, database,
and views that the policy has analyzed.
d. In the second row, in the Details column, click the View hyperlink. The Actual
Value column is False, indicating that the view definition is not encrypted.
e. In the Results Detailed View dialog box, click Close, and then in the Evaluate
Policies - Encrypt Views dialog box, click Close.
6. Verify policy subscription and functionality
a. In Object Explorer, right-click the Policy Management folder, and then click
Manage Categories.
b. In the Manage Policy Categories dialog box, in the Mandate Database
Subscriptions column, verify that the check box is selected for the Datacenter
Server Policies category.
c. Clear the Mandate Database Subscriptions check box for the AWDatabase
Policies category, and then click OK.
Note: The Mandate Database Subscriptions setting will automatically apply this
policy to all target object types that are defined in the policy.
d. In Object Explorer, expand Databases, right-click AdventureWorks, point to
Policies, and then click Categories.
e. In the Categories dialog box, expand Datacenter Server Policies.
f. Notice that the policy has a check showing that the database is subscribed to the
policy, but you cannot change the Subscribed setting. This is because the check
box in the Mandate Database Subscriptions column is selected for this category.
g. Expand the AWDatabase Policies category, and then review the policy settings.
h. Select the Subscribed check box for the AWDatabase Policies category, and then
click OK.
i. In Object Explorer, right-click the AdventureWorks database, and then click New
Query.
j. In the Query Editor, type the code in the following code example, and then click
Execute.
Note: This code is also available in the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PBM\EncryptedView.sql file.
USE AdventureWorks;
GO
CREATE VIEW TestEncrypt WITH ENCRYPTION
AS
SELECT Production.Product.Name,
Production.ProductReview.ReviewDate,
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps
Production.ProductReview.ReviewerName
FROM Production.Product INNER JOIN
Production.ProductReview
ON Production.Product.ProductID =
Production.ProductReview.ProductID
k. In Object Explorer, expand the AdventureWorks database, expand Views, and
then verify the creation of the view.
l. In Object Explorer, right-click the AdventureWorks database, and then click New
Query.
m. In the Query Editor, type the code in the following code example, and then click
Execute.
Note: This code is also available in the
C:\SQLHOLS\WhatsNewForDBAs\Starter\PBM\UnencryptedView.sql file.
USE AdventureWorks;
GO
CREATE VIEW TestNoEncrypt
AS
SELECT Production.Product.Name,
Production.ProductReview.ReviewDate,
Production.ProductReview.ReviewerName
FROM Production.Product INNER JOIN
Production.ProductReview
ON Production.Product.ProductID =
Production.ProductReview.ProductID
Note: The batch is aborted because it violates the Encrypt Views policy.
n. In Object Explorer, right-click (local), point to Policies, and then click View.
Notice that the Disable E-Mail policy setting is not enabled because the policy is
set for on-demand evaluation.
o. In the History column, click the History link, and then review the information that
is displayed. This history was created when you tested the policy earlier in the lab.
p. In the Log File Viewer dialog box, click Close.
q. In the View Policies - MIAMI dialog box, click Close.
r. Keep SQL Server Management Studio open for the next exercise.
Note: Although you can use the GUI to disable policies, it is also possible to use a set
of system stored procedures to define and create Policy-based Management
components. Books Online provides you with an overview of the system stored
procedures and metadata used by Policy-Based Management components that are
defined and stored in the MSDB database
What's New in Microsoft® SQL Server® 2008
No comments:
Post a Comment