Configure the Master target server or multi server environment:
Multi
server administration requires that you set up a master server and one or more
target servers. Jobs that will be processed on all the target servers are first
defined on the master server and then downloaded to the target servers.
Automating
administration across multiple instances of SQL Server is called multi server
administration. Use multi server
administration to do the following:
·
Manage two or more
servers.
·
Schedule information
flows between enterprise servers for data warehousing.
To take advantage of
multi server administration, you must have at least one master server and at
least one target server. A master server distributes jobs to, and receives
events from, target servers. A master server also stores the central copy of
job definitions for jobs that are run on target servers. Target servers connect
periodically to the master server to update their schedule of jobs. If a new
job exists on the master server, the target server downloads the job. After the
target server completes the job, it reconnects to the master server and reports
the status of the job. The following illustration shows the relationship
between master and target servers:
If you administer
departmental servers across a large corporation, you can define the following:
·
One backup job with job
steps.
·
Operators to notify in
case of backup failure.
·
An execution schedule
for the backup job.
Write this backup job
one time on the master server and then enlist each departmental server as a
target server. From the time of their enlistment, all the departmental servers
run the same backup job, yet you defined the job only once.
NOTE: Multiserver administration
features are intended for members of the sysadmin role. However, a member of
the sysadmin role on the target server cannot edit the operations that are
performed on the target server by the master server. This security measure
prevents job steps from being accidentally deleted and operations on the
target server from being interrupted.
|
Configure the Master target server
Here’s a basic look at how to setup Multi Server
Administration. First thing I’m going to do is register both servers in SQL Server Management Studio (SSMS). If you do
not see the Registered Servers tab in SSMS navigate to 'View' | 'Registered
Servers' or press 'Ctrl + Alt + G'.
Once both servers are registered I can start configuring
Multi Server Administration. To do this right click on 'SQL Server Agent' on
your master server and choose 'Multi Server Administration' | 'Make this a
Master…' to begin the process.
Click Next on the Welcome for the Master Server Wizard.
The next screen is where we can configure an operator. Just
like when we create an operator for local jobs, we can create one for our Multi
Server jobs. You can also leave this section blank if you prefer not to have an
operator although I would not recommended this practice. Once completed,
press the 'Next' button to continue.
On the Target Servers screen is where we specify our target
servers. You can have multiple targets, but for this example I’m only using
one. You can select target servers using your 'Registered Servers' from
your SSMS session or you can add a connection by clicking the 'Add
Connection...' button. I’m going to select Mirror as my target and click
the 'Next' button to continue the process.
Next, SQL Server will check that the versions of the master
and target server are compatible as shown on the screen shot below.
On the Master Server Login Credentials screen security is
checked between the master and target servers. If needed, the check box can be
selected to create a login and assign it rights to the master server. Click the
'Next' button to continue the process.
Once you click the 'Finish' button on the Complete the
Wizard screen it will start the configuring the servers.
Be aware that a common problem exists during configuration
and the following error (The enlist operation failed (reason: SQLServerAgent
Error: The target server cannot establish an encrypted connection to the master
server 'Server Name'. Make sure that the MsxEncryptChannelOptions
registry subkey is set correctly on the target server.) (Microsoft SQL
Server Error: 22026)) may be thrown as shown on the screen shot below:
As the error indicates, we need to make a registry change on
the target server. To accomplish this, logon to the target server and navigate
to 'Start' | 'Run' | type 'Regedit'. As a best practice, be sure to backup your
registry before making changes. From the Registry Editor browse to
'HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\<Your
instance>\SQLServerAgent and change the MsxEncryptChannelOptions' value to
'0'. Save the change and close the Registry editor.
Once this is complete, run the wizard from the master server
again and it should finish successfully as shown below.
To verify everything was configured correctly, connect to
both servers and beside SQL Server Agent it should have either '(MSX)' or
'TSX:<master server>' as shown below.
To set up a Multi Server Job under SQL Server Agent you must
connect to the master server in SSMS and navigate to 'SQL Server Agent' |
'Jobs' | 'Multi-Server Jobs". Configure the job as needed and on the
'Targets tab' select the target servers you want this job to execute on.
Once finished, click the 'OK' button to save job to both
servers.
The same general process applies to Maintenance Plans,
connect to the master server and navigate to 'Management' | 'Maintenance Plans'
| right click and choose 'New Maintenance Plan…'. This process starts by
naming the job as shown below.
Configure the Maintenance Plan as needed and on the Servers
button select the servers you want this Maintenance Plan to execute on.
Once finished, click 'Save and Close' to save both
Maintenance Plans to both servers as shown below.
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 Target. The Target Server Wizard guides you through the process of making a
target server.
1.
In Object Explorer, expand a server that is configured as a master
server.
2.
Right-click SQL Server Agent, point to Multi Server Administration, and then click Add Target Servers.
3.
Complete the Target
Server Wizard, which guides you through the process.
1.
In Object Explorer, expand a server that is configured as a target
server.
2.
Right-click SQL Server Agent, point to Multi Server Administration, and then click Defect.
3.
Click Yes to confirm that you want to defect this target
server from a master server.
1.
In Object Explorer, expand a server that is configured as a master
server.
2.
Right-click SQL Server Agent, point to Multi Server Administration, and then click Manage Target Servers.
3.
Click Post Instructions, and then in the Instruction type list, select Defect.
4.
Under Recipients, do one of the following:
·
Click All target servers to defect all target servers of this master
server. Use this option if you want to completely uninstall the current
multiserver administration configuration.
·
Click These target servers, and then click the corresponding Select box, to defect some, but not all, target
servers of this master server.
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 sub key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQL Server Agent\AllowDownloadedJobsToMatchProxyName (REG_DWORD) is set to 1 (true). By default,
this sub key 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 sub key 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.
Secure Sockets Layer (SSL)
Full Secure Sockets Layer (SSL) encryption and certificate
validation are enabled for connections between master servers and target
servers by default.
The wizard takes you
through the following steps:
·
Checking the security
settings for the SQL Server Agent service and the SQL Server service on all
servers intended to become target servers.
We recommend that both services should be running in Microsoft Windows domain
accounts.
·
Creating a master server
operator (MSXOperator) on the master server.
MSXOperator is the only operator that can receive notifications for multiserver
jobs.
·
Starting the SQL Server
Agent service on the master server.
·
Enlisting one or more
servers as target servers.
If you have a large
number of target servers, avoid defining your master server on a production
server. Otherwise, target server traffic can slow performance on your
production server. If you also forward events to a dedicated master server, you
can centralize administration on one server.
Note: I collected the above information from different links. If you have any doubts on the above topic, please feel free to post the question, i will try to get the answer for your question.
HAPPY LEARNING
