Muokkaa

Jaa

Enable or disable Always On availability group feature

  • Artikkeli

Applies to: SQL Server

Enabling Always On availability groups is a prerequisite for a server instance to use availability groups. Before you can create and configure any availability group, the Always On availability groups feature must have been enabled on the each instance of SQL Server that will host an availability replica for one or more availability groups.

Important

If you delete and re-create a WSFC cluster, you must disable and re-enable the Always On availability groups feature on each instance of SQL Server that hosted an availability replica on the original WSFC cluster.

Prerequisites

  • Prior to SQL Server 2017, and Windows Server 2016, the instance had to reside on a Windows Server Failover Cluster (WSFC) node to enable the Always On availability group feature. To support read-scale availability groups, starting with SQL Server 2017 and Windows Server 2016, it's possible to enable the availability group feature even if the SQL Server instance does not reside on a Windows Server Failover Cluster.

  • The server instance must be running an edition of SQL Server that supports Always On availability groups. For more information, see Features Supported by the Editions of SQL Server 2016.

  • Enable Always On Availability Groups on only one server instance at a time. After enabling Always On Availability Groups, wait until the SQL Server service has restarted before you proceed to another server instance.

For information about additional prerequisites for creating and configuring availability groups, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

Permissions

While Always On Availability Groups is enabled on an instance of SQL Server, the server instance has full control on the WSFC cluster.

Requires membership in the Administrator group on the local computer and full control on the WSFC cluster. When enabling Always On by using PowerShell, open the Command Prompt window using the Run as administrator option.

Requires Active Directory Create Objects and Manage Objects permissions.

Determine if feature is enabled

Using SQL Server Management Studio

To determine whether Always On Availability Groups is enabled

  1. In Object Explorer, right-click the server instance, and click Properties.

  2. In the Server Properties dialog box, click the General page. The Is HADR Enabled property displays one of the following values:

    • True, if Always On Availability Groups is enabled

    • False, if Always On Availability Groups is disabled.

Using Transact-SQL

To determine whether Always On Availability Groups is enabled

  1. Use the following SERVERPROPERTY statement:

    SELECT SERVERPROPERTY ('IsHadrEnabled');

    The setting of the IsHadrEnabled server property indicates whether an instance of SQL Server is enabled for Always On Availability Groups, as follows:

    • If IsHadrEnabled = 1, Always On Availability Groups is enabled.

    • If IsHadrEnabled = 0, Always On Availability Groups is disabled.

    Note

    For more information about the IsHadrEnabled server property, see SERVERPROPERTY (Transact-SQL).

Using PowerShell

To determine whether Always On Availability Groups is enabled

  1. Set default (cd) to the server instance on which you want to determine whether Always On availability groups is enabled.

  2. Enter the following PowerShell Get-Item command:

    PS SQLSERVER:\SQL\NODE1\DEFAULT> get-item . | select IsHadrEnabled

    Note

    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

Enable feature

To enable Always On, using:

Using SQL Server Configuration Manager

To enable Always On Availability Groups

  1. Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to enable Always On Availability Groups.

  2. On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and click SQL Server Configuration Manager.

  3. In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to enable Always On Availability Groups, and click Properties.

  4. Select the Always On High Availability tab.

  5. Verify that Windows failover cluster name field contains the name of the local failover cluster. If this field is blank, this server instance currently does not support Always On availability groups. Either the local computer is not a cluster node, the WSFC cluster has been shut down, or this edition of SQL Server that does not support Always On availability groups.

  6. Select the Enable Always On Availability Groups check box, and click OK.

    SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This enables you to choose a restart time that is best for your business requirements. When the SQL Server service restarts, Always On will be enabled, and the IsHadrEnabled server property will be set to 1.

Using SQL Server PowerShell

To enable Always On

  1. Change directory (cd) to a server instance that you want to enable for Always On Availability Groups.

  2. Use the Enable-SqlAlwaysOn cmdlet to enable Always On Availability Groups.

    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.

    Note

    For information about how to control whether the Enable-SqlAlwaysOn cmdlet restarts the SQL Server service, see When Does a Cmdlet Restart the SQL Server Service?, later in this topic.

To set up and use the SQL Server PowerShell provider

Example: Enable-SqlAlwaysOn

The following PowerShell command enables Always On availability groups on an instance of SQL Server (Computer\Instance).

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance

