I was recently asked to find a way of comparing our live and failover reporting servers to make sure that both matched.  After a bit of research, it seemed that most users recommended Red Gate software for this task; further research lead me to their SQL Automation Pack.  The SQL Automation Pack can run a suite of Red Gate products from the command line, one of these products being SQL Compare. SQL Compare allows you to compare database schemas and perform various tasks according to the results.  I’m not going to into too much detail about the various functions of SQL Compare as that’s beyond the scope of this article.

One of things I had been asked to do was have the results of the comparison emailed out to the members of the team.  As before, I started researching, but this time it became apparent that putting the two tasks together wasn’t as straightforward as it seemed it should be.  There were many pages of scripts to run SQL Automation and many more about the difficulties of sending email from the command line.  Nowhere though, was there one page that brought the two together.  I spent a good while exploring various avenues involving Batch Files, Windows Event Viewer, Windows Task Scheduler and PowerShell before hitting on the solution below.  As with many things the final method was surprisingly simple so I decided to write this to (hopefully) save others the time that I spent.

The method I used in the end makes use of an SQL Server Agent Job to run a PowerShell script then uses dbmail to email out the results.  There are likely to be other ways of achieving the same outcome but this, to me, seemed the most simple.

Creating the PowerShell Script

The first step is to write the PowerShell script that compares the two databases.  SQL Compare is a very flexible tool for schema comparison and there are many options available.  The script I have included here is pretty much the most basic you can write but it may be all that is required.  However, as I said previously, showing exactly what can be done with SQL Compare is not what this article is about.  Although you can write directly into your SQL Agent job, in my opinion, it will be easier to create it in PowerShell then copy and paste it across.

set-location "C:\Location Of SQL Compare";

./sqlcompare /s1:LiveServer /db1:ThisDatabase /s2:FailOverServer /db2:ThatDatabase /r:C:\LocationOfOutputFile\FileName.xls /rt:Excel /f

This script gives the two servers to be compared (/s1: and /s2:), the databases to be compared (/db1: and /db2:) and creates a report for the output of the comparison (/r:).  The /rt switch sets the report type, in this case to Excel.  The /f switch at the end tells SQL Compare to overwrite the output file if necessary.  If that switch is not included the script will fail if the file already exists.

Creating the Job

The next step is to create the SQL Server Agent job to execute the script and email the results. To create the job, there are two options.  Firstly you can right click on the SQL Server Agent node then select New then Job as shown in Figure 1 below.

Figure 1: Creating a New Job

Secondly, you can right click on the Jobs node and select New Job.

Figure 2: An Alternative Way to Create a New Job

Both options will open this screen.

Figure 3: Defining the Job

Give the job a name that will explain what it does and, if you wish, enter a more detailed description in the box.  After that, make sure the job owner is sa.  Next, click the Steps option followed by ‘New…’ This will open the screen shown in Figure 4.

Figure 4: Creating a Step

Give the step a name and set the Type to PowerShell.  At this point the ‘Run As:’ dropdown will automatically change to ‘SQL Server Agent Service Account’.  After this, enter your PowerShell script into the Command Box.  Click OK to return to the Job Properties screen then click ‘New…’

As before give the step a descriptive name but this time set the type to ‘Transact-SQL Script (T-SQL)’. Then, in the Command Box, enter the script to send the email using dbmail.  As before the example I’ve provided is almost as simple as it gets but it is enough to do the job.

exec msdb.dbo.sp_send_dbmail
     @Recipients = 'AnyBody@WhoNeedsToKnow.com'
,    @File_Attachments = 'C:\YourResultFile'
,    @Body = 'SQL Compare ran successfully'

When entering multiple email addresses separate them using semicolon but enclose all of them in single quotes i.e. ‘EmailOne; EmailTwo’.

The next step is to add some error-handling.  It’s all very well receiving the email every day to say things are fine but just as importantly, you need to know when they’re not.  As before, create and name a T-SQL step with the email script in the Command Box but this time change @Body to show your error message.  Before exiting this step, click the Advanced node on the left-hand side of the screen.  That will take you to Figure 5.

Figure 5: Error Handling

Set the ‘On success action:’ dropdown to ‘Quit the job reporting failure’.  Remember you want this to run when the job hasn’t worked.  Leave the ‘On failure action:’ dropdown as it is.  Click OK to return to the job properties screen.

Next, re-open your first step and click the advanced node.  This time set the ‘On success action:’ to, in this case, ‘Go to step:[2] Send Email’.  Then, set the ‘On failure action:’ to ‘Go to step:[3] Failure’.  Click OK to exit and then re-open your Send Email step.  Click the advanced node again and set the ‘On success action:’ to ‘Quit job reporting success’ and set the ‘On failure action:’ to ‘Go to step:[3] Failure’.  Click OK to exit.

Finally you need to schedule the job to run.  From the job Properties screen click the Schedules node and the click ‘New…’, this will take you to the screen below (Figure 6).

Figure 6: Scheduling the Job

Give the schedule a name and then set it as you wish.  I tend to run SQL Compare once a day overnight to pick up any changes made during the day.