• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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!
0
moruda
Asked:
moruda
  • 5
  • 3
1 Solution
 
Kevin3NFCommented:
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 :-)
0
 
morudaAuthor Commented:
Triggers are overhead -- also, we cant use autoincrement ID without getting complicated...how do we get this done?
0
 
Kevin3NFCommented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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?
0
 
Kevin3NFCommented:
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...
0
 
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
0
 
Kevin3NFCommented:
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
0
 
Kevin3NFCommented:
thanks :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now