Disable feature

Important

Disable Always On on only one server instance at a time. After disabling Always On Availability Groups, wait until the SQL Server service has restarted before you proceed to another server instance.

Recommendations

Before you disable Always On on a server instance, we recommend that you do the following:

  1. If the server instance is currently hosting the primary replica of an availability group that you want to keep, we recommend that you manually fail over the availability group to a synchronized secondary replica, if possible. For more information, see Perform a Planned Manual Failover of an Availability Group (SQL Server).

  2. Remove all local secondary replicas. For more information, see Remove a Secondary Replica from an Availability Group (SQL Server).

Using SQL Server Configuration Manager

To disable Always On

  1. Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to disable Always On Availability Groups.

  2. On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and click SQL Server Configuration Manager.

  3. In SQL Server Configuration Manager, click SQL Server Services, right-click SQL Server (<instance name>), where <instance name> is the name of a local server instance for which you want to disable Always On Availability Groups, and click Properties.

  4. On the Always On High Availability tab, deselect the Enable Always On Availability Groups check box, and click OK.

    SQL Server Configuration Manager saves your change and restarts the SQL Server service. When the SQL Server service restarts, Always On will be disabled, and the IsHadrEnabled server property will be set to 0, to indicate that Always On Availability Groups is disabled.

  5. We recommend that you read the information in Follow Up: After Disabling Always On, later in this topic.

Using SQL Server PowerShell

To disable Always On

  1. Change directory (cd) to a currently-enabled server instance that you want to disenable for Always On Availability Groups.

  2. Use the Disable-SqlAlwaysOn cmdlet to enable Always On Availability Groups.

    For example, the following command disables Always On Availability Groups on an instance of SQL Server (Computer\Instance). This command requires restarting the instance, and you will be prompted to confirm this restart.

    Disable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance

    Important

    For information about how to control whether the Disable-SqlAlwaysOn cmdlet restarts the SQL Server service, see When Does a Cmdlet Restart the SQL Server Service?, later in this topic.

    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

Follow Up: After Disabling Always On

After you disable Always On Availability Groups, the instance of SQL Server must be restarted. SQL Configuration Manager restarts the server instance automatically. However, if you used the Disable-SqlAlwaysOn cmdlet, you will need to restart the server instance manually. For more information, see sqlservr Application.

On the restarted server instance:

  • Availability databases do not start up at SQL Server startup, making them inaccessible.

  • The only supported Always On Transact-SQL statement is DROP AVAILABILITY GROUP. CREATE AVAILABILITY GROUP, ALTER AVAILABILITY GROUP, and the SET HADR options of ALTER DATABASE are not supported.

  • SQL Server metadata and Always On availability groups configuration data in WSFC are unaffected by disabling Always On Availability Groups.

If you permanently disable Always On Availability Groups on every server instance that hosts an availability replica for one or more availability groups, we recommend that you complete the following steps:

  1. If you did not remove the local availability replicas before disabling Always On, delete (drop) each availability group for which the server instance is hosting an availability replica. For information about deleting an availability group, see Remove an Availability Group (SQL Server).

  2. To remove the metadata left behind, delete (drop) each affected availability group on a server instance that is part of the original WSFC.

  3. Any primary databases continue to be accessible to all connections but the data synchronization between the primary and secondary databases stops.

  4. The secondary databases enter the RESTORING state. You can delete them, or you can restore them by using RESTORE WITH RECOVERY. However, restored databases are no longer participating in availability-group data synchronization.

When Does a Cmdlet Restart the SQL Server Service?

On a server instance that is currently running, using Enable-SqlAlwaysOn or Disable-SqlAlwaysOn to change the current Always On setting could cause the SQL Server service to restart. The restart behavior on depends on the following conditions:

-NoServiceRestart parameter specified -Force parameter specified Is the SQL Server service restarted?
No No By default. But the cmdlet prompts you as follows:

To complete this action, we must restart the SQL Server service for server instance '<instance_name>'. Do you want to continue?

[Y] Yes [N] No [S] Suspend [?] Help (default is "Y"):

If you specify N or S, the service is not restarted.
No Yes Service is restarted.
Yes No Service is not restarted.
Yes Yes Service is not restarted.

See Also

Overview of Always On Availability Groups (SQL Server)
SERVERPROPERTY (Transact-SQL)