Comparing Databases Across Instances With SMO

One of the handy things about Powershell is it works very well for cross-server tasks. One thing I’ve used it for quite often is comparing objects in databases between servers or instances. With straight TSQL this would require a linked server, and linked server queries can suffer a number of performance issues.

For this scenario, I have two instances, ServerA and ServerB. Both servers have the database MyDB which I suspect has different indexes.  ServerA I know is running fine, so if there are any indexes there that are not in ServerB I want to script them out and create them. To complicate matters, there are tables in ServerA that don’t exist in ServerB so I need to be sure not to script out indexes on those tables or we will get errors.

I should mention that the SMO framework is automatically installed with SSMS, so make sure you try the code on a machine that has that installed.

To begin, we initialize the SMO framework and create some objects to represent the servers:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$ServerA = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerA.MyIntranet.Com"
$ServerB = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerB.MyIntranet.Com"

$DbName = 'MyDB'

The Out-Null is optional but just prevents output from the Assembly static method that loads SMO from cluttering up the console.

The code above assumes you will be using integrated security (Windows domain authentication). If you need to use SQL Server credentials, you can like so:

$ServerA.ConnectionContext.LoginSecure = $false
$ServerA.ConnectionContext.set_Login('sa')
$ServerA.ConnectionContext.set_Password('SomePassword')

Next we instantiate the database objects representing MyDB on each server. The server objects contain a collection of Databases which is keyed on the database name – indexing into it with the string of the name returns the object representing that specific database.

$ADB = $ServerA.Databases[$DBName]
$BDB = $ServerB.Databases[$DBName]

…then some empty hash tables to contain our index information, and an array to contain the tables the two DBs have in common.

Important Note: the remaining code assumes you don’t have the same table name in multiple schemas (i.e. both schemaone.Table and schematwo.Table). If you do, this gets a good bit more complicated but I can post that as well if someone needs it.

$AIndexes = @{}
$BIndexes = @{}

$CommonTables = @()

Now we populate the $CommonTables with only those tables that exist in both DBs:

($BDB.Tables).Name  | Where-Object {($ADB.Tables).Name -contains $_} | ForEach-Object {$CommonTables += $_} 

In short, we take the .Name property of all the tables in the $BDB.Tables collection, filter only for tables whose name exists in $ADB.Tables and then add those names to $CommonTables.

Almost done – now we build our list of indexes. We use a hash table because the KEY of the hash table will be the qualified name of the index – TableName.IndexName.  The VALUE of the hash table will contain the actual SMO object representing the index which is much easier to deal with when we get to scripting.

$ADB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$AIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}

$BDB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$BIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}

This long pipe does a few things:

  • Iterates through all the tables
  • Filters out those not in our $CommonTables array
  • Iterates through all the indexes on each table
  • Filters out clustered indexes
  • Adds the unfiltered indexes to the hash table, with the qualified name as the key and the object itself as the value

Finally, we compare the hash tables, build a string to dump our script into, and then script out all the missing indexes:

$NeededIndexes = $AIndexes.Keys | Where-Object {$BIndexes.Keys -notcontains $_}

[string]$IndexScripts = ''

$NeededIndexes | ForEach-Object { $IndexScripts += ($AIndexes.get_Item($_)).Script(); $IndexScripts += "`n`n"}

Now you have a string containing the script for all missing indexes on ServerB, and you can either output it to the console to run it manually, save it to a file, or even run it against the server if you like.

Full Script Below

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

$ServerA = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerA.MyIntranet.Com"
$ServerB = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerB.MyIntranet.Com"

$DbName = 'MyDB'

<# Optional - Add SQL Credentials to the connection

$ServerA.ConnectionContext.LoginSecure = $false
$ServerA.ConnectionContext.set_Login('sa')
$ServerA.ConnectionContext.set_Password('SomePassword')

#>

$ADB = $ServerA.Databases[$DBName]
$BDB = $ServerB.Databases[$DBName]

$AIndexes = @{}
$BIndexes = @{}

$CommonTables = @()

($BDB.Tables).Name | Where-Object {($ADB.Tables).Name -contains $_} | ForEach-Object {$CommonTables += $_}

$ADB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$AIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}

$BDB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$BIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}
$NeededIndexes = $AIndexes.Keys | Where-Object {$BIndexes.Keys -notcontains $_}

[string]$IndexScripts = ''

$NeededIndexes | ForEach-Object { $IndexScripts += ($AIndexes.get_Item($_)).Script(); $IndexScripts += "`n`n"}

Fun with SMO – Introduction

Most people in the SQL Server world are at least passingly familiar with SMO – SQL Server Management Objects.  It’s essentially just an object-oriented structure for SQL Server objects like Instances, Databases, etc.

Using Powershell, it’s incredibly easy to do a wide variety of tasks using the SMO framework. There’s a pretty intuitive hierarchy to the whole thing, and if you’re at all comfortable in dealing with arrays and collections in PS it’s not too hard to do something like:

– Get a list of tables in a database

– Get all the nonclustered indexes in each table

– Check the fragmentation of every index in each table

– Rebuild those above a certain level of fragmentation

That kind of task is ALSO not too difficult to do in TSQL (Ola Hallengren is very well known for a TSQL based tool that does just this).

However, some tasks that aren’t easy to do in TSQL can be fairly easy to do with SMO, like (expect blog posts detailing all these things in the near future):

– Compare two databases on two servers and script out any tables or indexes on Server A that aren’t on Server B

– Script out all tables in a database that contain the string “Admin” in the table name.

– Compare users between two SQL instances and script out any variances.

You’ll notice that most of these are related to scripting and/or working across instances. If you’ve ever tried to script out a lot of objects from the SSMS GUI this probably should not be a surprise – while powerful, the GUI is a challenge to navigate especially in cases where you have a lot of non-default settings.  In SMO, this is as easy as persisting a ScriptingOptions object that contains all your settings an reusing it.

So for now, consider this post a placeholder. I’ll cover at least those three scenarios above in upcoming posts, as well as anything else that I think may be useful.