The SQL Server Policy Based Management feature provides an efficient way to declare certain rules and corresponding policies (e.g. to force Windows authentication mode on a SQL Server instance, to evaluate and prevent violations in object naming conventions, etc.). So far, we focused on a single SQL Server instance, the methods to setup and evaluate Policy Based Management policies on it.
SQL Server基于策略的管理功能提供了一种声明某些规则和相应策略的有效方法（例如，在SQL Server实例上强制Windows身份验证模式，评估和防止对象命名约定中的违规行为，等等）。 到目前为止，我们仅关注单个SQL Server实例，即在其上设置和评估基于策略的管理策略的方法。
In this article, we are going to explain how the Central Management Servers feature, introduced in the Standard and Enterprise Editions of SQL Server 2008, helps DBAs to maintain multiple SQL Server instances across an enterprise environment. The feature provides two major benefits when multiple SQL Server instances need to be affected by a specific action:
在本文中，我们将解释在SQL Server 2008的标准版和企业版中引入的中央管理服务器功能如何帮助DBA在整个企业环境中维护多个SQL Server实例。 当多个SQL Server实例需要通过特定操作受到影响时，此功能提供了两个主要好处：
- An execution of a specific T-SQL query against SQL Server instances registered within Central Management Servers 针对在中央管理服务器中注册SQL Server实例执行特定的T-SQL查询
- An evaluation of a set of policies on SQL Server instances registered within Central Management Servers. This is the benefit we are going to describe in further text 对在中央管理服务器中注册SQL Server实例的一组策略的评估。 这就是我们将在进一步的文字中描述的好处
创建中央管理服务器 (Creating a central management server )
It is advisable to have a dedicated SQL Server instance which will be used as the central management server. Note that central management servers support the registration of servers using the Windows authentication mode only. So, even DBAs can access the central management server, the access to its registered servers depends on the Windows authentication.
建议使用专用SQL Server实例作为中央管理服务器。 请注意，中央管理服务器仅支持使用Windows身份验证模式的服务器注册。 因此，即使DBA可以访问中央管理服务器，对其注册服务器的访问也取决于Windows身份验证。
There are two database roles in the msdb database that grant access to central management servers. The ServerGroupAdministratorRole role membership is required to manage the central management server and the ServerGroupReaderRole role membership to connect to the central management server.
msdb数据库中有两个数据库角色可授予对中央管理服务器的访问权限。 ServerGroupAdministratorRole角色成员资格是管理中央管理服务器所必需的，而ServerGroupReaderRole角色成员资格是连接到中央管理服务器所必需的。
To create a central management server:
要创建中央管理服务器：
- Registered Servers pane using the 查看”菜单打开“ View menu注册的服务器”窗格
- Database Engine node and click the 数据库引擎节点，然后从“ Register Central Management Server option from the 中央管理服务器”子节点上下文菜单中单击“ Central Management Servers sub-node context menu注册中央管理服务器”选项。
This will open the New Server Registration dialog. Use the Server name dropdown box to browse for SQL Server instance that will be used as the central management server (e.g. LENOVO\CMS). Type-in the name of the server the way you want it to be shown in the Registered Servers pane (e.g. CENTRAL SERVER).
这将打开“ 新服务器注册”对话框。 使用服务器名称下拉框浏览将用作中央管理服务器SQL Server实例（例如LENOVO \ CMS ）。 输入希望在“已注册服务器”窗格中显示的服务器名称 （例如CENTRAL SERVER ）。
- Test option to verify the connection and authentication on the central management server测试”选项来验证中央管理服务器上的连接和身份验证
Click Save to register the newly registered central management server within Central Management Servers.
单击“ 保存”以在“ 中央管理服务器”中注册新注册的中央管理服务器 。
The next step is to create a server group that will identify SQL Server instances manageable by the central management server. It is recommended to create multiple server groups which identify SQL Server instances (e.g. by their business role, such as production or development groups). One SQL Server instance can exist in multiple server groups if needed.
下一步是创建一个服务器组，该服务器组将标识可由中央管理服务器管理SQL Server实例。 建议创建多个服务器组来标识SQL Server实例（例如，按其业务角色，例如生产或开发组）。 如果需要，一个SQL Server实例可以存在于多个服务器组中。
To create a new server group under the CENTRAL SERVER and add SQL Server instances to the group:
要在“ 中央服务器”下创建一个新的服务器组并将SQL Server实例添加到该组：
- Select the New Server Group option from the CENTRAL SERVER context menu, type in the group name (e.g. Production Servers) and optional description
- 从CENTRAL SERVER上下文菜单中选择New Server Group选项，键入组名（例如Production Servers ）和可选说明
- Select the New Server Registration option from the Production Servers context menu. This will open the same dialog type we used for creating the central management server. In our example, we’ll register two SQL Server instances (e.g. REMOTE\PRIMARY and ACMECOMPANY\SECONDARY)
- 从生产服务器上下文菜单中选择新服务器注册选项。 这将打开与用于创建中央管理服务器的对话框类型相同的对话框。 在我们的示例中，我们将注册两个SQL Server实例（例如REMOTE \ PRIMARY和ACMECOMPANY \ SECONDARY ）
After the process of configuring the central management server, its group(s), and servers is finished, the Registered Servers pane will show the structure in a tree-like form:
完成中央管理服务器，其组和服务器的配置过程之后，“已注册服务器”窗格将以树状形式显示该结构：
The central management server stores its groups and servers registration details within the server itself. This way, you can simply register the existing central management server on other remote machine and instantly have access to its groups and registered servers.
中央管理服务器将其组和服务器注册详细信息存储在服务器本身内。 这样，您只需在其他远程计算机上注册现有的中央管理服务器，即可立即访问其组和已注册的服务器。
评估基于策略的管理策略 (Evaluating Policy Based Management policies)
Policies can exist on particular SQL Server instances or in a form of XML files (the format used for exporting once declared policies to a file system). Our goal is to evaluate Policy Based Management policies on multiple SQL Server instances, in our case on the instances managed by the CENTRAL SERVER management server. Note that the evaluation does not have to involve all instances, but only the ones in particular server group, or even single SQL Server instances. The central management server, server groups, and single instances provide the Evaluate Policies context menu option.
策略可以存在于特定SQL Server实例上，也可以以XML文件的形式（用于将已声明的策略导出到文件系统的格式）存在。 我们的目标是在多个SQL Server实例上评估基于策略的管理策略，在本例中是在CENTRAL SERVER管理服务器管理的实例上进行评估。 请注意，评估不必涉及所有实例，而仅涉及特定服务器组中的实例，甚至不必涉及单个SQL Server实例。 中央管理服务器，服务器组和单个实例提供“ 评估策略”上下文菜单选项。
To evaluate policies on a server group:
要评估服务器组上的策略：
- Select the Evaluate policies option from the Production Servers context menu
- 从生产服务器上下文菜单中选择评估策略选项
The Evaluate Policies dialog will open. Use the Source option to select the source of Policy Based Management policies. Use the Files option to browse for exported to XML policies, or the Server option to select a SQL Server instance which hosts declared policies we want to evaluate on instances in the Production Servers group.
将打开“ 评估策略”对话框。 使用“ 源”选项选择“基于策略的管理”策略的源。 使用“ 文件”选项浏览到导出到XML策略，或使用“ 服务器”选项选择一个SQL Server实例，该实例承载我们要在Production Servers组中的实例上评估的声明策略。
- OK to confirm the policies source selection确定以确认策略源选择
The Policies grid in the Evaluation Policies dialog will populate with policies from the source. Select the ones you want to evaluate against the servers group. In our example, we’ll select the PolicyServerAuthenticationMode policy (it checks whether the Windows Authentication mode is forced on a SQL Server instance).
“ 评估策略”对话框中的“ 策略”网格将填充源中的策略。 选择要针对服务器组评估的服务器。 在我们的示例中，我们将选择PolicyServerAuthenticationMode策略（它检查是否在SQL Server实例上强制执行Windows身份验证模式）。
Note that the Evaluation Policies dialog provides information the same way it does when used locally on SQL Server instances (e.g. the yellow exclamation icon in the screenshot warns that the PolicyCustomersCount contains scripts, and that it should be evaluated only if originates from a trustworthy source).
请注意，“ 评估策略”对话框提供的信息与在SQL Server实例上本地使用时所提供的信息相同（例如，屏幕截图中的黄色感叹号图标警告PolicyCustomersCount包含脚本，并且仅当来自可靠来源时才应对其进行评估） 。
- Click the Evaluate button to start the process of verifying whether the servers in the Production servers group comply with the selected policy
- 单击评估按钮以启动验证生产服务器组中的服务器是否符合所选策略的过程。
The Results tab in the Evaluation Policies dialog will show which SQL Server instances on which policy evaluation failed.
“ 评估策略”对话框中的“ 结果”选项卡将显示对其策略评估失败SQL Server实例。
Certain policy types, depending on the conditions used within, offer immediate reconfiguration of SQL Server instances (or other policy defined targets). To do that, check the box next to the server name and click Apply. In our case the LENOVO SQL Server instance will switch from the mixed (both Windows Authentication and SQL Server Authentication) mode to the Windows Authentication mode.
根据内部使用的条件，某些策略类型可立即重新配置SQL Server实例（或其他策略定义的目标）。 为此，请选中服务器名称旁边的框，然后单击“ 应用” 。 在我们的案例中， LENOVO SQL Server实例将从混合（Windows身份验证和SQL Server身份验证）模式切换到Windows身份验证模式。
In the described example, we have used the LENOVO\ONE SQL Server instance as the source for the policies we evaluated on the servers group. In order to improve centralized management of registered servers, you can use the central management server or the server groups as the policy source instead. Once the needed policies are tested (whatever their source is), the policies can be imported to the central management server or the particular server group which will perform the policies import to appropriate servers.
在所描述的示例中，我们将LENOVO \ ONE SQL Server实例用作我们在服务器组上评估的策略的源。 为了改善对注册服务器的集中管理，可以使用中央管理服务器或服务器组作为策略源。 一旦测试了所需的策略（无论其来源是什么），就可以将策略导入中央管理服务器或特定的服务器组，该服务器组将策略导入到适当的服务器。
扫一扫
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
