Search This Blog

Tuesday, May 30, 2017

Check Backup\Restore status

When running the AG wizard it is not clear what the percentage completion figure is of each stage.

Use the query below to examine the status of backup and restore operations.

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')





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


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

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.

$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)
        $instancename = $
        if($ -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
            Write-Host "Deleted Server $servername$cr" -ForegroundColor Red
    # Add Servers
    foreach ($instance in $fromserverGroup.RegisteredServers)
        $instancename = $
        if($ -notcontains $instancename)

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

            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
            Write-Host "Added Server $servername$cr" -ForegroundColor Green
            Write-Warning "Server $instancename already exists. Skipped$cr"

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

foreach ($element in $array) {

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


Sunday, May 21, 2017

SQL Server - Use specific IP/Ports not all

Hi All,

Just something that threw me this morning and took a while to understand what was going on.

note: to disable dynamic ports, just delete the value in the field. To reenable just add 0 and restart the sql instance.

If you need\want your SQL server instance to only listen on specific IPs (and not all IPs in the machine), then you must set “Listen All” on the protocol tab to “No”, whilst this seems intuitive now there is an IP All section on the IP Addresses tab, that when you look at articles on the interwebs, this is the setting you change.


IPAll section on the IP Addresses tab


Then in the IP section you want to apply to, set the Enabled setting to Yes, restart the SQL server instance and the server should now be listening on only the server IP.

This does not affect the listeners which are on different IPs, and handled by the cluster and alwayson services.



SQL Server 2012: AlwaysOn High Availability error 9692: Endpoint port

Had an issue where I was unable to get the “New Availability Group” wizard to successfully complete.

It would continually error with a 9692 Error, indicating that the endpoint couldn't be setup on port 5022.

I had expected this and on the endpoint tab I had changed to a different port (the servers already had other instances running on port 5022), however even though I changed the wizard to an unused port (i.e 5025), the error kept coming back with could not create end point on port 5022.

I had not come across this before and I cannot find anything about it on the interwebs..

The way I got around this was to manually setup the endpoints, before running the wizard (Changing the port to the desired value).

-- Create a new Database Mirroring Endpoint on Replica 1
   ROLE = ALL,

-- Start the Endpoint on Replica 1


When the wizard got to the endpoint it then listed these endpoints and I was able to continue through (the ports are greyed out and can no longer be changed)


Would love to know why on this occasion the wizard failed, as I have happily changed the port in the past but this work around got me through.


Monday, May 15, 2017

Changing SQL Server Named Instance from dynamic port to static port - (“The target principal name is incorrect.  Cannot generate SSPI context. “) fun!!

Recently had an issue where an engineer had decided to change an existing SQL Server from using dynamic ports to a static port.

The server was a named instance.


Having made the change using SQL Server Management Studio (SSMS) on the actual server he did not notice that access to the server instance via SSMS remotely had stopped working.

Trying to connect remotely to the server resulted in a

“The target principal name is incorrect.  Cannot generate SSPI context. “

error dialog appearing.

after a bit of investigation it was clear this was a problem with the Service Principal Name (SPN) that had been created. There are a number of troubleshooting articles about this issue and I list them in the references below.

One of the solutions is to remove and recreate the SPN. This involves using the setSPN.exe, however you need to know structure the URLs. However whilst looking at this I cam across this

Kerberos Configuration Manager for SQL server

This tool actual checks your SPNs and will generate a script to fix it, or you can just click fix and it will run the script.

In the end this is all I used to fix my issue