Link to home
Start Free TrialLog in
Avatar of moruda
moruda

asked on

Replication or DTC or Better?

We are implementing a new system by a client and we will have the database in both the client's office as well as the web-hosting facility. This is so that the company can function even if the connection to the internet fails -- and it does way too often. We are currently using SQL 2000 and have this set up with Merge Replication, however it is a big overhead and we feel it is too heavy for what we are doing -- but it works. We are getting web orders and they are processed in the office. Phone orders that are entered in the office will also be trackable by users online etc....This is the main jist of it, but it is way too complicated to write custom procedures to constantly handle the transfer of data. We are planning to have an additional web location and will need all databases to be synced. What is the best way to do this? We need the best quality solution. Speed of transfers is good, but a 5-10 minute delay will not make a difference.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Kevin Hill
Kevin Hill
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of moruda
moruda

ASKER

Triggers are overhead -- also, we cant use autoincrement ID without getting complicated...how do we get this done?
triggers are indeed overhead, but writing DTS packages or distrubuted queries are worse.

"we cant use autoincrement ID without getting complicated"

All distributed applications with sporadic connectivity issues are complicated....what specifically are you trying to avoid?
Avatar of moruda

ASKER

I am trying to avoid (or figure out) auto increment fields -- All I can think of is to not replicate that field. The triggers are a bit of overhead and database design changes become limited without invalidating replication for many circumstances. does 2005 have any better way to handle this? Is replication our only choice?
No idea on 2005.

Are you just looking for uniqueness, or true auto-increments?  If real autoincrement, you may need to write your own and not use SQL Server identities...
Avatar of moruda

ASKER

I was about to say I just need uniqueness, but the truth is that I need a real auto-increment-- as I will be referencing these numbers in a bunch of places
I don't know of any way you can guarantee true auto-increment across multiple, sometimes disconnected sites, unless each site can work within a range of values for its increment (Identity Range Management)....

If the connections were solid enough, maybe transactional replication with Immediate updating subscribers, but that's a "two-phased" commit process that breaks when the connection is down
thanks :-)