data synchronization between SQLServer2000 and SQLServer 2005

Question

data synchronization between SQLServer2000 and SQLServer 2005

Eunice-245470

SSC-Addicted

Points: 487
More actions
February 4, 2007 at 8:51 pm

#117469

Hi,
I need advise on synchronization of data for 2 different version of SQLServers.
SQLServer 2000 is residing on an external server and SQLServer 2005 is inhouse. Periodically, the data on each of the servers must be updated. The web application updates the data on SQLServer 2000 and an inhouse application updates the data on SQLServer 2005.
What is expected to happen is and also due to security reasons that a file is created for the data updated on each side and an existing FTP application will do the necessary transferrring.
I would appreciate any ideas on how this can be done and how to go about getting it done. I have no knowledge on this. Thanks for any help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think we need a bit more information before we can really offer meaningful suggestions...

by syncronization of data, do you mean one way, from 2000 to 2005, with a change list? can you simply import a backup from 2000, and create a view in the 2005 database that points to the 2000 data, so there is still a seperation and no true merge of the data?

what is the syncronization supposed to accomplish? is this for reporting, or order processing, or something else?

it sounds like you need to do two way syncronization, but is it at the database level, or just specific data/table elements?

I'd recommend restoring entire databases, and create Views that point to the other db's data for use within the db:

It's easy to restore a 2000 backup on 2005, but you'd need to export the 2005 data to a 2000 server to back it up to a 2000 format.

To get the data from 2005 to 2000, you'd need to do a import /export via DTS; can the servers see/talk to each other over the internet? otherwise, for a whole db, you'd need a 2000 server on the 2005 side just to make a DTS and then backup.

Hopefully others will have better ideas than mine.

i

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

This sounds like something that DTS/SSIS would be perfect for. Have some process copy the file to both servers and then have a package to upload it.

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 3

If you are not happy with SSIS/DTS then another option might be to look at a third party synchronization tool such as SQL Data Compare* this will allow you to generate synchronization scripts it also has an API and command line. A 14 day free trial is availible.

*I work for Red Gate and there are other third party tool vendors who offer similar packages

- James

--
James Moore
Red Gate Software Ltd