I am trying to manage database replication in SQL Server 2005. I have set up my publications, distributors and subscriptions and it seems to work properly at the start.
However, after a period of time, my subscription becomes deactivated, and requires reinitialization.
This is transactional publication, with a non-updatable, pull subscription. The publication has the "Subscription Expiration" option "Subscriptions never expire, but they can be deactivated until they are reinitialized" selected. The publishers and subscribers are on the same LAN.
What are the common reasons for a subscription to be deactivated?
For instance, could an insert failure on the subscriber like the following (due to badly handled schema change) cause a subscription to become deactivated?
Cannot insert explicit value for identity column in table 'SOME_TABLE' when
IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)
If this can cause a subscription to be deactivated, what are some other scenarios to look out for?
Best Answer
Yes that would be the cause. When the insert fails, the replication will then fail. Once the replication is in a failed state you've got to fix the problem within a few days before the subscription will expire and you'll need to reinit it.