Scripts to Enlist a Target Server to a Master Server and Central Management Server

Question

Is there a way to script the proccess described in the artical referenced below to Enlist a Target Server to a Master Server? 

How to: Enlist a Target Server to a Master Server (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/ms188710.aspx

Is there a way to script the proccess of adding multi-server jobs to a target server, once it has been enlisted?

Lastly,  Is there a way to scrip the proccess of adding a given server name to a Central Management Server?

Answer 1

1. To make a server a target server, you just execute below, on the target server:
EXEC msdb.dbo.sp_msx_enlist N'name_of_master_Server', N''

2: Use sp_add_jobserver.

3: Don't know, I'm afraid.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

Hi Michael W Swinarski,

For the third question, to register a SQL Server instance to Central Management Server by script, you may have a try to make use of SQL Server Powershell. Please pay attention to the example in this blog: Registering multiple servers in Central Management Server (CMS) using PowerShell Script.

---

Stephanie Lv

TechNet Community Support

Answer 3

Thank you for your suggestions.  I have started to work with the suggestions that have been mentioned, as well as some additional research and have come up with a powershell script that should answer my first two questions, and wanted to share what I came up with.  Note, have only limited testing at this point on SQL Server 2008, executed the script from sqlps:

 

#  Is there a way to script the proccess of Enlisting a Target Server to a Master Server?
#  Is there a way to script the proccess of adding multi-server jobs to a target server, once it has been enlisted?

 

param(
 [string]$srv=$null,
 [int]$interval=$null
 )

$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $srv                       
$inst = $m.ServerInstances | select @{Name="SrvName"; Expression={$m.Name}}, Name
$counter = 0
$srv=hostname

# Add all instances to MSX Server useing msdb.dbo.sp_msx_enlist

$i = 0
$inst | ForEach-Object {
 if ($_.Name -eq 'MSSQLSERVER') {
  $srvnm = $_.Name
  $_.Name = ""
  }
 else {
  $srvnm = 'MSSQL$' + $_.Name
  $_.Name = '\' + $_.Name
  }
 $stat = get-service -name $srvnm | select Status
 if ($stat.Status -eq 'Running') {
  $iname = $srvnm
  if ($iname -eq 'MSSQLSERVER') {
   $iname = 'SQLServer'
   }
  $locSrv = [string]$srv + [string]$_.Name
  $q = "EXEC [msdb].[dbo].[sp_msx_enlist]"
  $q = $q + " N'" + $MSXsrv + "', N''"
  #$res = Write-Host $q
  $res = invoke-sqlcmd -ServerInstance $locSrv -Database msdb -Query $q
  $InstID = $res.InstanceID
  $i += 1
  
  }
 }
$counter = $counter + 1
Start-Sleep -s $interval

########################################################################################################
# Add jobs to instance useing dbo.sp_add_jobserver
# There should be a section like this for each job
########################################################################################################

$m = New-Object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $srv                       
$inst = $m.ServerInstances | select @{Name="SrvName"; Expression={$m.Name}}, Name
$counter = 0
$srv=hostname

$MSXsrv = "YourServerName"
$MSXdb = "msdb"

$i = 0
$inst | ForEach-Object {
 if ($_.Name -eq 'MSSQLSERVER') {
  $srvnm = $_.Name
  $_.Name = ""
  }
 else {
  $srvnm = 'MSSQL$' + $_.Name
  $_.Name = '\' + $_.Name
  }
 $stat = get-service -name $srvnm | select Status
 if ($stat.Status -eq 'Running') {
  $iname = $srvnm
  if ($iname -eq 'MSSQLSERVER') {
   $iname = 'SQLServer'
   }
  $locSrv = [string]$srv + [string]$_.Name
  $q = "EXEC [msdb].[dbo].[sp_add_jobserver]"
  $q = $q + " @job_name = N'YourJobName',"
  $q = $q + " @server_name = N'" + $locSrv + "'"
  #$res = Write-Host $q
  $res = invoke-sqlcmd -ServerInstance $MSXsrv -Database msdb -Query $q
  $InstID = $res.InstanceID
  $i += 1
  
  }
 }
$counter = $counter + 1
Start-Sleep -s $interval

########################################################################################################
# End of section
########################################################################################################