Show-RowCountDifference.ps1

<#
.SYNOPSIS
Gets row count difference.

.DESCRIPTION
Gets row count difference between two identical databases from two different servers.

.PARAMETER Server1Name
Specify Server1 name, this is a mandatory parameter.

.PARAMETER Server2Name
Specify Server2 name, this is a mandatory parameter.

.PARAMETER DatabaseName
Specify the database name on which the comparision is required

.PARAMETER Server1Port
Specify the Server1 port number if it is not the default sql server port number 1433

.PARAMETER Server2Port
Specify the Server2 port number if it is not the default sql server port number 1433

.PARAMETER ConnectionTimeOut
Specify the connection timeout. This is not a mandatory parameter and the default timeout is 15 seconds.
    
.PARAMETER QueryTimeOut
Specify the query timeout. This is not a mandatory parameter and the default timeout is 15 seconds.

.PARAMETER ShowWindow
If you specify this parameter, then the output will be displayed in the separate window

.EXAMPLE
.\Show-RowCountDifference.ps1 

Displays this help.

.EXAMPLE
.\Show-RowCountDifference.ps1 -Server1Name SqlServer1 -Server2Name SqlServer2 -DatabaseName MyDatabase

Finds the row count differences from the two servers and will display the differences on the screen.

.EXAMPLE
.\Show-RowCountDifference.ps1 -Server1Name SqlServer1 -Server2Name SqlServer2 -DatabaseName MyDatabase -ShowWindow

Finds the row count differences from the two servers and will display the differences in a separate window.

.EXAMPLE
.\Show-RowCountDifference.ps1 -Server1Name SqlServer1 -Server2Name SqlServer2 -DatabaseName MyDatabase -ConnectionTimeOut 30 -QueryTimeOut 30

Increase the timeout values in case your severs respond slowly. It connects to the servers with the increased timeouts,
and finds the row count differences from the two servers and will display the differences on the screen.

You can use -ShowWindow to display the output in a separate window.

.EXAMPLE
.\Show-RowCountDifference.ps1 -Server1Name SqlServer1 -Server2Name SqlServer2 -DatabaseName MyDatabase -Server1Port 1981 -Server2Port 1982

In case if the sql server ports are other than default port 1433, then you can specify the ports. It connects to the servers with the given ports,
and finds the row count differences from the two servers and will display the differences on the screen.

You can use -ConnectionTimeOut and/or -QueryTimeOut to increase the request timeouts, and -ShowWindow to display the output in a separate window.

.INPUTS
None

.OUTPUTS
PSCustomObject

#>
[CmdLetBinding(DefaultParameterSetName = 'Help')]
param
(
    # Source SQL Server Name
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $true, ParameterSetName = 'WithPort')]
    [string] $Server1Name,

    # Target SQL Server Name
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $true, ParameterSetName = 'WithPort')]
    [string] $Server2Name,

    # Database to show the delta values
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $true, ParameterSetName = 'WithPort')]
    [string] $DatabaseName,
    
    # Source SQL Server Port
    [Parameter(Mandatory = $false, ParameterSetName = 'WithPort')]
    [int] $Server1Port = 1433,
    
    # Target SQL Servr Port
    [Parameter(Mandatory = $false, ParameterSetName = 'WithPort')]
    [int] $Server2Port = 1433,
    
    # Connection request timeout
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $false, ParameterSetName = 'WithPort')]
    [int] $ConnectionTimeOut = 15,
    
    # Query request timeout
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $false, ParameterSetName = 'WithPort')]
    [int] $QueryTimeOut = 15,

    # Output display in a seperate window
    [Parameter(Mandatory = $true, ParameterSetName = 'WithName')]
    [Parameter(Mandatory = $false, ParameterSetName = 'WithPort')]
    [switch] $ShowWindow
)

If ($PSCmdlet.ParameterSetName -eq 'Help')
{
    Get-Help $MyInvocation.Line -Full
    return
}

# Establish connection to the SQL Server
function Connect-SQL 
{
    [CmdLetBinding()]
    param 
    (
        [Parameter(Mandatory = $true)]
        [string] $ServerName, # Sql server name
        [Parameter(Mandatory = $true)]
        [int] $Port, # Sql server port
        [Parameter(Mandatory = $true)]
        [string] $DatabaseName, # Default database
        [Parameter(Mandatory = $true)]
        [int] $TimeOut                  # Connection timeout
    )

    # Create a new sql server connection object
    $SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection

    # Connection string usign the integrated credentials
    $SQLConnection.ConnectionString = "Server = $ServerName,$Port; Database = $DatabaseName; Integrated Security = True;Connection Timeout=$TimeOut;"

    # Establish the connection and throw a message on an error 
    try
    {
        # Open Sql Server connection
        $SQLConnection.Open()

        # Return the connection object
        return $SQLConnection
    }
    catch
    {
        # Display message on error
        Write-Host -ForegroundColor Red "Unable to connect to the SQL Server $ServerName using the port $Port"
    }
}

