How to: Make a Master Server (SQL Server Management Studio)

This topic describes how to make a master server using the Master Server Wizard.

To make a master server

  1. In Object Explorer, connect to an instance of the Microsoft SQL Server Database Engine, and then expand that instance.

  2. 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.

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:

  • 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). 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.

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."
    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.

See Also

Concepts

Creating a Multiserver Environment
Automating Administration Across an Enterprise

Help and Information

Getting SQL Server 2005 Assistance