Re: Sync up SQL 2000 DBs?
Do you just want the local DB for R & D work?
Re: Sync up SQL 2000 DBs?
No. I currently have that.
I want to use another local DB as the realtime live server that we will use, but I want this DB to sync up with the one on the web.
I am sure it IS possible, but I don't know if it will be possible for me depending on what is involved and if my webhost allows it. (ie something needs to be configured on the remote web SQL server to have this work)
Re: Sync up SQL 2000 DBs?
It's possible.... I've done DB synchs before.... 1) it isn't pretty.. and 2) it will never be real time. Especialy if there's a lag in the first place..... trying to keep two DBs in synch in realtime over the internet like that would cause the server to spend all of it's time keeping up with the changes, making it virtualy useless....
If you need to synch.... I'm guessing changes need to go both ways right? .... if it was one-way, log shipping would be the way to go.... but if they need to go both ways.... there's a lot of table/architecture changes that need to happen. It may also require changes to the app itself too.
-tg
Re: Sync up SQL 2000 DBs?
yeah I hear ya.. probably just not worth doing at this point.
Re: Sync up SQL 2000 DBs?
If both servers shall allow writing and the sync must go both ways then the only thing I can think of is using merge replication for the job.
It's not that much of a job to set up, buth there are a few things you should consider before using it. E.g. a new GUID column and three new triggers are added to each table. New stored procedures and views are added for each table. These are all part of the mechanism that keeps the databases in sync.
Replicated tables with identity columns must be set up to have different seeds on each replicated server (subscriber). Any triggers except those used for replication purposes, should be modified to include NOT FOR REPLICATION. That prevents them from being triggered when the replication engine inserts/updates/deletes them on the replicated server. You should also consider adding NOT FOR REPLICATION on indexes and constrainst for the sam reason.
Re: Sync up SQL 2000 DBs?
Yup... that's exactly the architecture changes I was talking about....it sucks it really does, and there's still no way to do it in real time....
-tg