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?
  • 1
    What edition of sql server are you using ?
    – Kin Shah
    Oct 9, 2018 at 13:44
    – dbadmin
    Oct 10, 2018 at 5:51

2 Answers 2

  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.

Since you are using SQL server 2014 EE, highly recommend to use Availablity groups for your scenario.

You just have to use Listener name in the app, so that you don't have to change it when a failover occurs.

For 2014, You can have 3 sync and 2 auto failover replica.

Replication will be pain when u have to do schema changes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.