Specify Replicas Page (New Availability Group Wizard/Add Replica Wizard)
This topic describes the options of the Specify Replicas page. This page applies to the New Availability Group Wizard and the Add Replica to Availability Group Wizard of SQL Server 2016. Use the Specify Replicas page to specify and configure one or more availability replicas to add the availability group. This page contains four tabs, which are introduced in the following table. Click the name of a tab in the table to go to the corresponding section, later in this topic.
Use this tab to specify each instance of SQL Server that will host or currently hosts a secondary replica. Note that the server instance to which you are currently connected must host the primary replica.
Use this tab to verify any existing database mirroring endpoints and also, if this endpoint is lacking on a server instance whose service accounts use Windows Authentication, to create the endpoint automatically.
Use this tab to specify your backup preference for the availability group as a whole and your backup priorities for the individual availability replicas.
Use this tab, if available, to create an availability group listener. By default, a listener is not created.
- Server Instance
Displays the name of the server instance that will host the availability replica.
If a server instance that you to use to host a secondary replica is not listed by the Availability Replicas grid, click the Add Replica button. If you are configuring an availability group in a hybrid-IT environment (see High Availability and Disaster Recovery for SQL Server in Windows Azure Virtual Machines), you can click the Add Azure Replica button to create virtual machines with secondary replicas in Windows Azure.
- Initial Role
Indicates the role that the new replica will initially perform: Primary or Secondary.
- Automatic Failover (Up to 3)
Select this checkbox only if you want this availability replica to be an automatic-failover partner. To configure automatic failover, you must choose this option for the initial primary replica and for one secondary replica. Both of these replicas will use the synchronous-commit availability mode. Only three replicas can support automatic failover.
For information about the synchronous-commit availability mode, see Availability Modes (AlwaysOn Availability Groups). For information about automatic failover, see Failover and Failover Modes (AlwaysOn Availability Groups).
- Synchronous Commit (Up to 3)
If you selected Automatic Failover (Up to 3) for the replica, Synchronous Commit (Up to 3) is also selected. If the check box is blank, select it only if you want this replica to use synchronous-commit mode with only planned manual failover. Only three replicas can use synchronous-commit mode.
If you want this replica to use asynchronous-commit availability mode, leave this checkbox blank. The replica will support only forced manual failover (with possible data loss). For information about the asynchronous-commit availability mode, see Availability Modes (AlwaysOn Availability Groups). For information about planned manual failover and forced manual failover, see Failover and Failover Modes (AlwaysOn Availability Groups).
- Readable Secondary Role
Select a value from the Readable secondary drop list, as follows:
No direct connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
- Read-intent only
Only direct read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.
- Add Replica
Click to add a secondary replica to the availability group.
- Add Azure Replica
Click to create a Windows Azure virtual machine that is running a secondary replica in the availability group. This option is applicable only for an availability group in hybrid IT that contains on-premise replicas. For more information, see High Availability and Disaster Recovery for SQL Server in Windows Azure Virtual Machines.
- Remove Replica
Click to remove the selected secondary replica from the availability group.
For each server instance that will host an availability replica, the Endpoints tab displays actual values of the existing database mirroring endpoint, if any, or suggested values for a potential new endpoint that would use Windows Authentication. For both existing and potential endpoints, the Endpoint values grid displays the following information:
- Server Name
Displays the name of a server instance that will host an availability replica.
- Endpoint URL
Displays the actual or proposed URL of the database mirroring endpoint. For a proposed new endpoint, you can change this value. For information the format of these URLs, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).
- Port Number
Displays the actual or proposed port number of the endpoint. For a proposed new endpoint, you can change this value.
- Endpoint Name
Displays the actual or proposed name of the endpoint. For a proposed new endpoint, you can change this value.
- Encrypt Data
Indicates whether data sent over this endpoint is encrypted. For a proposed new endpoint, you can change this setting.
- SQL Server Service Account
Username of the SQL Server service account.
For a server instance to use an endpoint that uses Windows Authentication, its SQL Server service account must be a domain account.
This requirement determines your next configuration step, as follows:
If every server instance is running under a domain service account, that is, if the SQL Server Service Account column displays a domain service account for every server instance, click Next.
If any server instance is running under a non-domain service account, you need to do make a manual change to your server instance before you can proceed in the wizard. In this case, clicking Next brings up a warning dialog box; you should click No, which returns you to theEndpoints tab. While leaving the wizard on the Specify Replicas page, make one of the following changes to each server instance for which the SQL Server Service Account column displays a nondomain service account, either:
Use the SQL Server Configuration Manager to change the SQL Server service account to a domain account. For more information, see Change the Service Startup Account for SQL Server (SQL Server Configuration Manager).
Use Transact-SQL or PowerShell to manually create a database mirroring endpoint that uses a certificate. For more information, see CREATE ENDPOINT (Transact-SQL) or Create a Database Mirroring Endpoint for AlwaysOn Availability Groups (SQL Server PowerShell).
If you leave the Specify Availability Replicas page open while you configure endpoints, return to the Endpoints tab and click Refresh to update the Endpoint values grid.
To specify where backups should occur, choose one of the following options:
- Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.
- Secondary only
Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.
Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.
- Any Replica
Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.
There is no enforcement of the backup-preference setting. The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups).
Use the Replica backup priorities grid to specify your backup priorities for each of replicas of the availability group. This grid contains the following columns:
- Server Instance
Displays the name of the instance of SQL Server that hosts the availability replica.
- Backup Priority (Lowest=1, Highest=100)
Assign the priority for backups being performed on this replica relative to the other replicas in the same availability group. The default value is 50. You can select any other integer in the range of 0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If you set Backup Priority to 1, the availability replica will be choosen for performing backups only if no higher priority availability replica is currently available.
- Exclude Replica
To prevent this availability replica from ever being be chosen for performing backups. This is useful, for example, for a remote availability replica to which you never want backups to fail over.
Specify your preference for an availability group listener that will provide a client connection point, one of:
- Do not create an availability group listener now.
Select to skip this step. You can create a listener later. For more information, see Create or Configure an Availability Group Listener (SQL Server).
- Create an availability group listener.
Specify your listener preferences for this availability group, as follows:
- Listener DNS Name
Specify the network name of the listener. This name must be unique on the domain and can contain only alphanumeric characters, dashes (-), and hyphens (_), in any order. When specified by using the Listener tab, the DNS name can up to 15 characters long.
If you enter an invalid DNS listener name (or port number) on the Listener tab, the Next button is disabled on the Specify Replicas page.
Specify the TPC port used by this listener.
If you enter an invalid port number (or DNS listener name) on the Listener tab, the Next button is disabled on the Specify Replicas page.
- Network Mode
Use the drop list to select the network mode to be used by this listener, one of:
- Static IP
Select if you want the listener to listen on more than one subnet. To use the static IP network mode, an availability group listener must listen on every subnet that hosts an availability replica for the availability group. For each subnet, click Add to select a subnet address and to specify an IP address.
If Static IP is selected as the network mode (this is the default selection), a grid displays the Subnet and IP Address columns, and the associated Add and Remove buttons are displayed. Note that the grid is empty until you add the first subnet.
- Subnet column
Displays the subnet address that you selected for each subnet you have added for the listener.
- IP Address column
Displays the IPv4 or IPv6 address that you specified for a given subnet.
Click to add a subnet to this listener. This opens the Add IP Address dialog box. For more information, see the Add IP Address Dialog Box (SQL Server Management Studio) help topic.
Click to remove the subnet that is currently selected in the grid.
Select if you want the listener to listen on a single subnet and to use a dynamic IPv4 address that is assigned by a server running the Dynamic Host Configuration Protocol (DHCP). DHCP is limited to a single subnet that is common to every server instance that host an availability replica for the availability group.
We do not recommend DHCP in production environment. If there is a down time and the DHCP IP lease expires, extra time is required to register the new DHCP network IP address that is associated with the listener DNS name and impact the client connectivity. However, DHCP is good for setting up your development and testing environment to verify basic functions of availability groups and for integration with your applications.
When DHCP is selected, the Subnet field is displayed.
If you selected DHCP as the network mode, use the Subnet drop list to select an address for the subnet that hosts the availability replicas of the availability group.
After you define an availability group listener, we strongly recommend that you do the following: