SQL Tools for synchronizing databases

Benjamin Nitschke (abi) — Thu, 31 Jan 2008 19:10:32 GMT

In the past I usually updated any local database changes directly at the server everytime I uploaded something. If your database is small and the changes occur not very often, this is not a big issue, but once the database grows and especially if you update something here and there very often, it is an absolute waste of time and annoying to do all this by hand. The SQL Management Studio does not really help you out like for example when you are trying to backup data or restore them again. You can connect to two databases, but you can't even have them side by side in a window and you certainly cannot drop anything from one db to the other. You will be able to copy some data from one table into a table of the other db, but that is pretty much about it.

Scripts are helpful, but if you are not a SQL pro and do not want to waste even more time getting involved with that (keep in mind I did most of my SQL coding in either LinQ or some other object releational mapper like EntitySpaces). I checked out some tools a year ago, but saw not really the need to purcase them (especially when you need these tools once a month for 5 minutes and they cost like 500 bucks). I think I bought something cheap and crappy for a few bugs, but it does not work anylonger and did not support my 64bit system. If you do a google search on sql tools you will find a lot of tools, and quite a lot cheap and crappy ones too, probably also quite a few free tools. But the more business related the problem is, the more unlikely it is to find freeware and open source (nobody wants to do this for free). Sometimes I get lucky and find exactly what I'm looking for and it only costs somethink like 19 bucks, then it is ok, even if you use it just for a short while.

Anyway, today I checked out some tools again, first the Teratrax Database Compare tool, which is nice and simple, but does cost quite a bit ($200 per developer). After I entered all the data for both databases it was able to figure out where there are changes, but once I tried to actually synchronize the database schemas (remember, I'm not interested in the data, copying that over is not so hard, I even have some functions in my code that do that) the program stopped and cleared all fields. I checked both dbs and nothing happend. I tried it some more times, but no errors appeared, it just did not work. This was very frustrating.

Instead I went to the next google search result ^^ It was SQL Compare 6 from Red-Gate. I'm already a Red-Gate customer (using the Ants Profiler quite a lot and recently have been using the new Exception Hunter tool), but I always found their SQL tools to expensive for me, especially since I do not do much SQL work (at least I don't want to ^^). Anyway, I fired up SQL Compare after installing their whole SQL toolbelt and quickly entered the connection data for both db connections, clicked next a couple of times, saw some nice statistics and messages and was all done. I checked my local database I used for testing and yes, everything from the other server was there, with default values, descriptions and so on. SQL Compare 6 is priced at around $300, so even more expensive then Teratrax and certainly more expensive than some of the other tools I have encountered. But it works and if I need it again for a few more times in the next 2 weeks (the trial period) I will probably buy it. The amazing thing is that they claim they have already 150 000 customers of the SQL tools, that is quite a lot of money they have generated with some developer-specific-tools. But then again, more than a million people have downloaded XNA Game Studio Express and I would not have guessed there are even so many programmers around (at least I do not see them in the real world, maybe they are all hiding).

Ok, end of story, all the boring database stuff is well again. Also makes generating database code much faster when done locally from a synchronized database instead of pulling the schema from some online server.

abi.exdream.com - Benjamin Nitschke's Blog - SQL

SQL Tools for synchronizing databases