# Execute the SQL Query using the SQL Server Connection
function Execute-SQL 
{
    [CmdLetBinding()]
    param 
    (
        [Parameter(Mandatory = $true)]
        [System.Data.SqlClient.SqlConnection] $SQLConnection, # Sql Server connection
        [Parameter(Mandatory = $true)]
        [string] $SQLQuery, # Sql query
        [Parameter(Mandatory = $true)]
        [int] $TimeOut                                          # Query timeout
    )
    
    # Check the status of the sql server connection
    # Run the query if the sql server connection is in open state
    if ($SQLConnection.State -eq 'Open')
    {
        # SQL Command
        $SQLCommand = New-Object System.Data.SqlClient.SqlCommand
        $SQLCommand.CommandText = $SQLQuery
        $SQLCommand.CommandTimeout = $TimeOut
        $SQLCommand.Connection = $SQLConnection

        # SQL Adapter
        $SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SQLAdapter.SelectCommand = $SQLCommand

        # Dataset
        try 
        {
            $DataSet = New-Object System.Data.Dataset
            $SQLAdapter.Fill($DataSet) | Out-Null
            
            #Return the output
            return $DataSet.Tables[0]
        }
        catch
        {
            # Display the error
            Write-Host -ForegroundColor Red $_.Exception.Message
        }
    }
    else 
    {
        # Inform if the connection didn't open
        Write-Host 'No open connection found.' -ForegroundColor Red
    }
}

# Close the sql server connection
function Disconnect-SQL
{
    [CmdLetBinding()]
    param 
    (
        [Parameter(Mandatory = $true)]
        [System.Data.SqlClient.SqlConnection] $SQLConnection    # Sql Server connection
    )
    # Close the sql server connection if it is already in open state
    if ($SQLConnection.State -eq 'Open')
    {
        # Close the sql server connection
        $SQLConnection.Close()
    }
}

# Dispose the sql server connection
function Dispose-SQL
{
    [CmdLetBinding()]
    param 
    (
        [Parameter(Mandatory = $true)]
        [System.Data.SqlClient.SqlConnection] $SQLConnection # Sql Server connection
    )
    # Dispose the sql server connection
    if ($SQLConnection)
    {
        # Dispose the sql server connection
        $SQLConnection.Dispose()
    }
}

# Query to fetch the row counts from the servers
$Query = @"
SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [RowCount]
FROM INFORMATION_SCHEMA.tables AS [InfoTables]
JOIN sys.tables as tables
on infotables.TABLE_NAME = tables.name
JOIN sys.partitions as [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
WHERE [InfoTables].TABLE_TYPE = 'BASE TABLE'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order BY 2 DESC
"@

# Establish the Server1 connection
$Server1SqlConnection = Connect-SQL -ServerName $Server1Name -Port $Server1Port -DatabaseName $DatabaseName -TimeOut $ConnectionTimeOut 

# Establish the Server2 connection
$Server2SqlConnection = Connect-SQL -ServerName $Server2Name -Port $Server2Port -DatabaseName $DatabaseName -TimeOut $ConnectionTimeOut 

# Execute the query on Server1
$RowCountFromServer1 = Execute-SQL -SQLConnection $Server1SqlConnection -SQLQuery $Query -TimeOut $QueryTimeOut 

# Execute the query on Server2
$RowCountFromServer2 = Execute-SQL -SQLConnection $Server2SqlConnection -SQLQuery $Query -TimeOut $QueryTimeOut 

# Check for the differences
$RawDifferences = Compare-Object -ReferenceObject $RowCountFromServer1 -DifferenceObject $RowCountFromServer2 -Property SchemaName, TableName, RowCount 

# If found no differences then end the script
if (-not $RawDifferences)
{
    Write-Host -ForegroundColor Yellow "No differences found"
    return
}

# Group the differences
$DifferencesByGroup = $RawDifferences | Group-Object -Property SchemaName, TableName 

# Format the output
$FoundDifferences = $DifferencesByGroup | ForEach-Object { 
    [PSCustomObject]@{
        SchemaName     = $_.Name.Split(',')[0] 
        TableName      = $_.Name.Split(',')[1]
        "$Server1Name" = $_.Group.RowCount[1]
        "$Server2Name" = $_.Group.RowCount[0]
    }
}

# Output
if ($ShowWindow)
{
    $FoundDifferences | Out-GridView
}
else
{
    return $FoundDifferences
}