SQL Server Performance Forum – Threads Archive
synch data into high traffic
One source db:– gets updates every 30 minutes.
– 230,000 records total. approx. 10% updated per day. Three identical destination db’s: (different physical servers)
– main web site db’s that gets 3 million page views per day.
What are the best options to get data changes synced in such high usage db’s?
New requirement to synch this changes data after the program runs every 30 minutes.
transactional replication sound like the go here… The data on the receiver side is readonly? or can it be updated there? Cheers
Twan
data on the destination db’s are mainly read-only yes.
Also(just remembered) – we have a denormalized search table that the main sp hits first.
This would need to be populated also.
doh.
Hi ya, still sounds like transactional replication to me… you could have the denormalized table periodically synch’ed on the source database and then just replicate it out to the destination databases…? This would work well, especially if the tables being replicated out are read-only. If they are not read only then how do you see changes propegating back? Cheers
Twan
so the main sp hits the denormalized search table on EVERY search to the DB.
– This table has ~ 230,000 records.
– This denormalized table is truncated and re-populated once nightly currently. What will happen to that table when transactional replication starts?
– the table will have been re-populated on the source (although there will be minor chnages each time). But if the table has been truncated and re-populated, will trans. rep. have to re-synch this whole table…this affecting the main search sp correct???
– if so, how long would trans. rep. take to synch approx. 230,000 records?(if it has to do this due to truncation)
Hi ya, yes transactional replication would need to do the same thing i.e. delete all rows and reinsert them all. it would be beneficial if the synching process on the source database didn’t truncate and repopulate, but instead only made the changes it needed. It will make the synching a bit more complex, but would make the replication faster alternatively snapshot replication, but it will mean that the table on the dest is temporarily unavailable while the snapshot happens… The only way to know for sure is to do this on a test environment and see which is better for you
Cheers
Twan
since I have 3 separate physical locations that will need the data replicated to it, could I choose replicate to each of these at different times? I plan on: 1. Taking down a location
2. Transaction replicate to it or snapshot replicate to it.
3. Bring the location back online.
4. Move on to the next location etc… BUT, I will need all 3 locations to have the EXACT same data set. Can replication do this?
(replicate a snapshot of the database.)
(Ex. Replicate the state of the db as it was at 2:00pm.)
Hi ya, replication is a two stage process, the first is when the source databases essentially gives all new transactions (or the snapshot) to the distribution database. the second then distributes this data to the destination databases, so as long as your logread job (or snapshot job) happens after the data has been distributed to all three databases then that will be ok. In our environment this means doing a logread on Mon, Wed, Fri and a distribution to the databases on Tue Thu Sat. But there is no reason why that process can’t be sped up. If you were to use transactional replication then you can leave it all set to continuous. This will mean that there is a risk that the data is slightly out of sync if communication is lost between the distributor and a destination database. But the replication is so simple and in general networks tend to be pretty resilient that it may be worth the risk Cheers
Twan
]]>