sql server replication: Basically your distribution database will get very large and your log reader
agent will lock with the clean up task. Ideally you will keep the
transaction retention period as small as possible

If your subscriber frequently connects this will not be an issue.

Your History retention should be the same as your subscription retention.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

Hi,

I have a setup with central subscriber and mutiple publishers in
Transactional replication.

By default, if i have the following settings, i understand my
subscription would get deleted after 14 days.
Transaction retention = 30 days
Subscription retention = 14 days(default)
History retention = 2 days

However, i am thinking of making subscription to never expire
(@retention = 0) and control the transaction retention to align with
business constraint of 1 month.

Will this infinite subscription retention design cause any performance
issues?

Appreciate help as soon as possible.

Thanks in advance,
Lavanya

As well as Hilary's advice, you will find a degredation in performance on
synchronization. Possibly not noticeable, but will be if you have millions
of rows. This'll reset each month though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

[quoted text, click to view]

Thank you both!

Can i conclude with the following setting?
History retention = 7 days (Why should this be same as subscription
retention, other than the reason of debugging !? )
Transaction retention = 3 days (default)
Subscription retention = 40 days

-Lavanya

No, subscriptions will expire on the lesser of history retention or
transaction retention. So these should be set to the same values - probably
7. Your subscription retention should also be the same value. If a
subscriber remains offline more than 7 days, when it connects it will try to
get the transaction, but it will only find 7 days worth and will be missing
the delta and expire itself.

They all have to be the same value.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



[quoted text, click to view]

At this stage I'm not too sure which is the required time-period for your
business requirements - originally you'd decided on one month. If you are
only interested in 3 days then you just need to set them all to be 3 days,
although this is too short in my opinion. We are in a 4 day holiday in the
UK at the moment and if a problem occurred today, investigation next Tues
would be too late and reinitialization would be necessary.
Cheers,
Paul Ibison SQL Server MVP,www.replicationanswers.com

[quoted text, click to view]

Thanks!
Still , am not getting your view of setting all of them to the same
value.

But as per the SQL BOL, i understand that an subscription expiration
and deletion happens only after the subscription retention period(40
days) and subscriptions made inactive after transaction retention
period (3 days). In both case, subscriber had sync problems and would
require reinitialization.
And History retention was just to maintain the logs. I also tested
with history retention to be 1 hour and transaction retention as 2
hours and subscription retention with 14 days(default) and found the
inactive state to occur only after 2 hours when my distribution agent
was in retry state (by virtue of network diconnection as well as by
inserting guids which already existed in the subscriber).

My business requirement is like, monitoring of servers will happen
only once in a month.
As per paul's response, may be i can increase transaction retention to
7 days and history retention to 11 days.
But then Subscription retention can still be at 40 days?

Please clarify.

-Lavanya