ALTER AVAILABILITY GROUP (Transact-SQL)

  • Article

Alters an existing AlwaysOn availability group in SQL Server 2012. Most ALTER AVAILABILITY GROUP arguments are supported only the current primary replica. However the JOIN, FAILOVER, and FORCE_FAILOVER_ALLOW_DATA_LOSS arguments are supported only on secondary replicas.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER AVAILABILITY GROUP group_name 
  {
     SET ( <set_option_spec> ) 
   | ADD DATABASE database_name 
   | REMOVE DATABASE database_name
   | ADD REPLICA ON <add_replica_spec> 
   | MODIFY REPLICA ON <modify_replica_spec>
   | REMOVE REPLICA ON <server_instance>
   | JOIN
   | FAILOVER
   | FORCE_FAILOVER_ALLOW_DATA_LOSS   | ADD LISTENER ‘dns_name’ ( <add_listener_option> )
   | MODIFY LISTENER ‘dns_name’ ( <modify_listener_option> )
   | RESTART LISTENER ‘dns_name’
   | REMOVE LISTENER ‘dns_name’
   | OFFLINE
  }
[ ; ]

<set_option_spec> ::= 
    AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }
  | FAILURE_CONDITION_LEVEL  = { 1 | 2 | 3 | 4 | 5 } 
  | HEALTH_CHECK_TIMEOUT = milliseconds 

<server_instance> ::= 
 { 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }

<add_replica_spec>::=
  <server_instance> WITH
    (
       ENDPOINT_URL = 'TCP://system-address:port',
       AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT },
       FAILOVER_MODE = { AUTOMATIC | MANUAL }
       [ , <add_replica_option> [ ,...n ] ]
    ) 

  <add_replica_option>::=
       BACKUP_PRIORITY = n
     | SECONDARY_ROLE ( { 
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } 
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port' 
          } )
     | PRIMARY_ROLE ( { 
          ALLOW_CONNECTIONS = { READ_WRITE | ALL } 
        | READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE } 
          } )
     | SESSION_TIMEOUT = seconds 


<modify_replica_spec>::=
  <server_instance> WITH
    (  
       ENDPOINT_URL = 'TCP://system-address:port' 
     | AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT } 
     | FAILOVER_MODE = { AUTOMATIC | MANUAL } 
     | BACKUP_PRIORITY = n
     | SECONDARY_ROLE ( { 
          ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } 
        | READ_ONLY_ROUTING_URL = 'TCP://system-address:port' 
          } )
     | PRIMARY_ROLE ( { 
          ALLOW_CONNECTIONS = { READ_WRITE | ALL } 
        | READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE } 
          } )
     | SESSION_TIMEOUT = seconds 
    )  


<add_listener_option> ::=
   {
      WITH DHCP [ ON ( <network_subnet_option> ) ]
    | WITH IP ( { ( <ip_address_option> ) } [ , ...n ] ) [ , PORT = listener_port ]
   }

  <network_subnet_option> ::=
     ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’  

  <ip_address_option> ::=
     { 
        ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’
      | ‘ipv6_address’
     }

<modify_listener_option>::=
    {
       ADD IP ( <ip_address_option> ) 
     | PORT = listener_port
    }

