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!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
"we cant use autoincrement ID without getting complicated"
All distributed applications with sporadic connectivity issues are complicated....what specifically are you trying to avoid?
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...
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...
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
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 :-)
ASKER