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 ?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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 ?
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.