Compare Modelstores using tableDiff Utility

Did you know SQL Server 2005 was shipped with a tool that makes it possible to analyze differences between two tales? This tool is called tablediff.

The reason I mention this is because I had to quickly check two Modelstores where there were some differences I didn't expect to find. My Modelstore-import failed and the reason seemed to be some conflicting elements.

I was thinking about just writing a SQL query to look it up. It would have involved setting up a Linked Server since these Modelstores where on two different SQL Server instances. While I was considering my options, and searching the web for possible options, I came across this old tool. I remember seeing it before, but never had the use of it. It seem to fit the bill perfectly, as all I was interested in was comparing the table ModelElement for conflicting elements.

Basically, it is a command line utility that ships with SQL Server, and you can run it with parameters for source, destination and some additional options. For details visit: http://technet.microsoft.com/en-us/library/ms162843.aspx. One of the benefits is its performance, and the ModelElement can contain a million rows or more. In my example, the comparison took less than 30 seconds.

The command I used was this:

tablediff.exe -sourceserver sql2012\test -sourcedatabase ax2012_test_model -sourceschema dbo -sourcetable modelelement -destinationserver sql2012 -destinationdatabase ax2012_prod_model -destinationschema dbo -destinationtable modelelement -et diff1 -f c:\compare.sql -c

The output file contained SQL for updating the destination, but I was only interested in the Query itself. Investigating the output file, searching for the string "update " (trailing space after the command), I found the dozen elements that was causing the conflict. I could then further list the ElementHandles in a new SQL where I would list them out to see what elements they were. Example:

SELECT ET.ELEMENTTYPENAME, ME.* FROM MODELELEMENT ME
JOIN ELEMENTTYPES ET ON ME.ELEMENTTYPE = ET.ELEMENTTYPE
WHERE ME.ELEMENTHANDLE IN (860734,860739,860746)

The utility is installed if you opt for installing the Replication Features, and will be installed normally under C:\Program Files\Microsoft SQL Server\100\COM\

I might wrap a PowerShell script around it later, but not today.