Had a situation where there were 2 different databases on 2 different servers. However, in each of those databases, there were tables that get populated the same data in their respective columns in real time. In this case, whenever someone does a sale at a store on their Point Of Sale system, the transaction amount gets stored on those 2 different databases differently but with the same data. Technically, both of these tables should match in terms of sales data but there have been times where they both don’t get populated the same sale amount (either the server is down for database 1 or a different technical issue occurs for server 2). The below PowerShell script is a template that queries data on both of the database tables, compares the table sales data, and alerts if there is a discrepancy.
Solution
Add-PSSnapin SqlServerCmdletSnapin100Add-PSSnapin SqlServerProviderSnapin100$currentDateTime = get-date -format “yyyy/MM/dd HH:mm”$Sales1 = @{}$Sales2 = @{}$SQLTable1Script = “D:\xxx1.sql”$SQLTable2Script = “D:\xxx2.sql”$SQLTable1Server = “xxx”$SQLTable2Server = “xxx”$Sales1 = Invoke-Sqlcmd -InputFile $SQLTable1Script -ServerInstance $SQLTable1Server$Sales2 = Invoke-Sqlcmd -InputFile $SQLTable2Script -ServerInstance $SQLTable2Server$emailTo = “[email protected]”$emailFrom = “[email protected]”$smtpServer = ‘mailrelay’$isIssue = 0$Sales1Total = 0$Sales2Total = 0$emailOutput = “<table><tr><th>Sales2 Store ID</th><th>Sales1 Store ID</th><th>Store Name</th><th>Sales2 Sales</th><th>Sales1 Sales</th><th>Difference</th></tr>”for ($i = 0; $i -le $Sales1.length – 1 ; $i++){$Result = “” | Select Sales2StoreID,Sales1StoreID,StoreName,Sales2,Sales1,Difference$difference = $Sales2[$i][1] – $Sales1[$i][1]$Result.Sales2StoreID = $Sales2[$i][0]$Result.Sales1StoreID = $Sales1[$i][0]$Result.StoreName = $Sales2[$i][2]$Result.Sales2 = $Sales2[$i][1]$Result.Sales1 = $Sales1[$i][1]$Result.Difference = $difference$Sales1Total += $Result.Sales1$Sales2Total += $Result.Sales2if ($difference -ne 0){$isIssue = 1$emailOutput += “<tr><td style=’background: yellow;’><center>” + $Result.Sales2StoreID + “</center></td><td style=’background: yellow;’><center>” + $Result.Sales1StoreID + “</center></td><td style=’background: yellow;’><center>” + $Result.StoreName + “</center></td><td style=’background: yellow;’><center>” + $Result.Sales2 + “</center></td><td style=’background: yellow;’><center>” + $Result.Sales1 + “</center></td><td style=’background: yellow;’><center>” + $Result.Difference + “</center></td></tr>”}else{$emailOutput += “<tr><td><center>” + $Result.Sales2StoreID + “</center></td><td><center>” + $Result.Sales1StoreID + “</center></td><td><center>” + $Result.StoreName + “</center></td><td><center>” + $Result.Sales2 + “</center></td><td><center>” + $Result.Sales1 + “</center></td><td><center>” + $Result.Difference + “</center></td></tr>”}#write-host $Sales2[$i][0]”`t”$Sales2[$i][1]”`t”$Sales1[$i][1]”`t”$difference}$emailOutput += “</table>”if ($isIssue -eq 1){Send-MailMessage -From $emailFrom -To $emailTo -Subject “Sales2 vs. Sales1 Sales TEST – $currentDateTime” -Body “Hi Team,<br /><br /> Please check the <b style=’background: yellow;’>Difference column or highlighted row</b> to see if Sales2 vs. Sales1sales are matching. <br /><br /> <b>Current Date/Time:</b> $currentDateTime <br /><br /><br /><br /><hr /> $emailOutput <br /><br /><br /><b>Total Sales1 Sales: </b> $Sales1Total <br /><br /><b>Total Sales2 Sales: </b>$Sales2Total” -BodyAsHtml -Priority High-DeliveryNotificationOption OnSuccess, OnFailure -SmtpServer $smtpServer}
