Introduction :
Always On Availability Groups is a high-availability and disaster-recovery feature in SQL Server that ensures database continuity with minimal downtime. In this blog, I explain the step-by-step process to add a database to an existing Availability Group using SQL Server Management Studio (SSMS), along with key prerequisites and validation checks commonly handled by a SQL DBA.
Prerequisites (Must Check Before Adding DB)
Database is in FULL recovery model:
Always ensure to add database in the full recovery model if it doesn’t, we have to make it to the full recovery model using the below query.
ALTER DATABASE MyDatabase
SET RECOVERY FULL;
Full back up is taken (Mandatory) :
We need to ensure to take a full back up the database prior to adding in the AG,Without a full backup, SQL Server will NOT allow AG to join.
Using the below query, we can take the full backup.
BACKUP DATABASE MyDatabase
TO DISK = ‘D:\Backup\MyDatabase_Full.bak’
WITH INIT;
It’s recommended to take the log backup also.
Add Database Using SSMS (GUI – Recommended) :
Open SSMS Object Explorer
Expand:
Always On High Availability
Availability Groups
Ag_primary(for eg)
Right-click on AG primary
Click Add Database
Wizard opens → Click Next
Select the database(s) you want to add
Click Next
Review validation checks
Click Finish.
SQL Server will:
Create synchronization
Join DB to all replicas
Start data movement automatically.
Conclusion :
Adding a database to an existing Always On Availability Group is a routine but critical DBA task. From the SSMS Object Explorer shown in the screenshot, the Add Database wizard provides a safe and controlled way to include databases without downtime.
