Forum Discussion
SQL Server replication to read only secondary?
MicrosoftTwo 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 Learn
SQL 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.