- Automatic seeding
- Full database and log backup
- Join only
- Skip initial data synchronization
Automatic seeding
The "Automatic seeding" option is suitable for small size databases. When I choose the Automatic seeding option, the database will be added to AG and created on a secondary node. I use the following DMV to check the automatic seeding status if the seeding is successful or failure.
select * from sys.dm_hadr_physical_seeding_stats
Full database and log backup
If I choose the "Full database and log backup" option, I will enter the centralized backup share in the file share path which is accessible by all the nodes in the AlwaysOn availability group.
In next step, AG wizard will verify the backup location, the database size, and database existence on all secondary nodes.
Upon successful validation, click on finish to add a database to AG.
Join only
I use this option for very large size databases.
on Primary node, take a full database backup and T Log backup of a database that is needed to add to AG.
Backup database DB to disk ='E:\DB.bak'
Backup log DB to disk =''E:\DBlog1.trn'
on Secondary, restore the full backup of the database with NORECOVERY option
USE [master]
RESTORE DATABASE DB FROM DISK = N'E:\DB.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
After a full DB backup is restored with NORecovery in a secondary node, restore a transaction logs backup with NORECOVERY option.
RESTORE LOG DB FROM DISK = N''E:\DBlog1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
Last, I will add the database to the existing AG group from a primary node using the Availability Group Wizard. Since I have already restored a full DB back up and Log, I will choose the "Join only" option.
Skip initial data synchronization
Personally, I think this option is almost the same as the "Join only" option.
I am taking the same steps as "Join only". The database and Log will be backed up and restored with the NORECOVERY option. I use this option for very large size databases.
on Primary node, take a full database backup and T Log backup of a database that is needed to add to AG.
Backup database DB to disk ='E:\DB.bak'
Backup log DB to disk =''E:\DBlog1.trn'
on Secondary, restore the full backup of the database with NORECOVERY option
USE [master]
RESTORE DATABASE DB FROM DISK = N'E:\DB.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
After a full DB backup is restored with NORecovery in a secondary node, restore a transaction logs backup with NORECOVERY option.
RESTORE LOG DB FROM DISK = N''E:\DBlog1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
Last, I will add the database to the existing AG group from a primary node using the Availability Group Wizard. Since I have already restored full DB back up and Log, I will choose the "Skip initial data synchronization" option.
Thank you for reading my post. I hope it helps you when you work on AGs.
