0

I have a transactional replication process set up to replicate changes on a database between two SQL Server systems. The replication process is bi-directional (there is a publisher and a subscriber at both ends), except that DDL changes can only flow in one direction.

My issue is that one of the subscribers periodically loses the connection to its publisher on a push-based subscription. When you click "View Synchronization Status" on the subscriber entry, a SQL Server connection dialog box will be displayed, and after the connection is manually completed the normal View Synchronization Status is displayed. Any queued changes will then be replicated.

If the View Synchronization Status window is kept open, the subscriber will continue replicating, but if that window is closed, it will eventually lose its connection, and the replication process in that direction will stop.

I have reviewed the Schedule for all of the agents on both sides, and all of them are enabled and set to "Start automatically when SQL Server Agent starts", so the subscriber should presumably connect and run automatically.

How can I reconfigure this so that the subscriber is always running, and doesn't need to be manually connected?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richard B. Sorensen
  • 103
  • 1
  • 12
  • can you explain what you mean by bi-directional? are you talking peer-to-peer, immediate updating, queued, etc. Also, please clarify what you mean by "lose its connection". do you mean the agent fails, or the agent stops gracefully? if the latter, then check that all your agents are either running on schedule, or are running continuously. – Greg Jul 15 '15 at 21:52
  • "bi-directional" means that the changes in each server are automatically sent to the other server, so that the databases are completely in sync and identical on an ongoig basis. – Richard B. Sorensen Jul 16 '15 at 13:09
  • The subscriber does not fail - it terminates gracefully. It does stay running if I keep the View Synchronization Status window open. However, if that window is closed and if I later click "View Synchronization Status" on the subscriber, a sql server connection dialog will be displayed and the connection must be completed before the subscriber starts receiving data again. This is the case even though all of the agent schedules are set to "Start automatically..." – Richard B. Sorensen Jul 16 '15 at 13:15
  • thanks, i know what bi-directional replication is, i am asking, what kind of replication are you using - peer to peer, merge, queued, or immediate updating. – Greg Jul 16 '15 at 13:23
  • I'm not sure exactly which of these types is being used - how would I tell? – Richard B. Sorensen Jul 17 '15 at 13:54

0 Answers0