快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

使用基于策略的管理检查SQL Server

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

SQL Server的一项功能是基于策略的管理,它是一种灵活的工具,可以帮助DBA管理一个或多个SQL Server实例。它用于在整个组织中监视和实施SQL Server的标准策略集。尽管有很多内置条件可供选择,但Dennes演示了如何设置自定义策略以及标准策略。查看许多服务器上的策略状态可能很繁琐,因此他还解释了DBA如何一眼就能评估多个服务器的状态。

基于策略的管理(PBM)使我们能够对服务器进行合规性检查。我们可以为服务器建立策略,并对它们执行定期的计划检查。SQL Server允许我们使用PBM来检查服务器中所有数据库的所有表的配置。

为了说明PBM,我将展示如何构建两个不同的策略:首先,我们将检查数据库以查看它们是否启用了“自动收缩”选项,这与当前策略背道而驰。这只是使用PBM的内置功能可能要检查的众多策略中的一种简单策略。

但是,PBM的主要功能来自能够创建任何类型的自定义策略。我将使用上一篇文章中说明的问题示例。对于具有varchar(max)字段的表,这涉及使用属性“行外的大值类型”的重要性。我们将需要为该策略创建一个自定义查询,以检查是否需要该属性的所有表都已启用它。

PBM使用基于现有Facet对象和Policy对象的Condition对象。条件是,顾名思义,将在策略的检查过程中执行的标准。构是预定义的对象,可从服务器中的几种类型的对象中恢复信息。它们用于建立条件。反过来,政策将条件汇总起来,决定如何使用其结果。

例如,一种检查我们表中内容的策略可以使用一种条件来决定要检查哪些服务器,另一种可以决定要检查哪些数据库,另外一个可以决定要检查哪些表并最终根据条件来检查桌子。

一个非常常见的模式是创建条件,以避免针对系统数据库和系统表进行策略检查。让我们逐步完成此过程。

避免系统数据库

为此,我们需要两个条件:一个检查系统数据库,另一个检查系统表。开始吧:

  1. 使用SSMS,右键单击“管理”->“策略管理”下方的“条件”文件夹,然后单击“新条件”菜单项以打开“创建新条件”窗口。使用基于策略的管理检查SQL Server
  2. “创建新条件”窗口中,在“名称”文本框中键入“非系统数据库”。这将是我们第一个条件的名称
  3. “创建新条件”窗口的“ Facet”下拉列表中,选择“数据库”,这样我们就可以针对数据库属性创建条件。使用基于策略的管理检查SQL Server

    每个“ Facet”都有几个字段。每个字段代表我们可以在对象中检查的信息。有在现场名为“数据库”小“@IsSytemObject”,我们可以用它来识别数据库是否是系统数据库。在我们的例子中,如果数据库不是系统数据库,则需要条件为true,因此策略检查将继续

  4. “创建新条件”窗口的“表达式”网格内的“ 字段”列中,选择“ @IsSystemObject”使用基于策略的管理检查SQL Server
  5. 选择“=”“操作”列内“表达”网格
  6. “表达式”网格 内的值”列中选择“假”使用基于策略的管理检查SQL Server
  7. “创建新条件”窗口中,单击“确定”按钮

创建“自动收缩”条件

我们第一个策略的主要条件是需要检查“自动收缩”数据库选项是否已启用。让我们来构建它:

  1. 使用SSMS,右键单击“管理”->“策略管理”下方的“条件”文件夹,然后单击“新条件”菜单项以打开“创建新条件”窗口。
  2. “创建新条件”窗口中,在“名称”文本框中键入“ AutoShrink”:这将是我们第一个条件的名称
  3. “创建新条件”窗口的“ Facet”下拉列表中,选择“数据库”,这样我们就可以针对数据库属性创建条件。
  4. “创建新条件”窗口的“表达式”网格内的“ 字段”列中,选择“ @AutoShrink”
  5. 选择“=”“操作”列内“表达”网格
  6. “表达式”网格内的值”列中选择“假” 。应禁用“自动收缩”选项,如果“ @AutoShrink”为假,则我们的策略将通过。使用基于策略的管理检查SQL Server
  7. “创建新条件”窗口中,单击“确定”按钮到目前为止,我们创建的两个条件将出现在SSMS中,如下图所示:

    使用基于策略的管理检查SQL Server

创建第一个策略:检查“自动收缩”选项

让我们在新政策中使用我们的条件。

  1. 右键单击“管理”->“策略管理”下的“策略”文件夹,然后单击“新策略”菜单项以打开“创建新策略”窗口。
  2. “创建新策略”窗口中,在“名称”文本框中键入“ AutoShrink”,这将是我们第一个策略的名称
  3. “检查条件”下拉列表中,选择“自动收缩”,这是我们政策的主要条件使用基于策略的管理检查SQL Server
  4. “针对目标”框中,单击“每个数据库”旁边的箭头,然后选择“非系统数据库”,这样就不会为系统数据库检查我们的策略。使用基于策略的管理检查SQL Server
  5. 点击“确定”按钮
  6. 右键单击“自动收缩”策略,然后单击“评估”上下文菜单项
  7. 检查结果,您将能够查看是否有任何数据库启用了“自动收缩”选项,然后单击“确定”按钮。使用基于策略的管理检查SQL Server

