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

    Domanda

  • 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?

    venerdì 17 febbraio 2012 19:25

Risposte

  • 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
    ########################################################################################################

    • Contrassegnato come risposta Stephanie Lv venerdì 24 febbraio 2012 03:24
    martedì 21 febbraio 2012 19:56

Tutte le risposte

  • 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

    sabato 18 febbraio 2012 11:49
  • 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

    lunedì 20 febbraio 2012 10:13
  • 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
    ########################################################################################################

    • Contrassegnato come risposta Stephanie Lv venerdì 24 febbraio 2012 03:24
    martedì 21 febbraio 2012 19:56