SQL SERVER – Adding New Database to AlwaysOn Replica is Slow

First, let me clarify, the words in the subject of this blog are the exact words used by my client and whatever you do and say but “customer is always right!“. Here is the complete story where we faced issue related to AlwaysOn Replica.

One fine day, I received an email from my old client.

Hi Pinal,
Need your help to understand if there is anything wrong with our deployment. We are adding new databases to an existing Always On availability group. We tried doing it and found that adding new database to availability group is taking long time.
Do you have any idea about it?

-name hidden

I asked how many replicas they have, and answer was Five and they also told that issue is happening right now. I joined call with them and found that they were adding a database to AG by using the Add database wizard. When I joined call, the restoration of the backup on one replica is progressing and it was running for 12+ hours and database size was only 60 GB.

If we really look at the wizard, it does many operations together.

1. Primary: full and log backup of the database and copy the same to all the secondary replicas.
2. Secondary: Restore full and log backups with norecovery.
3. Primary: Add database to availability group.

ALTER AVAILABILITY GROUP [AG] ADD DATABASE [DB]

4. Secondary: Join DB to availability group.

ALTER DATABASE [AG] SET HADR AVAILABILITY GROUP = DB;

SOLUTION / WORKAROUND

As I explained above, there are multiple activities which are done when we add a new database to an availability group. So, if we want to run Wizard faster, we should do first two steps manually and last two steps can be done using the wizard using “join only” option as shown below.

Hope you would understand now that the customer is always right, and you need to find alternate to prove it.

Reference: Pinal Dave (https://blog.sqlauthority.com)