I have properly configured transactional replication in SQL Server between two servers. I have a publisher that is configured with local distribution and a single transactional publication with updatable subscriptions. The second sserver subscribes to the publication and is configured to simultaneously commit changes back to the publisher.

When the subscriber machine is down, changes can be made at the publisher without any errors. When the subscriber comes back online, the changes propogate from the publisher to the subscriber as expected.

However, when the publisher is down, attempts to make changes at the subscriber will not committ. When I attempt to commit while the publisher is down or MSDTC service is stopped, I get an error specifying that MSDTC on the publishing server is not available. When I attempt to commit while the publisher's SQL Server DB Engine is stopped, I get an error specifying that the login failed to SQL Server on the publshing server.

Is this expected behavior? That is, does my configuration prevent the subscribing server from committing transactions while the publisher is offline?

Any help will be greatly appreciated. Thank you!


From How Updatable Subscriptions Work:

For each change made at a Subscriber, MSDTC manages the two-phase commit operation between the Publisher and the Subscriber committing the change.

So it is pretty clear that the subscriber and publisher have to be both online to apply any change to the Subscriber when Immediate updating is used. It is also called out very expicitly here:

  • Immediate updating. The Publisher and Subscriber must be connected to update data at the Subscriber.

If you want the Subscriber to be available for updates when the Publisher is offline you need to look into Queued updates.

  • Thank you for the timely response. Queued updates are the embarassingly obvious solution. – user35422 Feb 22 '10 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy