SQL Server Performance

subscription expiration

Discussion in 'Performance Tuning for SQL Server Replication' started by masqazi, Dec 25, 2006.

  1. masqazi New Member

    I have transactional replication between head office and branches located in different cities. network connection can go down sometimes and hence subscription can fail occasionally. now i am concerned about its effects. can anybody throw some light on this issue with possible precautions/setup and solution.

    one particular thing is subscription expiration: i read about it in books online, but i want to know from experienced guys about their practical experiences in this type of situation. what should be our option for transtional replication: never expires+reinitialization or expires after specified interval.

    In my replication, branch db is publisher and headoffice db is subscriber. At subscriber the data is changed after it is received from publisher, so i cant reintialize it now.
  2. MohammedU New Member

    You change the publication to NEVER EXPIRES in publication properties...
    By default ditribution jobs scheduled starts when the SQL Agent starts...
    but you can add another schedule to the same job to run every 10/30 minutes so that for any reason connection losts between the servers ditribution will fail by adding second schedule it will start without any manual intervention..

    I think you can reinitialize the subscription without changing the data at subscriber...it is top of my head but check in PUBLICATION PROPERTIES/SNAPSHOT TAB...


    Mohammed U.
  3. madhuottapalam New Member

    (a) You must choose Subscription Never Expires option in any case. By default it has 336 hrs(if i recall it correctly).
    (b) These kind of architecture is always tricky. As Mohammed said u can keep schema and data at the subscriber as unchanged when u reinitialize, but it will lead to data inconsistency, so I would say don#%92t do it. My question is why do not u go for a updatable Subscriber. If u don#%92t have the persistent connectivity then u can go for Queued updating. By going for this, u have both the publisher and subscriber with same data and if at all u need to reinitialise then it will not be a problem.

    Madhu

Share This Page