Nisarg Upadhyay
Introduction screen

Add SQL databases in an existing availability group

September 28, 2022 by

In this article, we will learn how to add a SQL database to an existing Alwayson availability group. Alwayson availability group is high availability and disaster recovery solution of Microsoft SQL Server. The database must meet the following prerequisites to be a part of an availability group.

Prerequisites to adding a SQL database in an availability group

  1. The database must be a user database.
  2. Auto-Close must be disabled.
  3. The database must be in multi-user and read-write mode.
  4. The database must be in the FULL recovery model.
  5. The database must have a full database backup.

Environment setup

I have created three virtual machines on my workstation to demonstrate the process. The details are following:

Hostname

Role

DC.Local

I have installed the Active directory role in the server and created a domain controller named DC.Local in it.

SQL01.dc.local

A primary replica of an availability group.

SQL02.dc.local

The secondary replica of an availability group.

I have created an availability group named SQLCluster using the above virtual machines, restored the stackoverflow2010 database on the Primary replica. We are adding it to the SQLCluster.

Add SQL database using SQL Server management studio

Launch SQL Server management studio on SQL01 🡪 Connect to the database engine 🡪 Expand Always On High availability 🡪 Expand Availability Groups 🡪 Expand SQLCluster 🡪 Right-click on Availability Databases 🡪 Select Add Database.

Add SQL Database in availability group

The Add Database… to Availability Group wizard starts. You can view the details and list of tasks performed by the wizard. Click Next.

Introduction screen

You can choose the database you want to add to the existing availability group. The list of databases is shown in the grid view with its status. You can select one or multiple databases from the list. In this demo, we are adding the StackOverFlow2010 database in SQLCluster, so tick StackOverFlow2010 from the list. Click Next.

Select SQL Database screen

On the Connect to Existing Secondary Replicas screen, you can view the list of replicas used to create the availability group. The SQL02 is in the list of secondary replicas. To grant the appropriate permission to the existing endpoints, we must connect to the secondary replicas. To do that, click on Connect.

Connect to secondary replicas

A Connect to Server dialog box opens. You can select the appropriate authentication method. We use Windows Authentication, so select Windows Authentication from the drop-down box.

Connect to database engine

We can select the data synchronization preference on the Select Initial Data Synchronization screen. You can select any of the following methods.

  1. Automatic seeding: SQL Server automatically creates the database and starts the seeding in this method. To use this method, the directory structure (data file path and log file path) on all participating replicas must be the same.
  2. Full database and Log backup: The wizard will start the data synchronization by generating a full backup and log backup of the database. The backups will be restored on the secondary replicas and join the availability group. We must create a network share to keep full and log backups in this method. Make sure that it is accessible from all secondary replicas and has required permissions.
  3. Join Only: This method is used when you have restored the backup of the database on secondary replicas. The wizard will start the data synchronization by joining the databases in an availability group.
  4. Skip initial data synchronization: If you want to manually generate the full and log backup of the database.

In the demo, we are using the Full database Log backup method; therefore, select Full database and Log backup. The network shares to keep full, and log backups are \\DC\AGBackups therefore, specify it in the file share path. Click Next.

Select initial data synchronization screen

The wizard will run an availability group validation test. It checks the following parameters:

  1. Shared Network location
  2. Free disk space on secondary replicas
  3. Checks whether the selected database exists on the SQL02 replica
  4. Compatibility of the data file locations on the SQL02 replica

The wizard will not continue until all validation tests are completed successfully. In our demo, the validation test was completed successfully.

Validation Test

You can verify the configuration and settings we have selected in the wizard on the summary screen. Click on the script to generate the script of the task performed by the availability group wizard. Click on Finish.

Summary of configuration values

The wizard begins the process to add the Stackoverflow2010 database in the SQLCluster availability group.

Database is being added

The time taken by the wizard depends on the time taken by the backup and restore process of the database. You can view the list of tasks completed by the wizard on the Results screen.

SQL database have been added

Once the database is added successfully, you can see it under the Availability Database node.

View SQL database under SQLCluster

You can view it in the availability group dashboard. To open the availability group dashboard, Expand SQLCluster 🡪 Right-click on Availability Databases and select Show Dashboard.

View availability database in dashboard

As you can see in the above image, the Stackoverflow2010 database has been added.

Alternatively, you can run the following query to view the list of availability databases in the SQLCluster availability group.

Output

Query to populate details of availability group

As you can see, Stackoverflow2010 has been added to SQLCluster.

Summary

We learned how to add a SQL database in an existing Alwayson availability group in this article.

Nisarg Upadhyay
Database design, High Availability

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views