If you've ever had to troubleshoot non-convergence with SQL Server Replication and not been able to initialize from a snapshot or backup, you could be left with the complex task of manually synchronising individual tables. Non-convergence is a term used to describe the differences in rows between two tables.  I recently came about this problem and whilst there are third-party tools available, I didn't have them at my disposal.  Instead I turned to PowerShell and a freely available command-line tool called TableDiff.

Introduced in SQL Server 2005, TableDiff is very useful when comparing schemas and troubleshooting non-convergence issues between replicated tables on a publisher and subscriber.  The TableDiff executable is located in the following directory, but only when the SQL Server Replication feature is installed:

C:\Program Files\Microsoft SQL Server\<version_number>\COM

There are limitations to TableDiff when comparing source and destination tables which readers should be aware of. For example, Microsoft state that the following data types aren't included in convergence scripts as a result of running TableDiff:

• varchar(max)
• nvarchar(max)
• varbinary(max)
• timestamp
• xml
• text
• ntext
• image

For more information, review the Remarks section within Microsoft's documentation at https://msdn.microsoft.com/en-us/library/ms162843(v=sql.120).aspx.

So let's go back to my scenario… there were 50+ replicated tables that needed converging.  Typing all the necessary TableDiff commands for this number of tables into a single script wasn't a prospect I relished!  Instead, I used PowerShell to automate the creation of the TableDiff commands against a returned set of replicated table names.  Crucially, I didn't use PowerShell to execute the TableDiff commands themselves - that was done via a batch file, but we'll come to this later. Essentially, the benefit of using PowerShell to generate the necessary TableDiff commands is that I can perform a quick visual check before running them.

The script relies upon the SQLPS PowerShell module installed with SQL Server, which can be imported using the PowerShell command below.  I could have used SMO however I opted to use the native cmdlets within the SQL Server provider for simplicity.

Import-Module SQLPS -DisableNameChecking

The script initially defines the variables upon which the functions within the script depends.
$source      = "source_sql_instance_name"
$destination = "destination_sql_instance_name"
$database    = "my_database"
$batchFile   = "<drive:><location>\Table_Diff_Script.bat"

The first function uses Invoke-SqlCmd to query the source database and obtain a list of replicated tables. If $true is passed for the variable $fastComparison, a fast comparison will be performed which means only row counts and schemas are compared. Finally a set of corresponding TableDiff commands will be generated in a batch file, enabling the user to perform a fast comparison between the source and destination databases.

function New-TableDiffCommands()
{
Param
(
[bool] $fastComparison
)

$tables = Invoke-Sqlcmd -Query "SELECT name FROM sys.tables WHERE is_replicated = 1" -ServerInstance
$source -Database $database

if ($fastComparison -eq $true)
{
# Generate commands for a fast comparison only
foreach ($table in $tables)
{
$tableName = $table.name
"tablediff -q -sourceserver $source -sourcedatabase $database -sourcetable
$tableName -destinationserver $destination -destinationdatabase $database -destinationtable
$tableName" | Out-File $batchFile -Append -Encoding ascii
}
}
else
{
foreach ($table in $tables)
{
$tableName = $table.name
"tablediff -sourceserver $source -sourcedatabase $database -sourcetable $tableName
-destinationserver $destination -destinationdatabase $database -destinationtable $tableName"
| Out-File $batchFile -Append -Encoding ascii
}
}
}

The second function takes the first a step further by generating a set of TableDiff commands with the -f option to produce convergence scripts (if necessary). The convergence scripts will typically contain a set of DML statements that need executing to converge the tables on the source and destination tables.

function New-TableDiffCommandsWithConvergenceScript()
{
Param
(
[string] $directory
)

# Delete any old convergence scripts
Get-ChildItem $directory -include *.sql -recurse | Remove-Item

$tables = Invoke-Sqlcmd -Query "SELECT name FROM sys.tables WHERE is_replicated = 1"
-ServerInstance $source -Database $database

# Generate TableDiff commands with the -f option to generate convergence scripts
foreach ($table in $tables)
{
$tableName = $table.name
$filename = $directory + $tableName + "_converge_script.sql"
"tablediff -f $filename -sourceserver $source -sourcedatabase $database -sourcetable $tableName
-destinationserver $destination -destinationdatabase $database -destinationtable $tableName"
| Out-File $batchFile -Append -Encoding ascii
}
}

The final part of the script executes the desired function, after which a batch file will be created at the location specified for the variable $batchFile. The resulting batch file can then be executed manually via a Command Prompt or PowerShell console.

# Main: Here we call the function we want to use
New-TableDiffCommands -fastComparison $true
New-TableDiffCommandsWithConvergenceScript -directory "C:\temp\"

Hope that was useful! You'll find the full PowerShell script zipped up here.