I have a situation where we have AlwaysOn Setup in SQL Server 2016 Enterprise editions with primary in region A and secondary in region B setup in Asynchronous-Commit mode. We have 4 databases in participating in this AG. Now we would like to add all another secondary in same cluster in Region A to different server. Out of 4 databases we only want to add 3 databases to 2nd secondary which will be in region A. Last database is heavy write application which grows quickly and we do not want to sync it between 2 secondaries. Is it possible to achieve and is there any performance effect of it?
Sql-server – Setup AlwaysOn with 2 Secondaries and not adding all the databases for 2nd Secondary
availability-groupssql-server-2016
Related Question
- SQL Server 2016 AlwaysOn DBCC CHECKDB on Async Replica – How to Guide
- SQL Server AlwaysON – Start Sync with Secondary for Selected Databases
- Sql-server – Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name
- Sql-server – Load balancing reads SQL Server 2016 AG
- SQL Server 2017 Availability Groups – Impact of Long-Running Transactions on Secondary Replica
- Sql-server – Always On Setup – OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory
- SQL Server AlwaysOn AG – Checkpoints on Secondary Replica
- AlwaysOn availability group DR failover with Powershell
Best Answer
You cannot exclude databases from replicas in an availability group. There may be a way to accomplish this with a hack, but it would be an unsupported configuration.
In order to accomplish what you are specifying, you need two availability groups. Availability group A would have all three servers as replicas, and would contain only the databases that you want to be replicated to all three servers.
Availability group B would have two servers as replicas, and would contain the one database that you want to be replicated to only those two servers.