Sync up SQL 2000 DBs?

[kleinma]

We use a webhost that provides us with a SQL 2000 DB. They are a pretty good host in that they allow remote connections to the DB (many hosts do not)

This means that in addition to being able to use Microsoft's Enterprise Manager utility for managing tables and such, I can also make remote connections to the DB from my own windows applications.

So we have this big customer database sitting out there on our webserver, and we access it from here in a windows app I made. Our customers also access it via an ASP.NET app on the webserver.

My question is this:

Is there a way we can possibly use a local sql server here for faster access, but have the server here also sync up real time with the one on the webserver, so that customers accessing it will also see the same data?

Problem is that at times it can be a bit slow to access the data over the web for us when reading lots of records, where as its about 10x faster using a local DB on the network here.

[szlamany]

Do you just want the local DB for R & D work?

[kleinma]

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)

[techgnome]

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

[kleinma]

yeah I hear ya.. probably just not worth doing at this point.

[kaffenils]

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.

[techgnome]

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