I need to create below three jobs in AOAGs SQL Server.
CheckDB integrity, Update stats, Re-organize index
My question is ,Do I need to create in only Primary Replica or all the secondaries also ?
2
-
1If you shall create all services in primary replica then no need to create in secondary replica. – Md Haidar Ali Khan Aug 8 '16 at 5:31
-
1The Statistics and Index maintenance plans will propagate down to the secondaries automatically, because these are transactional operations and are logged fully. The consistency check can be run on a secondary instead of the primary, or you could restore databases and check those, on a standalone instance. – Randolph West Aug 8 '16 at 7:05
4
My question is ,Do I need to create in only Primary Replica or all the secondaries also ?
You MUST run dbcc checkdb on all the replicas at least weekly. If you can afford it daily or once in two days that would be even better. Yes you can run checkdb on all replicas because checkdb internally creates snapshot and runs integrity check on that snapshot without affecting database. For more details read Checkdb from every angle
You cannot run index maintenance on read only replicas. You have to do it on primary replica. And since the logs generated from primary replica is replayed on secondary the index maintenance changes would eventually be done on secondary replicas.Please read Recommendations for Index Maintenance with AlwaysOn Availability Groups.
For statistic,s if you are running query on secondary replica which is different from primary replica and the query on secondary requires column statistics which is not there on primary you have option to create temporary statistics. Please read
I am sure you are aware about Ola Hallengren script for index maintenance, I suggest you download it and use it. It takes care of various things on AOAG database.