避免对系统表进行策略检查

我们仅需要一个条件,该条件将过滤将检查其政策的表。我们不希望我们的策略检查系统表。

让我们将新条件命名为‘Not System table’。该条件将使用“表”构面,因为将执行该条件以检查每个表。在“表”方面也有“@IsSystemObject”领域,所以我们可以在我们与以前的状态也做了同样的方式来使用它。

  1. 右键单击“管理”->“策略管理”下的“条件”文件夹,然后单击“新条件”菜单项以打开“创建新条件”窗口。
  2. “创建新条件”窗口中,在“名称”文本框中键入“非系统表”,这将是我们第一个条件的名称
  3. “创建新条件”窗口的“ Facet”下拉列表中,选择“表”,这样我们就可以针对数据库属性创建条件。
  4. “创建新条件”窗口的“表达式”网格内的“ 字段”列中,选择“ @IsSystemObject”
  5. 选择“=”“操作”列内“表达”网格
  6. “表达式”网格内的值”列中选择“假”
  7. “创建新条件”窗口中,单击“确定”按钮

检查表中的“大数值超出行数”属性

我们的第三个条件将成为我们第二个政策的主要条件。我们的政策将检查表格是否启用了“大值类型超出行”属性,因此我们需要使用“表格”构面。但是,“大数值类型不在行中”属性没有字段。在这种情况下,代替选择字段,我们需要使用带有一些特殊功能的自定义表达式。

构建自定义表达式的第一步是创建一个查询,该查询返回一个要由策略检查的值。通常,我们必须在同一查询中混合使用几种检查,并返回一个通常由策略检查的值,通常为‘0’‘1’

在我们的示例中,如果该表具有“ varchar(max)”字段,并且禁用了“大值类型超出行”属性,则它将失败检查(结果为“ 0”):否则,它将通过检查(结果是1′)。我们可以在子查询中使用“ case”语句来获得这些结果。

该查询将由PBM表达式中使用的ExecuteSQL函数执行。PBM框架将为每个表执行一次查询,并将传递查询参数,因此我们可以在执行期间识别表。

让我们分几步构建查询以更好地了解结果:

  1. 第一步:以下查询将产生具有varchar(max)字段的表列表
  2. 第二步:让我们使用ExecuteSQL函数中可用的参数过滤单个表的结果

    您将注意到,我在表达式中使用了一个名为“ @@ ObjectName”的变量。PBM将用“ sys.objects” DMV 中的对象名称替换此变量。这很重要:当‘sys.objects’DMV中不存在该对象时,该变量不存在。由于我们正在为表建立条件,因此该问题不会影响我们的示例。

  3. 第三步:检查是否禁用了“ large_value_types_out_of_row”属性。如果表具有varchar(max)列,并且禁用了“ large_value_types_out_of_row”属性,则该表不应通过策略检查。
  4. 第四步:如果查询结果返回了任何行,则最终结果应为0:如果没有返回结果,则最终结果将为1。因此,“ 0”表示存在问题的表, “ 1”表示一切正常。我们需要注意查询结果的含义以及我们将如何构建策略。

现在有了用于条件的查询,我们可以构建自定义表达式。

  1. 右键单击“管理”->“策略管理”下的“条件”文件夹,然后单击“新条件”菜单项以打开“创建新条件”窗口。
  2. “创建新条件”窗口中,在“名称”文本框中键入“ outofrow”,这将是我们第一个条件的名称
  3. “创建新条件”窗口的“ Facet”下拉列表中,选择“表”,这样我们的条件将对每个表执行一次。
  4. “创建新条件”窗口中,单击“字段”列旁边的按钮“…”
  5. “高级编辑”窗口中,键入以下表达式:ExecuteSql(’Numeric’,’在以下情况下选择大小写(从sys.tables中选择名称,如果存在(从sys.columns c中选择1,其中user_type_id = 167且max_length = -1和c.object_id = t.object_id),并且name = @ @ObjectName和large_value_types_out_of_row = 0)不为null,则为0,否则为1 end’)

    进行此工作最令人讨厌的部分是,您需要将整个查询作为字符串表达式写在一行中。该表达式使用函数ExecuteSQL执行查询并返回数字值。

    使用基于策略的管理检查SQL Server

  6. “高级编辑”窗口中单击“确定”按钮
  7. 选择“=”“操作”列内“表达”网格
  8. 类型“1”中的“值 ”列中的内部“表达”网格如前所述,根据查询的含义选择正确的值很重要。

    使用基于策略的管理检查SQL Server

  9. “创建新条件”窗口中,单击“确定”按钮

制定第二项政策

