I've created a transactional publication in SQL Server, now my requirement is to write on primary and read from subscriber and in case of primary going down for some reason, the subscriber should act as a primary automatically.
For now all I have is a publisher that writes to the subscriber.
Best Answer
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:
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: