What's a good way to automatically sync a SQL Server 2008 database from a 2005 database?
Okay, the script is two servers, on completely different parts of the Internet.
Sql 2008 database just needs to get data updates and schema changes. It doesn't need to submit anything to the 2005 database. Basically just suck the data and schema as efficiently as possible as a scheduled task.
The database is quite huge .... but the changes per day are probably around 20/30 megabytes of data /
I am unable to run any built-in replication on the 2005 database.
I looked at the Sync Framework, I think it might do what I want, but it seems a little painful and takes a bit of work to get going. I am wondering if there are tools to make this process easier?
or?? not really sure what my options are.
a source to share
I am unable to run any of the built-in replication on the 2005 database.
Any reason for this limitation? Replication is the way to solve your problem. Without a replication infrastructure, you simply cannot detect data changes or schema changes. There are two ways to detect changes, either through triggers and watch tables (and this is merge replication), or through the database log (and this is a replication transaction).
The Sync Framework itself, if used, would require either tracking changes or changing data. But these are special technologies from 2008, and they are really nothing more than masking replication (they use the same infrastructure as Merge and, accordingly, transactional replication).
Even if you want to roll your own, you'll quickly find out that pushing changes on them is the trivial part, for example. using Service Broker for robust delivery semantics. But the real hard problem is change detection, and it's hard. Eliminating a "huge" database over the Internet to detect changes simply won't work. So relying on the built-in infrastructure for change detection, namely two forms of replication, is just an obvious solution.
a source to share
Can you automate RedGate SQL and / or SQL Data Compare? http://www.red-gate.com/products/SQL_Compare/index.htm ... you could at least try this with a 14 day trial and see if the investment is worth making. Much cheaper than equipping it yourself, IMHO.
a source to share
Perhaps these questions will help you:
Microsoft Sync Framework or Replication
Is there a way to replicate some data not all data in db using SQL Server Replication?
you can create an application that generates a script from your modified data at your favorite period and then run that script on your target server.
a source to share