CODEWITHSUNDEEP
sqlsql-serversql-server-2005
JAS
JAS

Reputation: 294

Best way to sync SQL 2000 and 2005 databases

I have a production database in SQL 2000 and a SQL 2005 instance will be installed in a new server. I would like to have an updated copy of one SQL 2000 database in my SQL 2005 at anytime.

What is the best way to implement database sync between this two SQL versions?

Upvotes: 2

Views: 693

Answers (2)

HLGEM
HLGEM

Reputation: 96572

You can create an SSIS package to do this and schedule it as a job.

Or if this is development machine that you want to keep you can do all the development on the 2005 database in scripts kept in source control (which is of course what you should be doing), then you take the most recent backup, restore it to the database and run any change scripts you need.

Or use Red_gate's SQL data compare. It costs money but its worth it.

Upvotes: 1

Alex
Alex

Reputation: 23300

if "anytime" means you need the data to be updated as soon as it changes, i would think about triggering the tables on the 2000 server to mirror the operation on the 2005 server.

or, a DTS on the 2000 server might do the job, but i have no experience with those (they are also deprecated if i'm not mistaken).

or, some scheduled jobs might keep data in sync between the servers (but they'll be out of sync until the next job run)

hope this helps, myself i'd go with triggering since it's a "set and forget" approach.

Upvotes: 0

Related Questions