AlwaysON Maintenance Plan

Question

Hello Guru's, 

I've setup a two node SQL cluster with Always ON. 

There are number of databases whicih are part of the Availability Group.

Both the node use non shared disk. They both allow all connections on Availability Group properties. 

I'm going to create some maintenance plan. Do I've to create separate maintenance plan (Indexing, Update Stats, DBCC CHECKDB etc for both the server)? 

Also do I've to backup databases from both server separately?

Best regards, 
Mohan  

Answer 1

You don't need to backup from both server separately. You only need one. You need to come up with a plan on how you are going to backup , what is your backup preference primary or secondary etc.

http://info.tricoresolutions.com/blog/understanding-backups-with-sql-server-alwayson-high-availability-mirrors 

I am not sure whether using maintenance plan for indexing, update stats etc is a good idea. Try Ola Hallegran's free scripts here.

https://ola.hallengren.com/ 

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

I would second the suggestion of using Ola's tools for maintenance rather than the built in maintenance plans.

My suggestion on backup would be to backup from the secondary as long as it is a synchronous secondary, the reason being it will remove the workload from the primary and as long as they are in sync they will be exact copies.  Since the indexes are a part of the database, those will be synchronized between the replicas.

One of the myths surrounding AGs is that you only need to perform a database integrity check (DBCC CheckDB) on the secondary, this is completely false.  The check needs to be performed on all replicas as the integrity check evaluates the content of the database as SQL knows it compared to what the disk shows, obviously with each replica being on separate disk corruption could occur on one and none of the others.

-----------------------------------------------------

Peter Shore

Answer 3

Thanks you Ashwin for the valuable input.

Ola Hallengran's script is a gem, I just implemented it. 

The concern with secondary backup is as I was reading it can be COPY ONLY, is that correct? 

Answer 4

Thanks Peter, that is quite helpful info. 

I am quite tilted towards doing the backup from Secondary replica as on our environment both replica are running on synchronous mode. 

The concern with secondary backup is as I was reading it can be COPY ONLY, is that correct? 

Answer 5

Interesting, I had not caught that before.  You can, at least, offload transaction log backups to the secondary.

https://blogs.msdn.microsoft.com/sqlgardner/2012/07/18/sql-2012-alwayson-and-backups-part-1-offloading-the-work-to-a-replica/

https://blogs.msdn.microsoft.com/sqlgardner/2012/07/21/sql-2012-alwayson-and-backups-part-2-configuring-backup-preferences-and-automating-backups/

https://blogs.msdn.microsoft.com/sqlgardner/2012/08/28/sql-2012-alwayson-and-backups-part-3-restore/

Answer 6

https://msdn.microsoft.com/en-us/library/hh245119.aspx 

Backup Types Supported on Secondary Replicas

• BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

• Differential backups are not supported on secondary replicas.

• BACKUP LOG supports only regular log backups (the COPY_ONLY option is not supported for log backups on secondary replicas).

  A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

• To back up a secondary database, a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZEDor SYNCHRONIZING.

Yes, the secondary full backups only supports copy-only full backups, but what is exactly your concern?

http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com