Comparing SQL Server Logins on Mirror Servers

I think it is great to pass on a hobby like fishing to the future generations.  I really enjoy taking my kids out fishing, and I’m glad they seem to enjoy it as much as they do.  Unfortunately, at the younger ages you often have to keep a close eye on them to make sure they aren’t going to hook themselves or cast into a tree.

SQL Server database mirroring also requires a rather watchful eye.  One problem that can occur is when a login is created on the principal server but not on the mirror.  Mirroring does not automatically move logins from the principal to the mirror servers, so monitoring is required to make sure the logins stay in sync.

This can be done with a linked server from one to another, but we may not always want to allow a linked server for security purposes.  Instead, I’ve created a Powershell script to compare the two servers and send an email if the mirror server is missing logins that the principal server has.  I use a Windows Scheduled Task to run this script on a regular basis.

$MirrorSQLLoginList = Invoke-SQLCmd -query "SELECT s.name FROM master.sys.syslogins AS s INNER JOIN master.sys.server_principals AS sp ON sp.sid = s.sid WHERE sp.is_disabled = 0 ORDER BY s.name;" -Server "MirrorServer" | select -Expand name
$PrincipalSQLLoginList = Invoke-SQLCmd -query "SELECT s.name FROM master.sys.syslogins AS s INNER JOIN master.sys.server_principals AS sp ON sp.sid = s.sid WHERE sp.is_disabled = 0 ORDER BY s.name;" -Server "PrincipalServer" | select -Expand name
$MissingLogins = $PrincipalSQLLoginList | Where {$MirrorSQLLoginList -NotContains $_}
if ($MissingLogins.count -gt 0)
{
    $AlertEmail = new-object Net.Mail.MailMessage
    $AlertEmail.IsBodyHTML = $true
    $AlertEmail.From = "Powershell@mydomain.com"
    $AlertEmail.To.Add("dgugg@mydomain.com")
    $AlertEmail.Subject = "Logins on the primary database server missing from the mirror"
    $AlertEmail.Body = ($MissingLogins -join "<br/>")
    $smpt = new-object Net.Mail.SMTPClient("mydomain-com.mail.protection.outlook.com","25")
    $smpt.send($AlertEmail)
}

 

 

Advertisements