April 25, 2003 at 8:40 am
Got a large database (~20Gb) on central server(MS SQL2000 SP3). To reduce the load a transactional replication to another server, was established for part of the database (~40%) (MS SQL2000 SP3). The data is used for read only, but some changes from the central server are not always replicated. The database was NEVER restored from backup. What could this be?
Due to such replication problems creation a number of views on the central server was suggested. Does such solution makes much more load then transactional replication to the central server?
April 25, 2003 at 8:53 am
Transactional should work. It is really missing transactions, or maybe a problem with the filter you've applied to get the 40%? Pretty rare for a transaction to drop - I think it would take a deadlock. You can validate the publication to see if they are in sync or not.
If you want to reduce the load, replication is a good fit. Putting views on the central server doesn't reduce the load - unless maybe you index them, but I'd guess you might be trading one type of load for another, would depend on how much the data is updated.
I think before you give up on replication it's worth trying to figure out whats wrong with it.
Andy
April 25, 2003 at 9:19 am
As for filters, the table where problem was noticed, has all columns and all rows to replicate. There are some filters on other tables used for replication that might also have this problem. What can be the problem with the filter?
Central server has about 30-50 deadlocks a day.
The data replicated is for read purposes only.
There is for update trigger on the publisher's tables.
April 25, 2003 at 12:57 pm
Filter only a problem if you have data that drops out of the filter range. I'd look at the deadlocks - if they happen in the actual db it's ok, maybe data lost, but it would be lost on the publisher. Problem is when it happens in distribution db, then data is lost and you're out of sync. Depending on how important the data, you could run the validation daily or weekly, re-snapshot if out of sync.
Andy
April 26, 2003 at 2:12 am
So, if a deadlock happens on a distribution database on distributor server, such data loss is possible?
April 26, 2003 at 5:13 am
A theory, not even sure a good one. You'd think it would all be wrapped in a transaction.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply