设置主服务器Make a Master Server

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

重要

Azure SQL 托管实例目前支持大多数(但不是所有)SQL Server 代理功能。On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. 有关详细信息,请参阅 Azure SQL 托管实例与 SQL Server 的 T-SQL 区别See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

本主题描述如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio 设置主服务器 Transact-SQLTransact-SQLThis topic describes how to make a master server SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

安全性Security

如果分布式作业的步骤与某个代理相关联,则该作业将在目标服务器上该代理帐户的上下文下运行。Distributed jobs that have steps which are associated with a proxy run under the context of the proxy account on the target server. 请确保满足以下条件,否则与代理关联的作业步骤将不会从主服务器下载到目标服务器上:Make sure that the following conditions are met or job steps that are associated with a proxy will not be downloaded from the master server to the target:

  • 主服务器注册表子项 \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<*instance_name*>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) 设置为 1 (true)。The master server registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<*instance_name*>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) is set to 1 (true). 默认情况下,此子项设置为 0 (False)。By default, this subkey is set to 0 (false).

  • 目标服务器上已存在与运行作业步骤的主服务器代理帐户同名的代理帐户。A proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

从主服务器将使用代理帐户的作业步骤下载到目标服务器时,如果作业步骤失败,可以检查 msdb 数据库中 sysdownloadlist 表的 error_message 列是否存在以下错误消息:If job steps that use proxy accounts fail when downloading them from the master server to the target server, you can check the error_message column in the sysdownloadlist table in the msdb database for the following error messages:

  • “该作业步骤需要代理帐户,但是目标服务器上禁用了代理匹配功能。”"The job step requires a proxy account, however proxy matching is disabled on the target server."

    若要解决此错误,请将 AllowDownloadedJobsToMatchProxyName 注册表子项设置为 1。To resolve this error, set the AllowDownloadedJobsToMatchProxyName registry subkey to 1.

  • “找不到代理。”"Proxy not found."

    若要解决此错误,请确保目标服务器上已存在与运行作业步骤的主服务器代理帐户同名的代理帐户。To resolve this error, make sure a proxy account exists on the target server that has the same name as the master server proxy account under which the job step runs.

权限Permissions

默认情况下授予 sysadmin 固定服务器角色的成员执行此过程的权限。Permissions to execute this procedure default to members of the sysadmin fixed server role.

使用 SQL Server Management StudioUsing SQL Server Management Studio

设置主服务器To make a master server

  1. 在“对象资源管理器”中,连接到 MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine, and then expand that instance.

  2. 右键单击“SQL Server 代理”,指向“多服务器管理”,再单击“将其设置为主服务器”。Right-click SQL Server Agent, point to Multi Server Administration, and then click Make this a Master. 主服务器向导 将引导您完成设置主服务器和添加目标服务器的过程。The Master Server Wizard guides you through the process of making a master server and adding target servers.

  3. 从“主服务器操作员”中,配置主服务器的操作员。若要通过电子邮件或寻呼程序向操作员发送通知,必须配置 SQL ServerSQL Server 代理以发送电子邮件。From the Master Server Operator page, configure an operator for the master server To send notifications to operators by using e-mail or pagers, SQL ServerSQL Server Agent must be configured to send e-mail. 若要使用 net send 向操作员发送通知,必须在 SQL ServerSQL Server 代理所在的服务器上运行 Messenger 服务。To send notifications to operators by using net send, the Messenger service must be running on the server where SQL ServerSQL Server Agent resides.

    电子邮件地址E-mail address
    设置操作员的电子邮件地址。Sets the e-mail address for the operator.

    寻呼地址Pager address
    设置操作员的寻呼电子邮件地址。Sets the pager e-mail address for the operator.

    Net send 地址Net send address
    设置操作员的 net send 地址。Sets the net send address for the operator.

  4. “目标服务器” 页中,为主服务器选择目标服务器。From the Target Server page, select target servers for the master server.

    已注册的服务器Registered Servers
    列出已在 Microsoft SQL Server Management StudioSQL Server Management Studio 中注册但尚未成为目标服务器的服务器。Lists the servers registered in Microsoft SQL Server Management StudioSQL Server Management Studio that are not already target servers.

    目标服务器Target Servers
    列出已经为目标服务器的服务器。Lists the servers that are target servers.

    >
    将所选服务器移动到目标服务器列表中。Move the selected server to the target server list.

    >>
    将所有服务器移动到目标服务器列表中。Move all servers to the target server list.

    <
    从目标服务器列表中删除所选服务器。Remove the selected server from the target server list.

    <<
    从目标服务器列表中删除所有服务器。Remove all servers from the target server list.

    添加连接Add connection
    向目标服务器列表中添加服务器,但不注册该服务器。Add a server to the target server list without registering the server.

    ConnectionConnection
    更改所选服务器的连接属性。Change the connection properties for the selected server.

  5. “主服务器登录凭据” 页中,指定是否要在必要时为目标服务器创建新的登录名,并为其分配针对主服务器的权限。From the Master Server Login Credentials page to specify if you want to create a new login for the target server, if necessary, and assign it rights to the master server.

    在必要时创建新登录名,并为其分配针对 MSX 的权限Create a new login if necessary and assign it rights to the MSX
    如果指定的登录名不存在,则在目标服务器上创建新登录名。Create a new login on the target server if the login specified does not already exist.

使用 Transact-SQLUsing Transact-SQL

设置主服务器To make a master server

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 本示例将当前服务器登记到 AdventureWorks1 主服务器中。This example enlists the current server into the AdventureWorks1 master server. 当前服务器的位置是 Building 21、Room 309、Rack 5。The location for the current server is Building 21, Room 309, Rack 5.

USE msdb ;  
GO  
  
EXEC dbo.sp_msx_enlist N'AdventureWorks1',   
    N'Building 21, Room 309, Rack 5' ;   
GO;  

有关详细信息,请参阅 sp_msx_enlist (Transact-SQL)For more information, see sp_msx_enlist (Transact-SQL).

另请参阅See Also

创建多服务器环境Create a Multiserver Environment
企业范围的自动化管理Automated Administration Across an Enterprise