info@techdevops.com | 437-991-3573 | Data Tier Services
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server, Clusters, Always On, Migrations, Performance, & Cloud



Windows Failover Cluster - Using Paxos Tag to monitor cluster configuration synchronization across the system
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2020-06-20








Windows Server Failover Cluster (WSFC) uses the Paxos Algorithm to synchronize changes across the entire system.

Cluster Database synchronization between nodes is easily monitored by recording the Paxos Tag value and keeping a historical record.

Each time the Cluster configuration is changed the internal synchronization process sends out a proposal to all the nodes in the cluster. This is comprised of a sequence number and a proposal number. Once the proposer obtains consensus from the majority of nodes, it will choose a value and broadcast it out for all others to commit that value. This is the inner workings of the Paxos Tag Algorithm.

Using a Batch & Powershell script, we are able to log the Paxos Tag across all Nodes in the Cluster and email that out as a report.





Batch Code:

@echo off

set mydate=%date%
set mytime=%time%
set host=%COMPUTERNAME%
set vseparator=::

for /f "tokens=3" %%a in ('reg query "hklm\Cluster" /V "PaxosTag" ^|findstr /ri "REG_SZ"') do set paxos=%%a

echo %date% %mytime% %vseparator% %host% %vseparator% %paxos%>> Paxos.txt


Powershell Code:

#Pre-Requisite: Setup a Secure Password File that is the actual password for SMTP Authenticaion. Run below line once:
#Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File -FilePath C:\Bin\Secure.txt

$PaxosValue = Get-Content "C:\Bin\Paxos.txt" | select -Last 1

$FilePath = "C:\Bin\Secure.txt"
$PSEmailServer = "smtp com"
$SMTPPort = 587
$SMTPUsername = "alerts@.com"
$EncryptedPasswordFile = $FilePath
$SecureStringPassword = Get-Content -Path $EncryptedPasswordFile | ConvertTo-SecureString
$EmailCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SMTPUsername,$SecureStringPassword
$MailTo = "ops@.com"
$MailFrom = "noreply@.com"
$MailSubject = "Production: SQL Cluster: Paxos Value: $PaxosValue"
$MailBody = "Identical Values expected from all Nodes in Active Membership the Cluster"

Send-MailMessage -From $MailFrom -To $MailTo -Subject $MailSubject -Body $MailBody -Port $SMTPPort -Credential $EmailCredential -UseSsl