Maintenance Plans when using An AlwaysOn Database Availability Group

Question

Hi,

 I'm planning on using a 2 node alwayson database availability group with a witness server. To start the replica sync I'll be using a backup on node 1 and doing a restore on node 2.

I'm planning on configuring the following weekly items into a maintenance plan:

dbcc checkdb
rebuild index

When setting up maintenance plans on two servers in an AAG, can I just create the maintenance plan on each node at a time or do I need to pay special attention to an AAG group?

Thanks

---

IT Support/Everything

Answer 1

Hello,

Technically you can just set it up on each node, though I would do it AFTER you've setup the AAGs and joined all of the replicas. There was supposed added for checking preferred backup locations which I do not believe will be added into the MP unless it spots an AG.

Personally I would shy away from using maintenance plans, but I understand for general use they are quick and easy. Sometimes that's all that is needed. The problem you may face, just looking at your post, is just rebuilding all of the indexes. That's going to be fully logged and need to be sent to all of the replicas participating.

---

Sean Gallardy | Blog | Microsoft Certified Master

Answer 2

Cheers Sean,

Ok, I'll setup the maintenance after I've created the AAG. You've mentioned my main thoughts - what happens when the index is rebuilt on 2 databases that are synced and how to deal with it?

My database whilst initially small could grow to 300 GB- I've had a look around for advice on AAGs and maintenance plans, but I've found information scarce, any resources you could suggest would be helpful.

Thanks

---

IT Support/Everything

Answer 3

Hello,

what happens when the index is rebuilt on 2 databases that are synced and how to deal with it?

The log blocks must still be shipped over to all replicas involved. Maintenance plans run serially, so this is not a concern from a maintenance plan point of view but more from an index maintenance, log, and network point of view. If you rebuild a 100 GB clustered index, that's going to require a good bit of log space. Since those blocks need to be sent to other replicas, they will be sent out as fast as they can. If you have other requests coming in or other items going on, you can see it's very easy to cause artificial slow downs. Maintenance plans don't take anything into account when rebuilding indexes, all are rebuild fully, period. This is why I pointed out the potential issue, especially if your AG is across a WAN or slower link.

I've had a look around for advice on AAGs and maintenance plans, but I've found information scarce, any resources you could suggest would be helpful.

Unfortunately I don't use maintenance plans nor do I know of any good resources when dealing with them including AOAGs. My guess would be due to the fact that AOAG are an advanced feature and at that point the maintenance is being taken care of in other ways, or there are special routines just for the AOAGs.

I would say that you could expand your search to include mirroring and maintenance plans and that will be very close in terms of outcome and potential issues.

---

Sean Gallardy | Blog | Microsoft Certified Master