MSSQL 2016+ Auto seeding feature on AG

 Fixing broken AG..

Without auto seeding:

You will need to ..

1. Ensure endpoints in AG properly configured

2. Suspend HADR (primary)

ALTER DATABASE [DBName] SET HADR SUSPEND;

3. Remove the db from AG (primary)

ALTER AVAILABILITY GROUP [AGName] REMOVE DATABASE [DBName];

4. Drop the database (Secondary)

DROP DATABASE [DBName];

5. Backup the database + TX log (Primary)

BACKUP DATABASE [DBName] TO DISK = 'C:\Backup\DBName_Full.bak' WITH COMPRESSION; BACKUP LOG [DBName] TO DISK = 'C:\Backup\DBName_Log.trn' WITH COMPRESSION;

6. Copy the backup

7. Restore them in target  (Secondary)

RESTORE DATABASE [DBName] FROM DISK = 'C:\Backup\DBName_Full.bak' WITH NORECOVERY;

RESTORE LOG [DBName] FROM DISK = 'C:\Backup\DBName_Log.trn' WITH NORECOVERY;

8. Join AG (Secondary)

ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = [AGName];

9. Add db back to AG (Primary)

ALTER AVAILABILITY GROUP [AGName] ADD DATABASE [DBName];

10. Resume HADR (primary)

ALTER DATABASE [DBName] SET HADR RESUME;

Verify DB error log in each step apart from the feedback message for each command. So ideally it demands a lot of effort.

With Autoseeding: 

The manual db backup, backup copy, restore of the same is completely ruled out. Rest stays. Step sequence:

1. Ensure endpoints in AG properly configured

2. Suspend HADR (primary)

ALTER DATABASE [DBName] SET HADR SUSPEND;

3. Remove the db from AG (primary)

ALTER AVAILABILITY GROUP [AGName] REMOVE DATABASE [DBName];

4. Drop the database (Secondary)

DROP DATABASE [DBName];

5. Add db back to AG (Primary)

ALTER AVAILABILITY GROUP [AGName] ADD DATABASE [DBName];

6. Post the db addition back to AG, with AG property set to “SEEDING_MODE = AUTOMATIC”, AG starts auto seeding the database from primary to secondary. Just plan it properly such that we don’t halt business.

7. Resume HADR (primary)

ALTER DATABASE [DBName] SET HADR RESUME;

Refer: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/automatically-initialize-always-on-availability-group?view=sql-server-ver17

Additional Info:

1. Oracle FSFO is comparable to MSSQL’s AG. AG uses WSFC & quorum disks vs oracle uses observer node.

2. Oracle’s flashback based reinstatement of the old primary to act as standby or secondary isn’t available in MSSQL, we will need to restore the whole db in case of disaster.

3. Duplicate target db is a feature of oracle 11.2 which was released atleast 5 or 6 years before MSSQL. The duplicate db feature can be used to build standby or create a clone of prod.