I've created a transactional publication in SQL Server, now my requirement is to write on master and read from subscriber and in case of master going down for some reason, the subscriber should act as a master automatically.

For now all I have is a publisher that writes to the subscriber.

  • 3
    Sounds like you should have created an Availability Group, not replication... – Aaron Bertrand May 5 '19 at 13:21
  • @AaronBertrand all my requirements can be meet using Availability Group ? i am kinda new in Availability Group – Ayham Adam May 5 '19 at 13:37

Transactional replication is not the correct technology for the requirement you've described. What you're after is High Availability/Disaster Recovery (HADR) protection for the SQL Server, and transactional replication is not suitable or intended for this.

Brent Ozar has a High Availability Planning Worksheet in his First Responder toolkit. I would recommend you take a look at that to help you determine your requirements and design an appropriate architecture.

To keep it simple, below are your main options:

  1. AlwaysOn Availability Groups
  2. SQL Failover Cluster Instance
  3. Log Shipping
  4. Database Mirroring
  5. VM/SAN replication

Which option you choose depends on your current environment (version and edition of SQL Server are important), HADR requirements, licensing, budget and in-house skills to design, implement and manage the chosen solution.

For example, if you are running SQL Server 2014 or earlier, you need Enterprise Edition to run Availability Groups, but you can use Database Mirroring (now deprecated).

Running Availability Groups or Failover Cluster Instances requires a Windows Server Failover Cluster installed on the SQL Server nodes to provide HADR - do you have skills in-house to build and manage the cluster?

If your RPO and RTO requirements are not substantial, or you're running an older version of SQL Server, then Log Shipping might be a suitable solution. You can, with some effort, implement a solution to allow log shipping to 'write-back' from the secondary to the principal server after failing over.

Lastly, if you do the discovery work and you don't have the budget for your required RPO/RTO, your infrastructure team might be able to leverage SAN replication and VM DR technology (VMWare's SRM, Azure ASR, Hyper-V Replication etc) to provide protection for your SQL Server environment.

If you can, I would suggest engaging a skilled consultant who can perform the assessment and discovery work to provide an appropriate architecture meeting all your constraints and will be able to help build and deploy the solution. You should also look at training options to get up to speed on HADR technologies so you can adequately manage the system.

Some links to help you:

Your Answer

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

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