replication type advice
-
Hey all,
I currently have a SQL 2000 DB on another site, which is in continual use for business.
We have just had a VPN put in so that we can have a DB here so that the staff here can do some accounts stuff, and others can deal with business.
My question - what replication type??
The DB is quite large (50000 rows on the main table, 5 other main tables with 20,000 each).
If i chose transactional (so that we actually updated the remote server with the VB prog) then we have the problem of it probably running quite slow - but then locking is a problem. But it if the link goes down, so does our ability to add/ammend data.
If i chose merge, then there is a small possibility of 2 people changing the same record - and then we end up with data problems. And if the line does go down (possibily with no-one noticing for a few minutes) then there is the distinct possibility that we will have 2 people access the same record before they sinc.
Any ideas much welcome.
Dan
-
Daniel,
If your app is coded using normaly employed optimistic concurrency you still are going to have the problem of two users could try to change the same data at the same time no matter what replication type you use.
You should focus on how to resolve the conflict if it happens. If the connection link is flaky and on top of that data has to be changed at both locations I would go for merge. But If you have a VPN can your remote users just connect to the remote server ?
* Noel -
We are currently using pesimistic locking.... will this help as there will still surely be instances that 2 people in different locations can open the same one till they sync.
If there is a conflict, is there any way of taking one side (i.e. earliest), but emailing the details of the overwritten one to an email address - with the complete insert).
See my worry is that, one table could be altered - in which a row can be entered.... when this happens another table needs a value updating. Concevieably 2 people could add rows in table 1 (which would be fine) but then the increment of table 2 would be incorrect!
Thanks for your advice
Dan
-
Daniel,
Merge replication is designed to do most of what you need but from application stand point you just have to be carefull to perform primary key partitioning if at all possible
On the other end conflict resolution can be performed through a custom stored procedure so that you can do what you please with it
And if your primary key can not be partitioned replication of any kind is not the solution for you. In such a case you will have to try to connect your remote users directly to the DB using the thinnest possible client.
hth
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply