使用基于SQL Server策略的管理来实施SQL数据库最佳实践

最新推荐文章于 2021-04-25 17:27:50 发布

culuo4781

最新推荐文章于 2021-04-25 17:27:50 发布

阅读量328

点赞数

文章标签：数据库 java python 大数据 spring

原文链接：https://www.sqlshack.com/enforce-sql-database-best-practices-using-sql-server-policy-based-management/

版权

In this article, I am going to explain how we can enforce the SQL database best practices using Policy-Based Management. The policy-based management feature of SQL Server was introduced in SQL Server 2005. This feature was useful because it helps database administrators to define and enforce the database policies based on the organizations’ requirements.

在本文中，我将解释如何使用基于策略的管理来实施SQL数据库最佳实践。 SQL Server的基于策略的管理功能是在SQL Server 2005中引入的。此功能很有用，因为它可以帮助数据库管理员根据组织的要求定义和实施数据库策略。

The SQL Server policy-based management has the below components:

SQL Server基于策略的管理具有以下组件：

Facets
刻面
Conditions
条件
Target
目标
Evaluation Mode
评估模式

Facets are the predefined policies that describe the functionality of the SQL Objects. Facets are evaluated and manage the SQL Object. For example, Database Options is called a facet. There are 74 predefined facets.

构面是描述SQL对象功能的预定义策略。评估方面并管理SQL对象。例如，数据库选项称为构面。有74个预定义的构面。

Conditions are the state of the facets’ properties, which is evaluated. For example, 140 is the value of the compatibility level property of the SQL Database.

条件是构面属性的状态，将对其进行评估。例如，140是SQL数据库的兼容性级别属性的值。

Targets are the type of objects that are managed and evaluated.

目标是被管理和评估的对象的类型。

Evaluation mode determines how the policy will be evaluated by comparing the value defined in the setting with the actual value of a setting. If condition value matches the actual values of the setting, then the policy evaluated as true, or it will be evaluated as false. You can perform the evaluation manually (OnDemand), or it can be scheduled.

评估模式通过将设置中定义的值与设置的实际值进行比较来确定如何评估策略。如果条件值与设置的实际值匹配，则策略评估为true，否则将评估为false。您可以手动执行评估（按需），也可以安排评估。

You can read more about policy-based management in this article, SQL Server policy-based management.

您可以在本文SQL Server基于策略的管理中阅读有关基于策略的管理的更多信息。

业务需求 (Business Requirements)

Before a few months, my manager asked me to define a specific database policy. The purpose was to make the configuration of the SQL database uniform across all the servers. The following configuration parameters of the database must be uniform across all the servers.

几个月前，经理要求我定义特定的数据库策略。目的是使所有服务器上SQL数据库配置统一。数据库的以下配置参数在所有服务器上必须统一。

The recovery model of the user database must be FULL.
用户数据库的恢复模型必须为FULL。
The Auto close and auto shrink must be disabled.
必须禁用自动关闭和自动收缩功能。
The page verification option must be CHECKSUM.
页面验证选项必须为CHECKSUM。

The policy must be evaluated every Monday at 7:00 AM.

该策略必须在每个星期一的7:00 AM进行评估。

To configure the policy based on the business requirements, Open SSMS and connect to the SQL Server instance Expand Management Expand Policy Management Right-click on Policy Select New Policy. See the following image:

要基于业务需求配置策略，请打开SSMS并连接到SQL Server实例。展开管理展开策略管理右键单击Policy选择New Policy 。见下图：

On Create New Policy dialog box, provide the appropriate database policy name in the Name text box. To configure the condition, click on Check Condition drop-down box and select New Condition. See the following image:

在“ 创建新策略”对话框中，在“ 名称”文本框中提供适当的数据库策略名称。要配置条件，请单击检查条件下拉框，然后选择新建条件 。见下图：

On Create New Condition dialog box, enter the appropriate name of the condition in the Name text box. As I mentioned, SQL Server has 74 predefined facets. We want to configure the properties of the SQL database hence choose Database from Facets drop-down box. See the following image:

在“ 创建新条件”对话框中，在“ 名称”文本框中输入条件的适当名称。如前所述，SQL Server具有74个预定义的方面 。我们要配置SQL数据库的属性，因此从Facets下拉框中选择Database 。见下图：

Now, in the expression grid view, we will define the conditions. It is compared with the actual value of the target. Now, the first condition is that the recovery model of the SQL database should be FULL, so choose the @recoverymodel from the field column of the grid view. The operator should be equal to (=) and from the value column, choose FULL. See the following image:

