Problem: Sometimes we need to add databases to an existing SQL Server Always On configuration to set up High Availability (HA) and Disaster Recovery (DR) solutions for that database.

Solution: Always On Availability Groups (AOAG) is an advanced feature in SQL Server that allows us to achieve HA and DR solutions for databases. In this blog post, we will demonstrate the step-by-step process of adding a database to an existing Always On configuration using T-SQL.

Step 1: Check the Existing AOAG Configuration

Before proceeding with the next steps, it is important to ensure that the AOAG configuration is healthy. You can check the dashboard report or the Availability Group tree in SQL Server Management Studio (SSMS) to verify the state of the AOAG.

Step 2: Identify the Database to be Added

Once you have identified the database that needs to be added to the existing Always On Availability Group, you need to prepare it on the secondary replica as a secondary database. This involves restoring the database in norecovery mode with a copy of the full backup and a transaction log backup of the primary database.

Example:

USE [master];
GO
CREATE DATABASE Add_DB_AOAG;
GO

-- Create table.
USE Add_DB_AOAG;
GO
CREATE TABLE [Location] (
    [Sr.No] INT IDENTITY,
    [Date] DATETIME DEFAULT GETDATE (),
    [City] CHAR (25) DEFAULT 'Gurugram');

-- Insert Rows.
USE Add_DB_AOAG;
GO
INSERT INTO Location DEFAULT VALUES;
GO 100

Step 3: Run Full and Log Backups

Next, you need to run a full backup and subsequent log backup for the newly created database. This will be used to prepare the secondary database on the secondary replica.

Example:

-- Run Full Backup
BACKUP DATABASE Add_DB_AOAG
TO DISK = 'F:\Add_DB_AOAG.bak'
WITH INIT;

-- Run Log Backup
BACKUP LOG Add_DB_AOAG
TO DISK = 'F:\Add_DB_AOAG.trn'
WITH INIT;

Step 4: Copy Backup Files to Secondary Replica

Copy the backup files created in the previous step to the secondary replica. These files will be used to restore the secondary database on the secondary replica.

Step 5: Restore Secondary Database

Restore the secondary database on the secondary replica using the copied backup files.

Example:

-- Restore Full Backup
RESTORE DATABASE Add_DB_AOAG
FROM DISK = 'F:\Add_DB_AOAG.bak'
WITH NORECOVERY,
MOVE 'Add_DB_AOAG' TO 'F:\MSSQL12.MSSQLSERVERDR\MSSQL\Data\Add_DB_AOAG.mdf',
MOVE 'Add_DB_AOAG_log' TO 'F:\MSSQL12.MSSQLSERVERDR\MSSQL\Data\Add_DB_AOAG_log.ldf';

-- Restore Log Backup
RESTORE DATABASE Add_DB_AOAG
FROM DISK = 'F:\Add_DB_AOAG.trn'
WITH NORECOVERY;

Step 6: Add Database to AOAG Configuration

Connect to the primary replica and run the following T-SQL command to add the newly created database to the Always On Availability Group.

Example:

ALTER AVAILABILITY GROUP DBAG_ABC ADD DATABASE Add_DB_AOAG;
GO

Step 7: Validate the Change

Finally, validate the change by checking the AOAG configuration. You can use the dashboard report or expand the respective folders in SSMS to verify that the database has been successfully added to the AOAG configuration.

By following these steps, you can easily add a database to an existing SQL Server Always On Availability Group using T-SQL.

NOTE: It is important to test this solution in a lower life cycle environment before making any changes in production.