Forum Discussion
SQL Server replication to read only secondary?
Hi,
I currently have a SQL 2019 instance with a database that some application writes data to, this data is later read (only read) by other applications.
The plan is to split the readers from the writers, the goal is to have a solution where I have the editable master database where applications can actually change the data more secured on one instance and a replica/copy of the same database on another instance where other applications can read the data but not change anything.
Networkwise its preffered that any communication is initiated from the R/W instance to the R/O instance but not the other way around.
So even if the "read only" copy somehow is compromised or lost I always have the master r/w database available to just re-sync.
I need suggestions on the best way or method to accomplish this.
Both db's have to be accessible at the same time.
2 Replies
AyushBansalMicrosoft
Two methods of doing this that I would suggest are:
Always On Availability Groups (AG): Create an Always On AG with read-only routing enabled. The primary replica (R/W) is the only instance that can write data, and secondary replicas are set to read only for all other instances. The applications connect to the listener that automatically routes read only traffic to the secondary replica and R/W to primary. If a failover occurs, then one of the secondary replicas will become the primary replica ensuring high availability.
Create & configure availability groups (content index) - SQL Server Always On | Microsoft LearnSQL Server Replication: Use Transactional Replication in order to replicate data from the master read-write (R/W) database to a secondary read-only (R/O) database. The R/W instance pushes changes while the R/O replica pulls the changes but is strictly for reads. Configure the application connection strings to connect to the publisher for read-write operations and the subscriber for read-only queries.
Tutorial: Configure Transactional Replication - SQL Server | Microsoft Learn
You might have to explore these to match your exact requirements.I second using read only routing for Always on Availability Groups (AGs), but would mention two caveats from the start:
- Readable secondary replicas are only supported with "full" AGs, i.e. Enterprise edition.
- In order to connect to the read only replica, the application needs to include it's read only "application intent" in the connection string, which is an optional parameter defaulting to read/write.
