The intersection of Powershell and SQL Server Development

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:

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:

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.

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.

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:

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.