Arguments

  • group_name
    Specifies the name of the new availability group. group_name must be a valid SQL Server identifier, and it must be unique across all availability groups in the WSFC cluster.

  • AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE }
    Specifies a preference about how a backup job should evaluate the primary replica when choosing where to perform backups. You can script a given backup job to take the automated backup preference into account. It is important to understand that the preference is not enforced by SQL Server, so it has no impact on ad-hoc backups.

    Supported only on the primary replica.

    The values are as follows:

    • PRIMARY
      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.

      Important

      If you plan to use log shipping to prepare any secondary databases for an availability group, set the automated backup preference to Primary until all the secondary databases have been prepared and joined to the availability group.

    • 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.

    • 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 behavior.

    • NONE
      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.

    Important

    There is no enforcement of the AUTOMATED_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. The automated backup preference setting has no impact on ad-hoc backups. For more information, see Configure Backup on Availability Replicas (SQL Server).

    Note

    To view the automated backup preference of an existing availability group, select the automated_backup_preference or automated_backup_preference_desc column of the sys.availability_groups catalog view. Additionally, sys.fn_hadr_backup_is_preferred_replica (Transact-SQL) can be used to determine the preferred backup replica. This function will always return 1 for at least one of the replicas, even when AUTOMATED_BACKUP_PREFERENCE = NONE.

  • FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 }
    Specifies what failure conditions will trigger an automatic failover for this availability group. FAILURE_CONDITION_LEVEL is set at the group level but is relevant only on availability replicas that are configured for synchronous-commit availability mode (AVAILIBILITY_MODE = SYNCHRONOUS_COMMIT). Furthermore, failure conditions can trigger an automatic failover only if both the primary and secondary replicas are configured for automatic failover mode (FAILOVER_MODE = AUTOMATIC) and the secondary replica is currently synchronized with the primary replica.

    Supported only on the primary replica.

    The failure-condition levels (1–5) range from the least restrictive, level 1, to the most restrictive, level 5. A given condition level encompasses all of the less restrictive levels. Thus, the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4 includes levels 1-3, and so forth. The following table describes the failure-condition that corresponds to each level.

    Level

    Failure Condition

    1

    Specifies that an automatic failover should be initiated when any of the following occurs:

    • The SQL Server service is down.

    • The lease of the availability group for connecting to the WSFC cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server AlwaysOn Lease Timeout.

    2

    Specifies that an automatic failover should be initiated when any of the following occurs:

    • The instance of SQL Server does not connect to cluster, and the user-specified HEALTH_CHECK_TIMEOUT threshold of the availability group is exceeded.

    • The availability replica is in failed state.

    3

    Specifies that an automatic failover should be initiated on critical SQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too much dumping.

    This is the default behavior.

    4

    Specifies that an automatic failover should be initiated on moderate SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server internal resource pool.

    5

    Specifies that an automatic failover should be initiated on any qualified failure conditions, including:

    • Exhaustion of SQL Engine worker-threads.

    • Detection of an unsolvable deadlock.

    Note

    Lack of response by an instance of SQL Server to client requests is not relevant to availability groups.

    The FAILURE_CONDITION_LEVEL and HEALTH_CHECK_TIMEOUT values, define a flexible failover policy for a given group. This flexible failover policy provides you with granular control over what conditions must cause an automatic failover. For more information, see Flexible Failover Policy for Automatic Failover of an Availability Group (SQL Server).

  • HEALTH_CHECK_TIMEOUT = milliseconds
    Specifies the wait time (in milliseconds) for the sp_server_diagnostics system stored procedure to return server-health information before WSFC cluster assumes that the server instance is slow or hung. HEALTH_CHECK_TIMEOUT is set at the group level but is relevant only on availability replicas that are configured for synchronous-commit availability mode with automatic failover (AVAILIBILITY_MODE = SYNCHRONOUS_COMMIT). Furthermore, a health-check timeout can trigger an automatic failover only if both the primary and secondary replicas are configured for automatic failover mode (FAILOVER_MODE = AUTOMATIC) and the secondary replica is currently synchronized with the primary replica.

    The default HEALTH_CHECK_TIMEOUT value is 30000 milliseconds (30 seconds). The minimum value is 15000 milliseconds (15 seconds), and the maximum value is 4294967295 milliseconds.

    Supported only on the primary replica.

    Important

    sp_server_diagnostics does not perform health checks at the database level.

  • ADD DATABASE database_name
    Specifies a list of one or more user databases that you want to add to the availability group. These databases must reside on the instance of SQL Server that hosts the current primary replica. You can specify multiple databases for an availability group, but each database can belong to only one availability group. For information about the type of databases that an availability group can support, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server). To find out which local databases already belong to an availability group, see the replica_id column in the sys.databases catalog view.

    Supported only on the primary replica.

    Note

    After you have created the availability group, you will need connect to each server instance that hosts a secondary replica and then prepare each secondary database and join it to the availability group. For more information, see Start Data Movement on an AlwaysOn Secondary Database (SQL Server).

  • REMOVE DATABASE database_name
    Removes the specified primary database and the corresponding secondary databases from the availability group. Supported only on the primary replica.

    For information about the recommended follow up after removing an availability database from an availability group, see Remove a Primary Database from an Availability Group (SQL Server).

  • ADD REPLICA ON
    Specifies from one to four SQL server instances to host secondary replicas in an availability group. Each replica is specified by its server instance address followed by a WITH (…) clause.

    Supported only on the primary replica.

    You need to join every new secondary replica to the availability group. For more information, see the description of the JOIN option, later in this section.

  • <server_instance>
    Specifies the address of the instance of SQL Server that is the host for an replica. The address format depends on whether the instance is the default instance or a named instance and whether it is a standalone instance or a failover cluster instance (FCI). The syntax is as follows:

    { 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }

    The components of this address are as follows:

    • system_name
      Is the NetBIOS name of the computer system on which the target instance of SQL Server resides. This computer must be a WSFC node.

    • FCI_network_name
      Is the network name that is used to access a SQL Server failover cluster. Use this if the server instance participates as a SQL Server failover partner. Executing SELECT @@SERVERNAME on an FCI server instance returns its entire 'FCI_network_name[\instance_name]' string (which is the full replica name).

    • instance_name
      Is the name of an instance of a SQL Server that is hosted by system_name or FCI_network_name and that has AlwaysOn enabled. For a default server instance, instance_name is optional. The instance name is case insensitive. On a stand-alone server instance, this value name is the same as the value returned by executing SELECT @@SERVERNAME.

    • \
      Is a separator used only when specifying instance_name, in order to separate it from system_name or FCI_network_name.

    For information about the prerequisites for WSFC nodes and server instances, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

  • ENDPOINT_URL ='TCP://system-address:port'
    Specifies the URL path for the database mirroring endpoint on the instance of SQL Server that will host the availability replica that you are adding or modifying.

    ENDPOINT_URL is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. For more information, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server).

  • 'TCP://system-address:port'
    Specifies a URL for specifying an endpoint URL or read-only routing URL. The URL parameters are as follows:

    • system-address
      Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

    • port
      Is a port number that is associated with the mirroring endpoint of the server instance (for the ENDPOINT_URL option) or the port number used by the Database Engine of the server instance (for the READ_ONLY_ROUTING_URL option).

  • AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
    Specifies whether the primary replica has to wait for the secondary replica to acknowledge the hardening (writing) of the log records to disk before the primary replica can commit the transaction on a given primary database. The transactions on different databases on the same primary replica can commit independently.

    • SYNCHRONOUS_COMMIT
      Specifies that the primary replica will wait to commit transactions until they have been hardened on this secondary replica (synchronous-commit mode). You can specify SYNCHRONOUS_COMMIT for up to three replicas, including the primary replica.

    • ASYNCHRONOUS_COMMIT
      Specifies that the primary replica commits transactions without waiting for this secondary replica to harden the log (synchronous-commit availability mode). You can specify ASYNCHRONOUS_COMMIT for up to five availability replicas, including the primary replica.

    AVAILABILITY_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. For more information, see Availability Modes (AlwaysOn Availability Groups).

  • FAILOVER_MODE = { AUTOMATIC | MANUAL }
    Specifies the failover mode of the availability replica that you are defining.

    • AUTOMATIC
      Enables automatic failover. AUTOMATIC is supported only if you also specify AVAILABILITY_MODE = SYNCHRONOUS_COMMIT. You can specify AUTOMATIC for two availability replicas, including the primary replica.

      Note

      SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can only be configured for manual failover.

    • MANUAL
      Enables manual failover or forced manual failover (forced failover) by the database administrator.

    FAILOVER_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY REPLICA ON clause. Two types of manual failover exist, manual failover without data loss and forced failover (with possible data loss), which are supported under different conditions. For more information, see Failover and Failover Modes (AlwaysOn Availability Groups).

  • BACKUP_PRIORITY **=**n
    Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. These values have the following meanings:

    • 1..100 indicates that the availability replica could be chosen for performing backups. 1 indicates the lowest priority, and 100 indicates the highest priority. If BACKUP_PRIORITY = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.

    • 0 indicates that this availability replica will never be chosen for performing backups. This is useful, for example, for a remote availability replica to which you never want backups to fail over.

    For more information, see Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups).

  • SECONDARY_ROLE ( … )
    Specifies role-specific settings that will take effect if this availability replica currently owns the secondary role (that is, whenever it is a secondary replica). Within the parentheses, specify either or both secondary-role options. If you specify both, use a comma-separated list.

    The secondary role options are as follows:

    • ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
      Specifies whether the databases of a given availability replica that is performing the secondary role (that is, is acting as a secondary replica) can accept connections from clients, one of:

      • NO
        No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default behavior.

      • READ_ONLY
        Only connections are allowed to the databases in the secondary replica where the Application Intent property is set to ReadOnly. For more information about this property, see Using Connection String Keywords with SQL Server Native Client.

      • ALL
        All connections are allowed to the databases in the secondary replica for read-only access.

      For more information, see Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups).

    • READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
      Specifies the URL to be used for routing read-intent connection requests to this availability replica. This is the URL on which the SQL Server Database Engine listens. Typically, the default instance of the SQL Server Database Engine listens on TCP port 1433.

      For a named instance, you can obtain the port number by querying the port and type_desc columns of the sys.dm_tcp_listener_states dynamic management view. The server instance uses the Transact-SQL listener (type_desc = 'TSQL').

      For more information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for AlwaysOn.

      Note

      For a named instance of SQL Server, the Transact-SQL listener should be configured to use a specific port. For more information, see Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).

  • PRIMARY_ROLE ( … )
    Specifies role-specific settings that will take effect if this availability replica currently owns the primary role (that is, whenever it is the primary replica). Within the parentheses, specify either or both primary-role options. If you specify both, use a comma-separated list.

    The primary role options are as follows:

    • ALLOW_CONNECTIONS = { READ_WRITE | ALL }
      Specifies the type of connection that the databases of a given availability replica that is performing the primary role (that is, is acting as a primary replica) can accept from clients, one of:

      • READ_WRITE
        Connections where the Application Intent connection property is set to ReadOnly are disallowed. When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

      • ALL
        All connections are allowed to the databases in the primary replica. This is the default behavior.

    • READ_ONLY_ROUTING_LIST = { (‘<server_instance>’ [ ,...n ] ) | NONE }
      Specifies a comma-separated list of server instances that host availability replicas for this availability group that meet the following requirements when running under the secondary role:

      • Be configured to allow all connections or read-only connections (see the ALLOW_CONNECTIONS argument of the SECONDARY_ROLE option, above).

      • Have their read-only routing URL defined (see the READ_ONLY_ROUTING_URL argument of the SECONDARY_ROLE option, above).

      The READ_ONLY_ROUTING_LIST values are as follows:

      • <server_instance>
        Specifies the address of the instance of SQL Server that is the host for an availability replica that is a readable secondary replica when running under the secondary role.

        Use a comma-separated list to specify all of the server instances that might host a readable secondary replica. Read-only routing will follow the order in which server instances are specified in the list. If you include a replica's host server instance on the replica's read-only routing list, placing this server instance at the end of the list is typically a good practice, so that read-intent connections go to a secondary replica, if one is available.

      • NONE
        Specifies that when this availability replica is the primary replica, read-only routing will not be supported. This is the default behavior. When used with MODIFY REPLICA ON, this value disables an existing list, if any.

  • SESSION_TIMEOUT **=**seconds
    Specifies the session-timeout period in seconds. If you do not specify this option, by default, the time period is 10 seconds. The minimum value is 5 seconds.

    Important

    We recommend that you keep the time-out period at 10 seconds or greater.

    For more information about the session-timeout period, see Overview of AlwaysOn Availability Groups (SQL Server).

  • MODIFY REPLICA ON
    Modifies any of the replicas of the availability group. The list of replicas to be modified contains the server instance address and a WITH (…) clause for each replica.

    Supported only on the primary replica.

  • REMOVE REPLICA ON
    Removes the specified secondary replica from the availability group. The current primary replica cannot be removed from an availability group. On being removed, the replica stops receiving data. Its secondary databases are removed from the availability group and enter the RESTORING state.

    Supported only on the primary replica.

    Note

    If you remove a replica while it is unavailable or failed, when it comes back online it will discover that it no longer belongs the availability group.

  • JOIN
    Causes the local server instance to host a secondary replica in the specified availability group.

    Supported only on a secondary replica that has not yet been joined to the availability group.

    For more information, see Join a Secondary Replica to an Availability Group (SQL Server).

  • FAILOVER
    Initiates a manual failover of the availability group without data loss to the secondary replica to which you are connected. The replica on which you enter a failover target failover command is known as the . The failover target will take over the primary role and recover its copy of each database and bring them online as the new primary databases. The former primary replica concurrently transitions to the secondary role, and its databases become secondary databases and are immediately suspended. Potentially, these roles can be switched back and forth by a series of failures.

    Supported only on a synchronous-commit secondary replica that is currently synchronized with the primary replica. Note that for a secondary replica to be synchronized the primary replica must also be running in synchronous-commit mode.

    Note

    A failover command returns as soon as the failover target has accepted the command. However, database recovery occurs asynchronously after the availability group has finished failing over.

    For information about the limitations, prerequisites and recommendations for a performing a planned manual failover, see Perform a Planned Manual Failover of an Availability Group (SQL Server).

  • FORCE_FAILOVER_ALLOW_DATA_LOSS

    Warning

    Forcing failover, which might involve some data loss, is strictly a disaster recovery method. Therefore, We strongly recommend that you force failover only if the primary replica is no longer running, you are willing to risk losing data, and you must restore service to the availability group immediately.

    Supported only on a replica whose role is in the SECONDARY or RESOLVING state. --The replica on which you enter a failover command is known as the failover target.

    Forces failover of the availability group, with possible data loss, to the failover target. The failover target will take over the primary role and recover its copy of each database and bring them online as the new primary databases. On any remaining secondary replicas, every secondary database is suspended until manually resumed. When the former primary replica becomes available, it will switch to the secondary role, and its databases will become suspended secondary databases.

    Note

    A failover command returns as soon as the failover target has accepted the command. However, database recovery occurs asynchronously after the availability group has finished failing over.

    For information about the limitations, prerequisites and recommendations for forcing failover and the effect of a forced failover on the former primary databases in the availability group, see Perform a Forced Manual Failover of an Availability Group (SQL Server).

  • ADD LISTENER ‘dns_name’( <add_listener_option> )
    Defines a new availability group listener for this availability group. Supported only on the primary replica.

    Important

    Before you create your first listener, we strongly recommend that you read Create or Configure an Availability Group Listener (SQL Server).

    After you create a listener for a given availability group, we strongly recommend that you do the following:

    • Ask your network administrator to reserve the listener's IP address for its exclusive use.

    • Give the listener's DNS host name to application developers to use in connection strings when requesting client connections to this availability group.

  • dns_name
    Specifies the DNS host name of the availability group listener. The DNS name of the listener must be unique in the domain and in NetBIOS.

    dns_name is a string value. This name can contain only alphanumeric characters, dashes (-), and hyphens (_), in any order. DNS host names are case insensitive. The maximum length is 63 characters.

    We recommend that you specify a meaningful string. For example, for an availability group named AG1, a meaningful DNS host name would be ag1-listener.

    Important

    NetBIOS recognizes only the first 15 chars in the dns_name. If you have two WSFC clusters that are controlled by the same Active Directory and you try to create availability group listeners in both of clusters using names with more than 15 characters and an identical 15 character prefix, you will get an error reporting that the Virtual Network Name resource could not be brought online. For information about prefix naming rules for DNS names, see Assigning Domain Names.

  • <add_listener_option>
    ADD LISTENER takes one of the following options:

    • WITH DHCP [ ON { (‘four_part_ipv4_address’,‘four_part_ipv4_mask’) } ]
      Specifies that the availability group listener will use the Dynamic Host Configuration Protocol (DHCP). Optionally, use the ON clause to identify the network on which this listener will be created. DHCP is limited to a single subnet that is used for every server instances that hosts an availability replica in the availability group.

      Important

      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.

      For example:

      WITH DHCP ON ('10.120.19.0','255.255.254.0')

    • WITH IP ( { (‘four_part_ipv4_address’,‘four_part_ipv4_mask’) | (‘ipv6_address’) } [ , ...n ] ) [ , PORT **=**listener_port ]
      Specifies that, instead of using DHCP, the availability group listener will use one or more static IP addresses. To create an availability group across multiple subnets, each subnet requires one static IP address in the listener configuration. For a given subnet, the static IP address can be either an IPv4 address or an IPv6 address. Contact your network administrator to get a static IP address for each subnet that will host an availability replica for the new availability group.

      For example:

      WITH IP ( ('10.120.19.155','255.255.254.0') )

  • four_part_ipv4_address
    Specifies an IPv4 four-part address for an availability group listener. For example, 10.120.19.155.

  • four_part_ipv4_mask
    Specifies an IPv4 four-part mask for an availability group listener. For example, 255.255.254.0.

  • ipv6_address
    Specifies an IPv6 address for an availability group listener. For example, 2001::4898:23:1002:20f:1fff:feff:b3a3.

  • PORT = listener_port
    Specifies the port number—listener_port—to be used by an availability group listener that is specified by a WITH IP clause. PORT is optional.

    The default port number, 1433, is supported. However, if you have security concerns, we recommend using a different port number.

    For example: WITH IP ( ('2001::4898:23:1002:20f:1fff:feff:b3a3') ) , PORT = 7777

  • MODIFY LISTENER ‘dns_name’  ( <modify_listener_option> )
    Modifies an existing availability group listener for this availability group. Supported only on the primary replica.

  • <modify_listener_option>
    MODIFY LISTENER takes one of the following options:

    • ADD IP { (‘four_part_ipv4_address’,  ‘four_part_ipv4_mask’) | (‘dns_nameipv6_address’) }
      Adds the specified IP address to the availability group listener specified by dns_name.

    • PORT = listener_port
      See the description of this argument earlier in this section.

  • RESTART LISTENER ‘dns_name’
    Restarts the listener that is associated with the specified DNS name. Supported only on the primary replica.

  • REMOVE LISTENER ‘dns_name’
    Removes the listener that is associated with the specified DNS name. Supported only on the primary replica.

  • OFFLINE
    Takes an online availability group offline. There is no data loss for synchronous-commit databases.

    After an availability group goes offline, its databases become unavailable to clients, and you cannot bring the availability group back online. Therefore, use the OFFLINE option only during a cross-cluster migration of AlwaysOn Availability Groups, when migrating availability group resources to a new WSFC cluster.

    For more information, see Take an Availability Group Offline (SQL Server).

Arrow icon used with Back to Top link[Top]

Prerequisites and Restrictions

For information about prerequisites and restrictions on availability replicas and on their host server instances and computers, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

For information about restrictions on the AVAILABILITY GROUP Transact-SQL statements, see Overview of Transact-SQL Statements for AlwaysOn Availability Groups (SQL Server).

Security

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Examples

A. Joining a secondary replica to an availability group

The following example joins a secondary replica to which you are connected to the AccountsAG availability group.

ALTER AVAILABILITY GROUP AccountsAG JOIN;
GO

B. Forcing failover of an availability group

The following example forces the AccountsAG availability group to fail over to the secondary replica to which you are connected.

ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

See Also

Reference

CREATE AVAILABILITY GROUP (Transact-SQL)

ALTER DATABASE SET HADR (Transact-SQL)

DROP AVAILABILITY GROUP (Transact-SQL)

sys.availability_replicas (Transact-SQL)

sys.availability_groups (Transact-SQL)

Concepts

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server)