Friday, January 10, 2014

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.

Disable UAC on Windows 2012 R2

I noticed that User Access Control (UAC) was still on on my Windows 2012 R2 box, even though I had turned it off in Control Panel. So I went through the process of turning it off the hard way. While doing so I sort had to think over why I want to turn it off. There are several good arguments to keep UAC on, but there's also reasons to just get it out of the way. UAC creates additional obstacles for any operation that might cause your system to fail, but if you're like me, you are constantly changing stuff on the system, and most of these changes are protected by UAC. I'm willing to conclude that some of you might want to turn UAC off, so here is how to on Windows 2012 R2 (as far as I know).


So if you were to load User Access Control and turn it down to "Never notify", you might think that UAC is off. Well, it is not. Even if you restart the server, it is not.


If you open the registry holding the setting you will notice the value is still 1.


So I open a command line window and I run this command.
reg add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system /v EnableLUA /t REG_DWORD  /d 0 /f


Immediately the Security settings light up and prompts for a restart.


Unless you restart right away the restart option is available from the taskbar.


After the restart the UAC is completely turned off.


Again, there are a lot of ways you can work with UAC on so unless you really need it off, I would recommed having it on.