Search This Blog

Tuesday, May 23, 2017

Central Management Servers - HA

 

Recently setup a new Central management Server for SQL Server, whilst doing this I was hoping to use HA, however CMS stores its config in the msdb database, and as such is not able to be HA’d… Sad smile

So… I started looking at a way for me to utilise HA to achieve this.

In the end I came up with using Always On listeners and powershell script to sync between two servers.

Because you can’t add a CMS server to the list, I decided to create a very small Always on environment to just deal with the CMS. I am not going to go through how to setup Always on or how to setup named instances.

Two servers and one instance.

  • DEV-DB01\CMS
  • DEV-DB02\CMS.

The listener was set on

  • DEV-DBCMS

So by doing this DEV-DBCMS would point at whatever server was the primary server. Connecting SSMS to the DEV-DBCMS, would point it at either DEV-DB01 and DEV-DB02 changes made in CMS would only appear on the servers DEV-DBCMS was pointing at.

So I came up with a PowerShell script that will use the DEV-DBCMS as the source of truth (and this would be whatever the primary server was in the HA), and then sync to the other server (in fact it syncs to itself as while, but makes no changes as it will always match). The PowerShell script is run every 5 minutes and from what I can see is working very well. This is not ideal as there is the possibility that changes can be lost if a failover occurs before a change to CMS has sync’d, but that should not occur that often and the benefit of having the CMS always online in some manner is more important.

This script is a modification on some CMS scripts developed by Chrissy LeMaire

https://blog.netnerds.net/smo-recipes/central-management-server/

I used the following command in SQl Server job to run the powershell script on a schedule of 5 minutes. I had the same job running on both servers in the HA, with jobs staggered by 2 minutes.

SQL Server Job Command

powershell –ExecutionPolicy Bypass -Command C:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db01\cms; powershell –ExecutionPolicy Bypass -Command W:\Scripts\CMS_SyncToServer.ps1 dev-dbcms dev-db02\cms;

Powershell Script

param([string]$fromcms, [string]$tocms)

$cr = "`r`n"

Write-Host "######################################$cr" -ForegroundColor Red
Write-Host "Starting Sync for $fromcms to $tocms $cr" -ForegroundColor Red
Write-Host "######################################$cr" -ForegroundColor Red

#For SQL 2012 Microsoft.SqlServer.Smo.dll Client Tools SDK is needed
#For SQL 2012 Servers, Microsoft.SqlServer.Management.RegisteredServers.dll must be copied from an newer version of SQL Server (2016 etc)

#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'
#Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.RegisteredServers.dll'

#For older versions of powershell.
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.RegisteredServers")

$fromserver = New-Object Microsoft.SqlServer.Management.Smo.Server $fromcms
$toserver = New-Object Microsoft.SqlServer.Management.Smo.Server $tocms
 
try {
    $fromcmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($fromserver.ConnectionContext.SqlConnectionObject)
    $tocmstore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($toserver.ConnectionContext.SqlConnectionObject)
    }
catch { throw "Cannot access Central Management Servers$cr" }
 
Function Parse-ServerGroup($fromserverGroup, $toservergroup)
{
    # Remove Servers
    foreach ($instance in $toservergroup.RegisteredServers)
    {
        $toservergroup.RegisteredServers.refresh()
        $instancename = $instance.name
        if($fromserverGroup.RegisteredServers.name -notcontains $instancename)
        {
            Write-Host "Deleting Server $instancename$cr" -ForegroundColor Red
            $oldserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $oldserver.ServerName = $instance.ServerName
            $oldserver.SecureConnectionString = $instance.SecureConnectionString
            $oldserver.ConnectionString = $instance.ConnectionString
            $oldserver.Description = $instance.Description
            $instance.drop()
            Write-Host "Deleted Server $servername$cr" -ForegroundColor Red
        }
    }
   
    # Add Servers
    foreach ($instance in $fromserverGroup.RegisteredServers)
    {
        $instancename = $instance.name
        if($toservergroup.RegisteredServers.name -notcontains $instancename)
        {

            if ($instance.ServerName.ToLower() -eq $tocmstore.DomainInstanceName.ToLower())
            {
                Write-Warning "Server name is Central Management Server name. Add prohibited. Skipping.$cr"
                continue
            }

            Write-Host "Adding Server $instancename$cr"
            $newserver = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($toservergroup, $instancename)
            $newserver.ServerName = $instance.ServerName
            $newserver.SecureConnectionString = $instance.SecureConnectionString
            $newserver.ConnectionString = $instance.ConnectionString
            $newserver.Description = $instance.Description
            $newserver.Create()
            Write-Host "Added Server $servername$cr" -ForegroundColor Green
        }
        else
        {
            Write-Warning "Server $instancename already exists. Skipped$cr"
        }
    }

    # Add Groups
    foreach($fromsubgroup in $fromserverGroup.ServerGroups)
    {
        $tosubgroup = $toservergroup.ServerGroups[$fromsubgroup.name]
        if ($tosubgroup -eq $null)
        {
            Write-Host "Creating group $($fromsubgroup.name)$cr" -ForegroundColor Green
            $tosubgroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($toservergroup,$fromsubgroup.name)
            $tosubgroup.create()
        }
        Parse-ServerGroup -fromserverGroup $fromsubgroup -toservergroup $tosubgroup
    }
}


foreach ($element in $array) {
    $element
}

foreach ($serverGroup in $fromcmstore.DatabaseEngineServerGroup)
{
    Parse-ServerGroup -fromserverGroup $serverGroup -toservergroup $tocmstore.ServerGroups[$serverGroup.name]
}


Share/Bookmark

No comments:

Post a Comment