Comparing two SQL instances

There’s many a time in a DBA’s life where they’ll need to compare various aspects of two SQL instances against each other.

For instance, last week I was working with two instances that contained databases part of an Always On availability group and needed to ensure that all databases on the primary were on the secondary.

Now I know there’s a few ways to do this but I needed a quick and easy method as there were over 300 dbs involved.

The method I used to do this implemented the powershell cmdlet Compare-Object

What this does is pretty much what it says on the tin. The cmdlet takes two objects and compares them based on a input property (in this case it’ll be database name).

Here’s the code: –

$InstanceA = ''
$InstanceB = ''

$SqlQuery = 'SELECT name FROM sys.databases'

$DatabasesA = Invoke-SqlCmd2 -sqlinstance $InstanceA -query $SqlQuery
$DatabasesB = Invoke-SqlCmd2 -SqlInstance $InstanceB -query $SqlQuery

Compare-Object -ReferenceObject $DatabasesA -DifferenceObject $DatabasesB -Property "name"

Let’s run a quick test to show the output. Say I have two SQL instances with the following databases: –

If I run the script, I will get the following: –

This is telling me that Databases C & G exist in Instance B but not Instance A and that Databases E & I exist in Instance A but not in Instance B.

The script runs very quickly (even with a large amount of dbs) and gives a nice, easy to understand output that allowed me to work out which databases needed to be reseeded in my AG.

Now, that’s pretty cool. But what else can we do with this cmdlet?

How about we implement the dbatools Get-DbaSpConfigure to compare the configuration settings between these two instances?

Let’s say I want to compare the Maximum Memory settings between the instances. Here’s the code: –

$InstanceA = ''
$InstanceB = ''

$ConfigValue = "MaxServerMemory"

$InstanceAConfig = Get-DbaSpConfigure -SqlInstance $InstanceA | Where-Object {$_.ConfigName -eq $ConfigValue}
$InstanceBConfig = Get-DbaSpConfigure -SqlInstance $InstanceB | Where-Object {$_.ConfigName -eq $ConfigValue}

Compare-Object -ReferenceObject $InstanceAConfig -DifferenceObject $InstanceBConfig -Property "RunningValue"

And here’s the output: –

Telling me that Instance A’s memory is set to 2048MB and Instance B’s is set to 1024MB.

Nice! That means I can input any configuration setting outputted from
Get-DbaSpConfigure and compare across instances. Handy tool to have in the script deck imho 🙂

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s