现在，在表达式网格视图中，我们将定义条件。将其与目标的实际值进行比较。现在，第一个条件是SQL数据库的恢复模型应为FULL ，因此请从网格视图的字段列中选择@recoverymodel 。运算符应等于（=），然后从值列中选择FULL 。见下图：

The next condition is that Auto Close and Auto Shrink must be disabled on the SQL database. To configure the condition, choose AND in the AndOr column, choose @AutoShrink from Field column, choose equal to (=) from the operator column and choose False in the value column. Similarly, choose @AutoClose from Field column, choose equal to (=) from the operator column and choose False in the value column. See the following image:

下一个条件是必须在SQL数据库上禁用“ 自动关闭”和“ 自动收缩” 。要配置的条件下，选择与在ANDOR列中，从字段列选择@AutoShrink，选择从操作者柱等于（=），并选择在值列假。同样，从“字段”列中选择@AutoClose ，从运算符列中选择等于（=） ，然后在值列中选择“假”。见下图：

The next condition is that Page verification of the database must be CHECKSUM; hence choose @Pageverify from Field column, choose equal to (=) from the operator column and choose CHECKSUM in the value column. See the following image:

下一个条件是数据库的Page验证必须为CHECKSUM。 因此，请从“ 字段”列中选择@Pageverify ，从运算符列中选择等于（=） ，然后在值列中选择“ CHECKSUM ”。见下图：

We want to evaluate the condition only on the customer database; hence choose the database to choose @id from field column, choose greater than (>) in the Operator column and enter 5 in the Value column. See the following image:

我们只想在客户数据库上评估条件；因此，请选择数据库以从字段列中选择@id ，在“ 运算符”列中选择大于（>） ，然后在“ 值”列中输入5 。见下图：

Once all conditions are defined, click OK to save the condition and close the dialog box.

定义所有条件后，单击“确定”以保存条件并关闭对话框。

On Create New Policy dialog box, choose Check configuration parameter from Check condition drop-down box. Now, we want to run this policy against the databases hence choose the Check configuration parameters in the “Against targets” textbox. See the following image:

在“创建新策略”对话框中，从“检查条件”下拉框中选择“ 检查配置参数 ”。现在，我们要对数据库运行此策略，因此在“针对目标”文本框中选择“ 检查配置”参数 。见下图：

As per business requirements, the policy should be evaluated at 7 AM every Monday. To configure the schedule, accordingly, choose On schedule from the evaluation mode. To define the schedule, click on New. See the following image:

根据业务需求，应在每个星期一的 上午7点评估该策略。要配置计划，请相应地从评估模式中选择按计划 。要定义时间表，请点击新建。见下图：

On the New Job schedule dialog box, enter the appropriate name of the schedule, From the occurs drop-down box, select Weekly and From the list of the days click on Monday. In text box named Occurs once at, enter 7:00:00. The schedule has been created, click on OK to save the schedule and close the dialog box. See the following image:

在“ 新作业计划”对话框上，输入计划的适当名称，从“ 发生”下拉框中选择“每周”，然后从天数列表中单击“星期一”。在名为“ 一次发生”的文本框中，输入7:00:00 。时间表已创建，单击“ 确定”以保存时间表并关闭对话框。见下图：

On Create New Policy screen, you can see that the policy evaluation schedule is assigned automatically. See the following image:

在“ 创建新策略”屏幕上，您可以看到策略评估计划已自动分配。见下图：

Click OK to save the policy and close the dialog box. In the SQL Server management studio, you can view the policy under Policy Management and conditions under conditions. See the following image:

单击“确定”保存策略并关闭对话框。在SQL Server Management Studio中，您可以查看条件下下的策略管理和条件S政策。见下图：

Now let us test the policy. To do that, right-click on the policy and select Evaluate. See the following image:

现在让我们测试该政策。为此，请右键单击该策略，然后选择“ 评估” 。见下图：

In the Evaluate Policies dialog box, you can see the result of the execution of the policy. To view detailed information on policy evaluation, click on View in Details column of the grid. See the following image:

在“评估策略”对话框中，您可以看到策略执行的结果。要查看有关策略评估的详细信息，请单击表格的“详细信息”列中的“查看”。见下图：

In Results Details View dialog box, you can see the list of the conditions, its expected value and actual value. See the following image:

在“ 结果详细信息视图”对话框中，您可以看到条件列表，其期望值和实际值。见下图：

As you can see, all the conditions defined in the policy are satisfied, hence the policy evaluation is successful.

如您所见，策略中定义的所有条件均已满足，因此策略评估成功。

摘要 (Summary)

In this article, I have explained about policy-based management and how it can be used to define and enforce the SQL database policies based on the organizations’ requirement.

在本文中，我已经解释了基于策略的管理以及如何将其用于根据组织的要求定义和实施SQL数据库策略。