Add a SQL Server Database to an Existing Always On Availability Group

By:   |   Comments (3)   |   Related: > Availability Groups


Problem

In this tip we will cover how to add another database to an existing SQL Server Always On Availability Group.

Solution

Sometimes we need to add databases to existing SQL Server Always On Availability Group (AG) configuration according to business needs. Here I will show the steps to add a database to an existing Availability Group.

Before we get started, let me give you details about the existing configuration. I have an Availability Group with two database between two replicas. I will show how to add a database to an existing Availability Group.

NOTE: BE SURE TO TEST THIS SOLUTION IN LOWER LIFE CYCLE FIRST. DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER LIFE CYCLE ENVIRNOMENTS.

Add Database to a SQL Server Always On Configuration

Step 1: The first step is to check the existing AG configuration and its state. The AG should be healthy before proceeding to the next step. You can check dashboard report as well the Availability Group tree in SSMS. You can see AG looks healthy in the below screenshot.

Check AOAG State - Description: Check AOAG state

Step 2: Now identify your database that needs to be added to the AG. Once you have identified the database, you need to prepare it on the secondary replica as a secondary database by restoring it to a norecovery state with a copy of a full backup and a transaction log backup from the primary database. You can see I have restored this database on the secondary replica in the below screenshot and it shows the database is a restoring state.

Prepare Secondary Database - Description: Restore identified database on secondary replica

Step 3: Once you prepared the secondary database, the next step is to add this database to the AG configuration. Connect to the primary replica and expand AlwaysOn High Availability and Availability Groups in SSMS as shown below. Right click on the Availability Group name and choose Add Database... as shown in below image.

Start Adding database to AOAG - Description: Start Adding database to AOAG

Step 4: You will get the screen once you click Add Database. Click on Next button to proceed.

AOAG welcome screen - Description: AOAG welcome screen

Once you click on Next button, you will get the below screen to choose the database.

Choose identified database  - Description: Choose identified database

Check database “Add_DB_AOAG_GUI”, to add to the AG.

Select identified database in configuration window - Description: Select identified database in configuration window

Click on Next button after selecting the database and you will get the below screen to proceed. As we have already prepared the secondary database, we will choose the “Join Only” option and click Next.

Database Synchronization - Description: Database Synchronization

You will then get this screen.  Click Connect to proceed.

Connect to Secondary Replica - Description: Connect to Secondary Replica

 Clicking on the Connect above will open a window to enter credentials to connect to the secondary replica.

Connect to Secondary Replica - Description: Connect to Secondary Replica

Once you connect to the secondary replica you will get the below screen. Then click on the Next button.

Connected to Secondary Replica - Description: Connected to Secondary Replica

After clicking Next it will check validation rules for this configuration. If they all pass, click Next to proceed.

Validation Checks - Description: Validation Checks

Then you will get a summary page to verify the details as shown in the below image. This is final window of this configuration. Click on the Finish button to add the database to the AG.

AOAG Configuration Summary windows - Description: AOAG Configuration Summary windows

You will see the following that shows the database has been added to the AG.  Click Close to close this window.

Added Database to AOAG - Description: Added Database to AOAG

Step 5: Now the database has been added to the existing AG. Next, we will check and validate this change. We can run the dashboard report again or check in SQL Server Management Studio. I checked both ways and you can see the database has been added to this AG in the below image and our Availability Group is running healthy post adding this database.

Validate Database Addition to AOAG - Description: Validate Database Addition to AOAG
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, June 12, 2018 - 2:05:24 AM - Pramod Back To Top (76186)

 I would like to know if there are any steps to do the above steps by SQL script.

 


Tuesday, February 6, 2018 - 3:50:08 AM - Manvendra Back To Top (75111)

 Hi Blake

Thank you for your queson.

I have already mention in this article that we have already configured our secondary database by taking full backup and a tlog backup and restored at secondary replica.

If you havenot dont this step or you have not prepared your secondary database then you can choose Full option in place of join only. Remeber, if yo will chose first option it may take more time if you have large database because this configuration will run the backup and restore them on secondary replica to synchronize them with each other.

Hope i answered your query.


Monday, February 5, 2018 - 1:55:15 PM - Blake Back To Top (75106)

Is there a reason why you would use Join over Full for the data synchronization step?















get free sql tips
agree to terms