Introduction 

Automating SQL Server Availability Group Management is essential for maintaining high availability and disaster recovery for critical databases. However, as organisations grow, manually adding databases to AGs and performing backups can become time-consuming and error-prone. 

In this blog post, we’ll explore an automated approach to AG management, focusing on streamlining the process of backing up databases and adding them to Availability Groups in high-availability setups. 

Business Scenario: Automating SQL Server Availability Group for High Availability 

The Challenge 

Imagine your organisation runs critical applications supported by SQL Server Availability Groups. Your setup includes primary and secondary replicas for high availability. However, as development teams frequently create new databases, these databases are often not automatically added to the AG, leading to operational inconsistencies. 

Moreover, ensuring up-to-date backups for these databases before adding them to AGs is critical for safeguarding against data loss. As the number of databases grows, performing backups manually becomes labour-intensive, especially in environments with multiple replicas. 

Our Solution 

To address this challenge, we designed an automated process that: 

1. Automatically backs up databases before adding them to the AG. 
2. Verifies that databases are not already part of the AG before adding them. 
3. Ensures the job runs only on the primary replica. 
4. Leverages SQL Server Agent Jobs for scheduling and execution. 

Take your expertise to the next level with our
Oracle SQL Training

The Automated Approach 

Steps to Automate AG Management 

1. Check Primary Replica: Confirm that the server is the primary replica of the AG. 
2. Identify Databases: Loop through databases that are not already part of the AG. 
3. Perform Backups: Execute a full backup for each database before adding it to the AG. 
4. Add to AG: Add the database to the Availability Group. 
5. Handle Errors: Implement error handling and logging to track any failures. 

SQL Script for Automation 

Below is the SQL script that implements this approach: 

Check if the current server is the primary replica  
IF sys.fn_hadr_is_primary_replica('DatabaseName') = 1   
BEGIN   
    PRINT 'This server is the primary replica. Proceeding with the job...';   
    DECLARE @dbName NVARCHAR(128);   
    DECLARE @sql NVARCHAR(MAX);   
    DECLARE @backupPath NVARCHAR(255);   
    -- Cursor to loop through databases not in the AG   
    DECLARE db_cursor CURSOR FOR   
    SELECT name   
    FROM sys.databases d   
    WHERE name LIKE 'ABCD%'  -- Adjust the pattern if needed   
      AND name NOT IN (SELECT database_name FROM sys.availability_databases_cluster)   
      AND state_desc = 'ONLINE';  -- Ensure the database is online   
    OPEN db_cursor;   
    FETCH NEXT FROM db_cursor INTO @dbName;   
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        -- Full Backup before adding the database to the AG   
        SET @backupPath = 'C:\Backup\' + @dbName + '_FullBackup.bak';   
        PRINT 'Performing full backup for database: ' + @dbName;   
        BEGIN TRY   
            BACKUP DATABASE @dbName TO DISK = @backupPath WITH INIT, COMPRESSION;   
            PRINT 'Full backup completed successfully for database: ' + @dbName;   
            -- Add database to the AG   
            SET @sql = 'ALTER AVAILABILITY GROUP [AG_NAME] ADD DATABASE [' + @dbName + ']';   
            PRINT 'Adding database: ' + @dbName;   
            EXEC sp_executesql @sql;   
            PRINT 'Successfully added database: ' + @dbName;   
        END TRY   
        BEGIN CATCH   
            PRINT 'Failed to backup or add database: ' + @dbName;   
            PRINT ERROR_MESSAGE();   
        END CATCH;   
        FETCH NEXT FROM db_cursor INTO @dbName;   
    END;   
    CLOSE db_cursor;   
    DEALLOCATE db_cursor;   
END   
ELSE   
BEGIN   
    PRINT 'This server is not the primary replica. Job will not run.';   
END;    

Key Features of the Script 

1. Backup Before Adding to AG: Ensures a full backup is performed for every database before adding it to the AG, safeguarding data. 
2. Primary Replica Verification: Confirms the script runs only on the primary replica to avoid conflicts. 
3. Automated AG Addition: Simplifies the process by adding databases to the AG without manual intervention. 

Why Automate This Process? 

Benefits: 

• Reduced Human Error: Prevents mistakes such as skipping backups or misconfiguring AGs. 
• Consistency: Ensures all databases added to the AG are backed up, preserving data integrity. 
• Time Savings: Frees up time by automating repetitive tasks like backups and AG additions. 
• High Availability: Keeps critical applications resilient with an up-to-date AG. 

Implementation in SQL Server Agent Jobs 

To fully automate this process, schedule the script in SQL Server Agent: 

1. Create a New Job: In SQL Server Management Studio (SSMS), create a new SQL Server Agent Job. 
2. Add Job Steps: Add the script to a job step. 
3. Set a Schedule: Define the frequency of execution (e.g., daily or weekly). 

This setup ensures new databases are backed up and added to the AG automatically, improving operational efficiency. 

Conclusion 

Managing SQL Server Availability Groups manually can be a challenge as database numbers grow. Automating the backup and AG addition process not only saves time but also reduces errors and ensures high availability. Implementing this solution with SQL Server Agent further enhances reliability and minimises downtime, keeping your organisation’s critical applications running smoothly. 

Author: Kashif Baksh is a Senior Principal Consultant at Fusion Practices