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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin HillSr. SQL Server DBACommented:
Replication is your best bet here.  

What overhead are you referring to?

I have been down this path with a customer before, so I feel yer pain :-)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
morudaAuthor Commented:
Triggers are overhead -- also, we cant use autoincrement ID without getting do we get this done?
Kevin HillSr. SQL Server DBACommented:
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

morudaAuthor Commented:
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?
Kevin HillSr. SQL Server DBACommented:
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...
morudaAuthor Commented:
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
Kevin HillSr. SQL Server DBACommented:
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
Kevin HillSr. SQL Server DBACommented:
thanks :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.