How to Add a New Database to Always On Availability Group?

Adding or Removing databases from availability group is not day to day task but we need to perform such activities whenever it required. You can read attached article to learn how to remove database from availability group. Here, I will explain how to add database to an existing Always On availability group by using SSMS (SQL Server Management Studio) and Transact-SQL.

Before starting this exercise, make sure that primary and secondary replicas are accessible to each other and existing AOAG configuration is working fine and in healthy state. You must be connected to the server instance that hosts the primary replica to add a database to availability group.

Add Database to Availability Group using GUI in SSMS

First, we need to configure the corresponding secondary database on each server instance that hosts a secondary replica to save add database wizard processing time. You just need to run the full backup and immediate transaction log backup of the database on primary replica that has been identified to add to the availability group and then restore it to corresponding secondary replica.

Although it is not mandatory step to do it before adding the database to availability group as this can be done during adding database to availability group as well. But it is recommended to prepare your secondary databases on secondary replica before adding it to availability group. Let’s start with configuring secondary replicas followed by adding them to AOAG configuration.

  1. First run the full backup and immediate transaction log backup of the database that has been identified to add to the availability group on primary replica.
  2. Once backups are completed on primary replica for to-be added database, copy these backups to your secondary replica and restore it with same name in no recovery mode if you want to keep your secondary database inaccessible.
  3. Once database will be restored/ synced on secondary replicas, we will start adding this database to existing AOAG configuration using Add Database to Availability Group Wizard on primary replica.
  4. Launch SQL Server Management Studio. Connect to the server instance that hosts the primary replica, and expand the server tree.
  5. Expand the Always On High Availability folder followed by Availability Groups.
  6. Right-click the availability group name, and select one of the following commands:
    • To launch the Add Database to Availability Group Wizard, select the Add Database.
    • To add one or more databases by specifying them in the Availability Group Properties dialog box, select the Properties.Add Database
  7. Once you click on Add Database… option to add database to existing AOAG configuration, an Introduction wizard will appear that will very similar to the one you might have seen during creating AOAG on your SQL Server Instance.
  8. Next screen will come to select databases to proceed with adding them in to AOAG configuration.
  9. Third window will come to select initial data synchronization option. Here you should be careful as if you have already configured secondary databases as we did in step 1 and step 2 then you need to select second option that is “Join Only”. If you haven’t configured your secondary database you can go with either first option that will backup the primary database and restore it to secondary replica during this processing only or third option in which you can configure secondary database later. If your database is big in size it will take lot much time to complete the processing.join only option in add database wizard
  10. Follow rest of all windows by clicking at next button and once you will click at finish button on last window, your database will be added to existing availability group. You can see the status of this newly added database in to dashboard report or in SSMS.

Add a Database to Availability Group using T-SQL

As I have described n above section, first thing is to configure secondary database so we need to configure the corresponding secondary database on each server instance that hosts a secondary replica to save add database processing time. You just need to run the full backup and immediate transaction log backup of the database on primary replica that has been identified to add to the availability group and then restore it to corresponding secondary replica. Once restore will be completed then follow below steps to add this database to availability group.

    1. Connect to the server instance that hosts the server instance that hosts the primary replica.
    2. Use the ALTER AVAILABILITY GROUP statement, as follows:
--AG_name is the name of the availability group.
--database_name is the name of a database to be added to the group.
ALTER AVAILABILITY GROUP AG_name ADD DATABASE database_name

The following example adds the Techyaz database to the AG_Techyaz availability group.

-- Connect to the server instance that hosts the primary replica.
-- Add an existing database to the availability group.
ALTER AVAILABILITY GROUP AG_Techyaz ADD DATABASE Techyaz;
GO
  1. If you haven’t configured your secondary database then you need to configure it on each server instance that hosts a secondary replica after adding database to an availability group. If you already added it then you are done with adding your database to existing AOAG configuration. Validate it by launching dashboard report or looking in to SSMS.

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

Ganesh Bekkinkeri

I am working as SQL Server DBA in one of the biggest IT company in the world. I have very good knowledge on SQL Server database administration. Please contact me at info@techyaz.com if you have any question.
Summary
How to Add Database to existing Availability Group?
Article Name
How to Add Database to existing Availability Group?
Description
Adding or Removing databases from availability group is not day to day task but we need to perform such activities whenever required. You can read attached article to learn how to remove database from availability group. Here, I will explain how to add database to an existing Always On availability group by using SSMS (SQL Server Management Studio) and Transact-SQL.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *