Integrate a database into an Availability Group
In this tutorial we will learn how to integrate a database into an Availability Group. This also describes how to add a database to an Always On availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2019 (15.x).
Use SQL Server Management Studio
- Firstly, in Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
- Secondly, expand the Always On High Availability node and the Availability Groups node.
- Thirdly, right-click the availability group, and select one of the following commands:
- Now, to launch the Add Database to Availability Group Wizard, select the Add Database command.
- Here, to add one or more databases by specifying them in the Availability Group Properties dialog box, select the Properties command. The steps for adding a database are as follows:
- Firstly, in the Availability Databases pane, click the Add button. This creates and selects a blank database field.
- Secondly, enter the name of a database that meets the availability-databases prerequisites.
Further, to add another database, repeat the preceding steps. When you are done specifying databases, click OK to complete the operation.
Use Transact-SQL
- Firstly, connect to the server instance that hosts the server instance that hosts the primary replica.
- Secondly, use the ALTER AVAILABILITY GROUP statement, as follows:
- ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,…n]
- where group_name is the name of the availability group and database_name is the name of a database to be added to the group.
- The following example adds the MyDb3 database to the MyAG availability group.
— Connect to the server instance that hosts the primary replica.
— Add an existing database to the availability group.
ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDb3;
GO
- Lastly, after you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica.
Use PowerShell
- Firstly, change directory (cd) to the server instance that hosts the primary replica.
- Secondly, use the Add-SqlAvailabilityDatabase cmdlet.
- For example, the following command adds the secondary database MyDd to the MyAG availability group, whose primary replica is hosted by PrimaryServer\InstanceName.
Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\PrimaryServer\InstanceName\AvailabilityGroups\MyAG
-Database “MyDb”
- Lastly, after you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica.
Reference: Microsoft Documentation