This topic 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.
Prerequisites and Restrictions
You must be connected to the server instance that hosts the primary replica.
Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
Use SQL Server Management Studio
In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
Expand the Always On High Availability node and the Availability Groups node.
Right-click the availability group, and select one of the following commands:
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:
In the Availability Databases pane, click the Add button. This creates and selects a blank database field.
Enter the name of a database that meets the availability-databases prerequisites.
To add another database, repeat the preceding steps. When you are done specifying databases, click OK to complete the operation.
After you use the Availability Group Properties dialog box to add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica. For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).
Use Transact-SQL
Connect to the server instance that hosts the server instance that hosts the primary replica.
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
Change directory (cd) to the server instance that hosts the primary replica.
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.
To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.
The following example shows the full process for preparing a secondary database from a database on the server instance that hosts the primary replica of an availability group, adding the database to an availability group (as a primary database), and then joining the secondary database to the availability group. First, the example backs up the database and its transaction log. Then the example restores the database and log backups to the server instances that host a secondary replica.
The example calls Add-SqlAvailabilityDatabase twice: first on the primary replica to add the database to the availability group, and then on the secondary replica to join the secondary database on that replica to the availability group. If you have more than one secondary replica, restore and join the secondary database on each of them.
In this module, you'll learn how to deploy highly available solutions by using Azure SQL. You'll also look at architectures and how they affect availability.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.