AlwaysOn Secondary Parallel Redo Workers
When your secondary replica server calculates the parallel redo worker threads and you have many databases, due to the 100 max parallel redo worker threads in an instance and 16 per database, a lot of databases become single threaded. Some of these databases could be under heavy load with writes, which causes the Redo Queue to get significantly behind and can cause extended downtime if a failover occurs.
Please give option to choose which database for parallel redo and to allocate the number of redo threads per database.
I should be able to select the database for parallel redo and should be able to allocate the number of threads if needed.
I should also able to disable/enable parallel redo at the database level.
Kevin McDonnell commented
DCR should be filed on this to correct it. This should be a database option. Otherwise, customers may be forced to go back to using SAN replication methods (SRDF).
Per your own documentation: SQL Server 2017 introduces two different architectures for availability groups. Always On availability groups provide high availability, disaster recovery, and read-scale balancing.
In this scenario you're not providing High Availability for Disaster Recovery if you're allowing replicas to fall behind
Dan Carollo commented
I would love to be able to configure the number of parallel threads dedicated to Redo -- for each database. For example, if there are 100 available threads, and we have 10 databases on our secondary, we should be able to configure 10 threads per database (instead of default 16).
Either that or increase the total MAX threads to something greater 100. This impacts our environment as well.