Add a Secondary Replica to an Availability Group (SQL Server)
Published: May 16, 2016
Updated: May 17, 2016
Applies To: SQL Server 2016
This topic describes how to add a secondary replica to an existing Always On availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2016.
Before you begin:
To add a replica, using:
Follow Up: After Adding a Secondary Replica
We strongly recommend that you read this section before attempting to create your first availability group.
- You must be connected to the server instance that hosts the primary replica.
For more information, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).
Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
To add a replica
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:
Select the Add Replica command to launch the Add Replica to Availability Group Wizard. For more information, see Use the Add Replica to Availability Group Wizard (SQL Server Management Studio).
Alternatively, select the Properties command to open the Availability Group Properties dialog box. The steps for adding a replica in this dialog box are as follows:
In the Availability Replicas pane of the dialog box, click the Add button. This creates and selects a replica entry in which the blank Server Instance field is selected.
Enter the name of a server instance that meets the prerequisites for hosting an availability replica.
To add an additional replicas, repeat the preceding steps. When you are done specifying replicas, click OK to complete the operation.
To add a replica
Connect to the instance of SQL Server that hosts the primary replica.
Add the new secondary replica to the availability group by using the ADD REPLICA ON clause of the ALTER AVAILABILITY GROUP statement. The ENDPOINT_URL, AVAILABILITY_MODE, and FAILOVER_MODE options are required in an ADD REPLICA ON clause. The other replica options— BACKUP_PRIORITY, SECONDARY_ROLE, PRIMARY_ROLE, and SESSION_TIMEOUT—are optional. For more information, see ALTER AVAILABILITY GROUP (Transact-SQL).
For example, the following Transact-SQL statement creates a new replica to an availability group named
MyAGon the default server instance hosted by
COMPUTER04, whose endpoint URL is
TCP://COMPUTER04.Adventure-Works.com:5022'. This replica supports manual failover and asynchronous-commit availability mode.
ALTER AVAILABILITY GROUP MyAG ADD REPLICA ON 'COMPUTER04' WITH ( ENDPOINT_URL = 'TCP://COMPUTER04.Adventure-Works.com:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL );
To add a replica
Change directory (cd) to the server instance that hosts the primary replica.
Use the New-SqlAvailabilityReplica cmdlet.
For example, the following command adds an availability replica to an existing availability group named
MyAg. This replica supports manual failover and asynchronous-commit availability mode. In the secondary role, this replica will support read access connections, allowing you to offload read-only processing to this replica.
$agPath = "SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAg" $endpointURL = "TCP://PrimaryServerName.domain.com:5022" $failoverMode = "Manual" $availabilityMode = "AsynchronousCommit" $secondaryReadMode = "AllowAllConnections" New-SqlAvailabilityReplica -Name SecondaryServer\Instance ` -EndpointUrl $endpointURL ` -FailoverMode $failoverMode ` -AvailabilityMode $availabilityMode ` -ConnectionModeInSecondaryRole $secondaryReadMode ` -Path $agPath
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.
To set up and use the SQL Server PowerShell provider
To add a replica for an existing availability group, you must perform the following steps:
Connect to the server instance that is going to host the new secondary replica.
Join the new secondary replica to the availability group. For more information, see Join a Secondary Replica to an Availability Group (SQL Server).
For each database in the availability group, create a secondary database on the server instance that is hosting the secondary replica. For more information, see Manually Prepare a Secondary Database for an Availability Group (SQL Server).
Join each of the new secondary databases to the availability group. For more information, see Join a Secondary Database to an Availability Group (SQL Server).
To manage an availability replica
ALTER AVAILABILITY GROUP (Transact-SQL)
Overview of Always On Availability Groups (SQL Server)
Creation and Configuration of Availability Groups (SQL Server)
Use the Always On Dashboard (SQL Server Management Studio)
Monitor Availability Groups (Transact-SQL)