最后,在创建了我们所需的条件之后,该创建策略了。让我们一步一步地做。

  1. 右键单击“管理”->“策略管理”下的“策略”文件夹,然后单击“新策略”菜单项以打开“创建新策略”窗口。
  2. “创建新策略”窗口的“检查条件”组合框中,选择“行中的大值”
  3. “反对目标”框中,打开“数据库”对象旁边的菜单,然后选择“非系统数据库”菜单项
  4. “反对目标”框中,打开“表”对象旁边的菜单,然后选择“非系统表”菜单项
  5. “名称”文本框中,键入“行中的较大值”作为策略的名称。使用基于策略的管理检查SQL Server
  6. 点击“确定”按钮
  7. 右键单击新的“检查大价值”政策
  8. 点击“评估”菜单项使用基于策略的管理检查SQL Server
  9. “评估策略”窗口中,单击“评估”按钮
  10. “政策评估安全通知”窗口中,单击“运行”按钮。自定义条件始终在执行前生成警报。例如,如果从Internet导入自定义条件,则需要确保自定义代码是可信任的。使用基于策略的管理检查SQL Server

    过一会儿,您将在“目标详细信息”框中看到结果,指向所有需要启用“大值类型超出行”并且不符合我们的新政策的表。

    使用基于策略的管理检查SQL Server

政策管理与执行

到现在为止,我们创建了策略并根据需要执行它们。PBM还允许我们使用SQL Server代理以计划的配置执行策略。

编辑策略属性,可以将“评估模式”组合框更改为“计划的”。在同一窗口中,您可以单击“选择”按钮以选择预定义的计划,也可以单击“新建”按钮以创建新的自定义计划,这两个选项都将在SQL Server代理中创建一个新作业。

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

可以启用或禁用所有具有“计划的”评估模式的策略。如果启用了策略,则启用该作业,并将根据计划执行该作业;否则,该作业将被禁用。通过右键单击该策略,可以找到“启用”“禁用”菜单项。

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

您随时可以右键单击该策略并检查执行历史记录,以确保服务器一切正常。

使用基于策略的管理检查SQL Server

管理多个服务器

将PBM从仅仅是一个有趣的功能转变为重要的合规资源的原因是,您可以在多个服务器上执行策略。

通过使用PBM,我们可以管理数十个甚至数百个SQL Server,以确保服务器符合我们选择的所有配置规则。

跨多个服务器的策略检查有几种解决方案:

手动执行:

我们可以右键单击要评估的服务器,然后在上下文菜单中单击“策略” -> “评估”。我们选择创建策略的服务器,选择策略并执行。

我们可以将所有策略保存在单个服务器中,并在我们想要的任何服务器中执行它们。

使用基于策略的管理检查SQL Server

导入策略:

通过右键单击策略,可以将策略导出到文件。通过右键单击“ Policies”文件夹,可以将策略导入服务器。您可以将策略导出/导入到所有公司服务器,并为每个服务器创建执行计划。

导出策略时,该策略的所有条件都一起导出到一个XML文件中,我们可以将其导入到其他服务器中。

使用基于策略的管理检查SQL Server

使用基于策略的管理检查SQL Server

使用“注册服务器”窗口:

“注册服务器”窗口用于管理多个服务器。尽管“对象资源管理器”窗口允许我们将策略导入到一台服务器或针对一台服务器执行策略,但我们可以使用“注册服务器”窗口创建一组服务器,然后一次导入或执行策略。

使用“注册服务器”窗口,我们甚至可以根据一个共同的要求创建几组不同的服务器:例如,每个国家的服务器一组,检查每一组中不同的合规性规则。

使用基于策略的管理检查SQL Server

使用企业策略管理:

企业策略管理(EPM)是一个公共项目,使用PowerShell,自定义数据库和一些报告来管理策略在多台服务器上的执行,并创建具有执行结果的报告。如果服务器太多,这是一个很好的解决方案。

您可以在此处下载企业策略管理框架

您可以在此处查看有关企业策略管理的文章

使用基于策略的管理检查SQL Server

使用PowerShell:

您可以创建自定义PowerShell解决方案,甚至自定义EPM PowerShell脚本,以管理策略执行。

预定义策略

SQL Server为我们提供了一些预定义的策略,总共50个数据库引擎策略以及一些其他控制外围应用配置的策略。

您可以在文件夹
C:\ Program Files(x86)\ Microsoft SQL Server \ 140 \ Tools \ Policies \ DatabaseEngine \ 1033中 找到这些策略,
最好将它们导入SQL Server,因为它们代表了重要的策略:例如,其中一种可用策略可以检查我在上一篇博客文章(此处)中描述的可信任问题。这种错误配置可能使SQL Server用户可以控制服务器,并且可用策略能够识别该漏洞。

小心使用SQL Server Management Studio版本

不幸的是,某些版本的SSMS存在一个奇怪的错误,当我们在表界面上执行带有自定义条件的策略时,会出现该错误。SSMS 2016最高版本为17.0时会发生这种情况。版本17.2已纠正该错误。

我在Microsoft Connect网站上注册了此错误,您可以在此处查看详细信息

结论

当您管理多个服务器时,能够创建自定义条件并在多个服务器上执行策略将对您大有帮助。PBM是完成任务的理想选择。

不必局限于检查配置,您可以创建条件来检查所有内容,包括业务规则,查询数据库中的数据。您的想象力是唯一的极限。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638