SQL Server AlwaysOn Availability Group Automatic Seeding – How to Implement

availability-groupshigh-availabilitysql serversql-server-2019

I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error in which your database already exist ! what is the solution?

Best Answer

You should be able to see the reason automatic seeding didn't complete by looking in the SQL Server error log from the time period where you created the AG (on both the primary and secondary replicas).

One common cause for this failure is that the Availability Group doesn't have the "CREATE ANY DATABASE" permission on the secondary. To correct that, you need to go to each secondary replica and run this command:

ALTER AVAILABILITY GROUP yourAgName
GRANT CREATE ANY DATABASE

Another place to look is the automatic seeding DMV. This query is from the MS Docs:

SELECT start_time, 
    completion_time
    is_source,
    current_state,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding

Check out the failure reason for clues as to why automatic seeding isn't working.

After correcting any issues you find, you can retry automatic seeding by running this code on the primary:

ALTER AVAILABILITY GROUP yourAgName
MODIFY REPLICA ON 'SecondaryReplicaName' 
WITH (SEEDING_MODE = AUTOMATIC);
Related Question