Expiration of Subscription ...

Hi,

Description: In the past I had an issue with the replication that suddenly became expired (no reason at all) and I had to re-do the whole thing from scratch. This is a transactional replication (SQL2.5K -> SQL2.5K) and the subscriber is used as warehouse where applications also inserting data (with neg. seed values for the PKs). This means applying the snapshot from the publisher is not applicable because there are data which will be lost from subscriber.

Question: Since the applying snapshot is not an option for reinitializing the subscription (initially was created without snapshot - from the backup and restore is not applicable at this stage) I need to find implementation way for two things:

1. I need subscription never get marked as expired (I did disable job "Expired subscription clean up" but still the replication can be marked as expired from the "Distribution clean up" job which I am not suppose to disable - this has different function as well - cleaning up the delivered transactions)

2. Safely dropping articles from the publication without applying snapshot to the subscriber (see reason above - num.1). If you force to drop the articles, does this mean that the subscription will be marked as expired after period of time? (This again will lead to problems as above).

Please, give me your opinion and/or best practices how to solve these problems. Other than that I am very happy with the improvements in the replication domain in SQL 2005.

Regards,

Zlatko

Hi,

1) Question 1: transactional replication uses maximum distribution retention period and publication retention period to decide when a subscription should expire. Look att BOL topic "subscription expiration and deactivitaion" on how to adjust it.

2) You can try to initialize subscription manually. For transactional replication, it means to @.sync_type to 'replication support only' when you create the subscription. Look at BOL topic" Initialize a Subscription Manually (Replication Transact-SQL Programming)" or "How to: Initialize a Subscription Manually (SQL Server Management Studio)" for instructions to setup.

Peng