what's a good way to synchronize a sql server 2008 database from a 2005 database automatically?

Question

Ok, the scenario is... two servers, on completely different parts of the internet.

The sql 2008 database just needs to get data updates and schema changes. It doesn't need to send anything to the 2005 database. Basically just suck data and schema as efficiently as possible automatically as a scheduled task.

The database is quite huge.... but the changes per day are probablly around 20/30 megabytes of data/

I can't run any of the inbuilt replication on the 2005 database.

I've had a wee look at the Sync Framework, I think that might do what I want, but seems a bit painful and requires a bit of work to get going. I'm wondering if there is tooling out there to make this easier?

or?? not quite sure what my options are.

Answer 1

Could you automate RedGate's SQL Compare and/or SQL Data Compare? http://www.red-gate.com/products/SQL_Compare/index.htm ... you could at least try that out with the 14-day trial and see if it is worth the investment. Much cheaper than tooling it yourself, IMHO.

Answer 2

maybe these questions help you:

http://stackoverflow.com/questions/1607103/microsoft-sync-framework-or-replication

http://stackoverflow.com/questions/1584783/sql-server-data-archive-solution

http://stackoverflow.com/questions/2309297/is-there-a-way-to-replicate-some-data-not-all-data-in-db-by-sql-server-replicatio

you can make an application that generate a script from your changed data in your favorite period and then run this script in your target server.

Answer 3

I can't run any of the inbuilt replication on the 2005 database.

Any reason for this restriction? Replication is the way to solve your problem. W/o a replication infrastructure you simply won't be able to detect data changes, nor schema changes. There are only two ways to detect the changes: either via triggers and tracking tables (and that is Merge Replication) or via the database log (and that is Transactional Replication).

Sync Framework itself, if it would be used, would require either Change Tracking or Change Data Capture. But these are 2008 specific technologies and they're really nothing else but replication in disguise (they use the very same infrastructure used by Merge and respectively Transactional Replication).

Even if you want to roll your own, you'll find out quickly that shipping the changes over is the trivial part, eg. using Service Broker for reliable delivery semantics. But the Real hard problem is detecting the changes, and that is hard. Diff-ing a 'quite huge' database over the internet to detect changes is just not going to work. So relying on the built-in infrastructure to detect changes, namely the two forms of Replication, is just the obvious solution.