6

I am working in a test situation and have already successfully created a distributed availability group with automatic seeding. I cannot figure out how to add another database to the distributed AG. I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG. I get the following error:

Msg 15151, Level 16, State 1, Line 28
Cannot alter the availability group 'xxxx', because it does not exist or you do not have permission

Attempting to modify the permissions for my group login yields the same message, yet the group is under the sysadmin role. Surely there is a way to add a database after you create the distributed AG?

5

I am working in a test situation and have already successfully created a distributed availability group with automatic seeding.

Automatic seeding is a property of the replica not a distributed availability group.

I cannot figure out how to add another database to the distributed AG.

Interestingly I went to find the MSDN BOL page to show this... and there isn't one. That definitely needs to be corrected and I'll follow up on that.

I successfully added the new database to the primary AG, but the alter availability group statement does not seem to work on the distributed AG.

Distributed AGs don't hold databases, they hold availability groups. Availability Groups hold databases, that's where we need to go.

Surely there is a way to add a database after you create the distributed AG?

Surely there must be, as to create a distributed availability group the secondary availability groups must be void of all databases.

How to add a database to an existing distributed availability group:

  1. Create the new database on the distributed availability group's primary replica for that availability group.
  2. If you are using automatic seeding, it should seed the database to the other replicas in the primary replicas group. If you're not using automatic seeding, backup and restore the database to the secondary replicas leaving it in a restoring state. Then join the database to the local availability group.
  3. Regardless of the automatic seeding setting, you'll need to backup and restore the database to each secondary distributed availability group. Restore the database and leave it in a restoring state. Then join the database to the local availability group. Do this for each secondary availability groups' replicas.
  4. Have yourself a treat because that's a bunch of work.
  • Just tried this with various permutation, and it still will not work. Creating the database on the primary succeeds; I can also add the database to the primary AG and it does in fact automatically seed over. – JohnH Nov 4 '16 at 15:05
  • I then take a backup of the db and restore on all replicas of the secondary AG; they are restored with the no recovery option. Then I attempt an alter AG statement to add the database, and receive the following message: Msg 41190, Level 16, State 7, Line 14 Availability group 'xxxx' failed to process add-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command....btw, I am struggling with comment size limit. – JohnH Nov 4 '16 at 15:11
  • Finally found the secret sauce...it was the way I was trying to "join" to the secondary AG. Apparently using ALTER AVAILABILITY GROUP...ADD DATABASE was not correct. From another blog post I saw that the command I needed was ALTER DATABASE <name of database> SET HADR AVAILABILITY GROUP = [<name of second AG>]. So to recap, all your steps worked, I just needed to "join" in the proper manner. I will mark yours as the answer, and thanks for the help. – JohnH Nov 4 '16 at 16:24
0
  1. Adding a database in a distributed availability group is not supported yet. The primary node on-site 1 contains the metadata which does not support to add the database. There is a nice article to implement distributed availability group on mssqltips (link).

Moreover to replicate the newly create a database from the primary site to secondary site would be accomplished through automatic seeding functionality. To add a database which is just created, try to initiate command on the Primary site availability group instead of the distributed availability group.

ALTER AVAILABILITY GROUP [SITE_AG]  ADD DATABASE [TESTDB]

Once the above command triggers, it will replicate the database to site b.

  1. If there is a requirement to remove the database, initiate the below command on the Site 1 Availability group.
ALTER AVAILABILITY GROUP [SITE_AG]  remove DATABASE [TESTDB]

Then connect to the availability group of site 2 and initiate the below command.

ALTER DATABASE [TESTDB] SET HADR OFF;

Once you execute the above statement, the database state will turn to restoring mode, and then you can drop the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.