mssqltips logo

How to Join a Secondary SQL Server Database to an AlwaysON Availability Group

By:   |   Updated: 2017-10-24   |   Comments   |   Related: More > Availability Groups

Problem
In the last tip, I explained how to remove Availability Group databases from an AlwaysON Availability Group (AOAG) configuration. In this tip, I will explain how to add a secondary database to an existing AOAG using both SQL Server Management Studio (SSMS) and T-SQL commands.
Solution

In this tip, I will explain how to join a secondary database to existing AOAG configuration. There are multiple reasons that you need to join a secondary database to an AOAG configuration. If your AOAG configuration failed to join the database to AOAG on the secondary replica, then you can fix the issue that was causing the database not to join the AOAG followed by adding the database to the AOAG configuration.

Availability Groups support a set of read-write primary databases and one to eight sets of corresponding secondary databases. Here we have an AOAG configuration between two replicas with three availability databases. One database is added to the AOAG, but two databases are not part of the AOAG indicated by the red and yellow warning sign on the secondary replica. Here we will add both databases to the AOAG configuration on the secondary replica in order for them to have a green status indicating a healthy state.

I am assuming you have prepared the secondary database with a full backup and a transaction log backup of the primary database and tried to add it to the AOAG. If you have not done that you can run a full backup and immediately issue a transaction log backup then restore these backups on a secondary replica with the NORECOVERY option.

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.

Join Secondary Database to AlwaysON Availability Group using SQL Server Management Studio

Step 1: Our very first step is to check and validate the existing AlwaysON Availability Group configuration by launching the SSMS dashboard report. Right click on the Availability Group name and choose "Show Dashboard" to display the AlwaysON dashboard report for this configuration.

Validate AlwaysON Dashboard Report in SQL Server Managment Studio

The dashboard report for this Availability Group will be displayed in the right pane of SSMS once you click on the "Show Dashboard" option as shown in the above screenshot. We can see two databases are displaying a warning sign on the secondary replica. The synchronization status is shown as "Not Synchronizing". Now we need to add these databases to the AOAG configuration because they haven't been added properly to this configuration.

Step 2: In SSMS, now expand the folder "AlwaysON High Availability" on the Secondary Replica followed by expanding folder "Availability Databases" under the AOAG name. You can see the status of these two databases "Add_DB_AOAG" and "Add_DB_AOAG_GUI" in the left pane of the above screenshot. We will add the "Add_DB_AOAG" database using SSMS and "Add_DB_AOAG_GUI" using T-SQL in the next part of the tip. Now right click on the database "Add_DB_AOAG" which you want to join to the existing AOAG configuration and click on "Join to Availability Group..." option as shown in the below screenshot.

Right click on the database in SSMS that needs to be added to the Availability Group

Step 3: Once you click on "Join to Availability Group..." another window named "Join Database to Availability Group 'DBAG'" will appear. You can see the name of the identified secondary database that needs to be added to the AOAG configuration.

Join Database to Availability Group in SSMS

Step 4: Check the details on this page and click the "OK" button to proceed. Once you click OK it will indicate it is processing for few seconds and then this window will disappear from the screen if this database has been joined the AOAG. The screen will not disappear if there are any issues during this process.

Adding a database to the SQL Server AOAG in SSMS

Step 5: Now launch the dashboard report again to validate the changes. We can see that the "Add_DB_AOAG" database is now showing a green icon and in a healthy state in the dashboard report and the AOAG has two databases now.

SQL Server AlwaysOn Availability Group dashboard report indicates success

Join Secondary Database to AlwaysON Availability Group using T-SQL

We have added a secondary database to the AOAG in the above section using SSMS above. Now we will add another database "Add_DB_AOAG_GUI" to the same AOAG. Sometimes you need to run T-SQL commands, so this technique is valuable to know.

Step 1: You can see the status of this database is showing a warning and the status is not synchronizing in the above dashboard screenshot.

Step 2: Now connect to the secondary replica of the AlwaysON Availability Group configuration and launch a new query window. Run the below command to join your secondary database to the existing AOAG configuration.

--Join Secondary DB to AOAG
ALTER DATABASE DBNAME SET HADR AVAILABILITY GROUP = AOAG_Name ;  
GO
join secondary database to AOAG using T-SQL

Once the command executes successfully you are done with the secondary database addition to the AOAG.

Step 3: Now we will check and validate whether this secondary database has been added to this AG or not. Launch the SSMS dashboard report of this Availability Group to check these details.

SSMS dashboard report of dbag to validate if the database was added

WWe can see all databases are part of the AOAG configuration now and show a green status. Now your AOAG configuration is healthy and databases are fully synchronized.

Next Steps


Last Updated: 2017-10-24


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools