In my lab, I decided to play around with the automatic seeding functionality that is part of Availability Groups. This was sparked by my last post about putting SSISDB into an AG. I wanted to see how it would work for a regular database. When I attempted to do so, I received the following error:
Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
This seemed odd to me. What would cause this error? What changed in my environment?
First, I realized that I did break down my AG and recreate it in management studio. When I created the group, I did not put any databases into the group, so I cold test automatic seeding.
Next, I decided to go through the process and instead of using the GUI, I scripted out the commands and would run manually. This way I could pinpoint where the process was failing.
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. :Connect sql2016n1 USE [master] GO ALTER AVAILABILITY GROUP [Group1] MODIFY REPLICA ON N'SQL2016N2' WITH (SEEDING_MODE = AUTOMATIC) GO USE [master] GO ALTER AVAILABILITY GROUP [Group1] ADD DATABASE [WWI_Preparation]; GO :Connect sql2016n2 ALTER AVAILABILITY GROUP [Group1] GRANT CREATE ANY DATABASE; GO
When I ran the above code, the steps that were to run on SQL2016N1, succeeded, but those that were to run on SQL2016N2, failed. Additionally, I received the below message:
Msg 15151, Level 16, State 1, Line 24
Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission.
I went and looked at the AlwaysOn High Availability folder in SSMS on SQL2016n2 and found nothing.
So, the group cannot be given permissions to create any database, because the group did not exist. Then it dawned on me as to why.
Creating an availability group, without databases using the GUI, only creates the group on the primary. It does not reach out to the secondary replicas to join those nodes to the group. This must be done manually. So to resolve the error, I had to run this code on SQL2016N2:
USE [master] GO ALTER AVAILABILITY GROUP Group1 JOIN GO
This allowed the secondary replica to join the AG that was established on SQL2016N1. Then I was able to proceed with the next step of running
USE [master] GO ALTER AVAILABILITY GROUP [Group1] GRANT CREATE ANY DATABASE; GO
Once this was completed, the database started seeding from one node to the other.
I think the biggest takeaway from this is the importance of using a script to do DBA actions instead of the GUI. It allows for a more focused and deliberate execution, where you can see things happen in order. This, in turn, teaches you and allows for greater flexibility, when things go bump.