November 28, 2013 at 6:20 am
I have setup transactional replication at a customer site with SQL 2008 R1 SP1 Enterprise Edition residing on Windows 2008 R2 Enterprise Edition x64. The replication is configured with one publisher, distributor (on the same engine as Publisher), and subscriber.
Replication is going okay and latency is excellent. And, undistributed commands grows to some amount and returns to zero with estimated time to apply at 0 seconds.
Also, note, I fixed a known bug with SP for distribution cleanup so that the proper amount of commands and transactions are being retrieved for deletion.
However, I noticed that the the number of commands and transactions keeps growing in the distribution database. Currently I show 6,388,422 records in the transactions table and growing and 32,998,193 records in the commands table and growing. The Distributor is configured as default with min and max retention period is 0 & 72, respectively with history at 48.
What I want to know is there a technique, calculations, or guideline to configure the distributor properties? I have Google'd but, what I find is where to configure these values. I am looking on how to change the values in a proper way to tune the distributor.
November 29, 2013 at 5:01 am
What are you thinking is an issue?
Is it the distribution DB is too big?, with the latency and undistributed commands details you have provided it appears your replication is running as expected.
Reducing your max rentention period will store less within your distribution db, but the longer you have available will mean that in the event the subscriber is down for a sustained period, as long as that period is smaller than the retention period the subscription will not expire.
MCITP SQL 2005, MCSA SQL 2012
November 30, 2013 at 10:33 pm
RTaylor2208 (11/29/2013)
What are you thinking is an issue?Is it the distribution DB is too big?, with the latency and undistributed commands details you have provided it appears your replication is running as expected.
Reducing your max rentention period will store less within your distribution db, but the longer you have available will mean that in the event the subscriber is down for a sustained period, as long as that period is smaller than the retention period the subscription will not expire.
Hi RTaylor2208,
Thank you for your reply.
You are correct, I don't see an immediate issue at this time but, I do see a continually growing commands and transactions table (However, I am continually monitoring and seeing the counts beginning to come down. Will try to see if there will be an average amount of records for transactions and commands in the meantime.). Yes, I understand that the subscriber will not expire under the given circumstance. I just wondered if there is an optimal way to configure the distributor properties other than accepting the standard default when replication is setup? You are correct in that, at this time I should leave things as is until I need to make a change. Is there an advice you can provide on configuration of such properties?
December 2, 2013 at 3:00 am
This thread may be of some use for tips on optimizing the distribution db.
http://www.sqlservercentral.com/Forums/Topic892451-60-1.aspx
MCITP SQL 2005, MCSA SQL 2012
December 2, 2013 at 3:11 am
Thank you very much I will take a look.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply