Sql-server – Transactional replication failure handling

sql serversql server 2014transactional-replication

Web Application is High school record management system (HSRMS). Users of the application are

  1. High school management (Normal Users)
  2. Data verifying users (Admin)

User group 2 will allow the User group 1 to submit data periodically and then user group 2 will validate it and ask for corrections. Hope the system flow is quite clear.

Replication Plan (Proposed)

I have setup a proposed transactional replication as

  • Publisher (Server A)
  • Distributor (Server B)
  • Subscriber (Server(s) i.e C,D,E etc).

Note: All the servers will be in the same area/location.

What I understood is that the publisher should be the database server which should be connected to the HSRMS application, the distributor contains the transactions(updates) and is responsible to carry forward to subscribers, but I need to record a server status on any agent which should notify the sysadmin to update their DB server address in case of server failure (due to any reason) and the subscriber must be responding meanwhile.

  1. Is my approach and understanding correct or I need to dive deep into it?
  2. Is there any mechanism to keep the application server aware of the failure and act accordingly?
  3. Dynamic Server connection upon failure of any db server? I.e Instead of sys admin the server connection should be automatically mapped to the active running db server?
  4. What could be the role of subscribers if the application is just connected to the Server A i.e Publication for updates? Are subscribers there only for the DB backup and will not interact with the web application?

Best Answer

  1. Yes, your understanding is correct. Usually, the Publisher and Distributor are the same servers.
  2. Yes, there are jobs that are automatically created that notify you when the replication is down. After creating the replication, check your SQL Server Agent and see the new jobs. You can alter the schedule and the notification properties. But you can create your own job that checks some latest record in two instances if they have linked server to one another.
  3. That one I think you will have to set it up by the application.
  4. Subscribers can get queries and read operations, that will let you the publisher with less I/O and tend to be faster. Updates will work though, so you will have to treat it so that the user knows that it may not persist.
Related Question