Uređivati

Podijeli putem

Enable or disable the Always On availability group feature

Applies to: SQL Server

Before you can create and configure an Always On availability group, you must enable the Always On availability groups feature on each instance of SQL Server that hosts an availability replica.

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

Permissions

When you enable availability groups on an instance of SQL Server, the server instance has full control over the WSFC cluster.

You need to be a member of the Administrator group on the local computer and have full control over the WSFC cluster. When you enable availability groups with PowerShell, open the Command Prompt window using the Run as administrator option.

You need Active Directory Create Objects and Manage Objects permissions.

Determine if the feature is enabled

You can use SQL Server Management Studio (SSMS), Transact-SQL, or PowerShell to check if the availability groups feature is enabled.

Use SQL Server Management Studio

  1. In SQL Server Management Studio (SSMS), in Object Explorer, right-click the server instance, and select Properties.

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

    • True, if availability groups are enabled
    • False, if availability groups are disabled.

Use Transact-SQL

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 availability groups, as follows:

  • IsHadrEnabled is 1, if availability groups are enabled.
  • IsHadrEnabled is 0, if availability groups are disabled.

Note

For more information about the IsHadrEnabled server property, see SERVERPROPERTY.

Use PowerShell

  1. Change directory (cd) to a server instance where you want to determine whether Always On availability groups is enabled.

  2. Enter the following PowerShell Get-Item command at the SQLSERVER:\SQL\NODE1\DEFAULT prompt:

    Get-Item . | Select-Object 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, see SQL Server PowerShell Provider.

Enable the feature

You can enable the availability groups feature using SQL Server Management Studio (SSMS) or PowerShell.

Enable with SQL Server Management Studio

  1. Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to enable availability groups.

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

  3. In SQL Server Configuration Manager, select SQL Server Services, right-click SQL Server (<instance name>). The <instance name> is the name of a local server instance for which you want to enable availability groups. Select Properties.

  4. Select the Always On High Availability tab.

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

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

    SQL Server Configuration Manager saves your change. Then, you must manually restart the SQL Server service. This step enables you to choose a restart time that best fits your business requirements. When the SQL Server service restarts, availability groups are enabled, and the IsHadrEnabled server property is set to 1.

Enable with PowerShell

  1. Change directory (cd) to a server instance where you want to enable availability groups.

  2. Use the Enable-SqlAlwaysOn cmdlet to enable 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 article.

To set up and use the SQL Server PowerShell provider, see 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 the feature

Use the following sections to disable the availability groups feature using SQL Server Configuration Manager or PowerShell. After you complete the disable operation, you can perform any required follow-up tasks.

Important

Disable the availability groups feature on only one server instance at a time. After disabling Always On availability groups, wait until the SQL Server service restarts before you proceed to another server instance.

Recommendations

Before you disable the availability groups feature on a server instance, complete the following steps:

  1. If the server instance currently hosts the primary replica of an availability group that you want to keep, 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).

Disable with SQL Server Configuration Manager

  1. Connect to the Windows Server Failover Cluster (WSFC) node that hosts the SQL Server instance where you want to disable availability groups.

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

  3. In SQL Server Configuration Manager, select SQL Server Services, right-click SQL Server (<instance name>). The <instance name> is the name of a local server instance for which you want to disable availability groups. Select Properties.

  4. On the Always On High Availability tab, clear the Enable Always On Availability Groups check box, and select OK.

    SQL Server Configuration Manager saves your change and restarts the SQL Server service. When the SQL Server service restarts, availability groups are disabled, and the IsHadrEnabled server property is set to 0, to indicate that the feature is disabled.

  5. Read the information in Follow up, later in this article.

Disable with PowerShell

  1. Change directory (cd) to a currently enabled server instance where you want to disable availability groups.

  2. Use the Disable-SqlAlwaysOn cmdlet to disable availability groups.

    For example, the following command disables availability groups on an instance of SQL Server (Computer\Instance). This command requires restarting the instance, and you're 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 article.

    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, see SQL Server PowerShell Provider.

Follow up after disabling availability groups

After you disable Always On availability groups, restart the instance of SQL Server. SQL Configuration Manager restarts the server instance automatically. However, if you use the Disable-SqlAlwaysOn cmdlet, you need to restart the server instance manually. For more information, see sqlservr Application.

On the restarted server instance:

  • Availability databases don't start up at SQL Server startup, making them inaccessible.

  • The only supported availability group Transact-SQL statement is DROP AVAILABILITY GROUP. CREATE AVAILABILITY GROUP, ALTER AVAILABILITY GROUP, and the SET HADR options of ALTER DATABASE aren't supported.

  • SQL Server metadata and Always On availability groups configuration data in WSFC aren't affected by disabling availability groups.

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

  1. If you didn't remove the local availability replicas before disabling availability groups, 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, 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 availability group setting can cause the SQL Server service to restart. The restart behavior depends on the following conditions:

-NoServiceRestart parameter specified -Force parameter specified SQL Server service restarted
No No By default. See If both parameters are specified.
No Yes Service is restarted.
Yes No Service isn't restarted.
Yes Yes Service isn't restarted.

If both parameters are specified

If you specify both -NoServiceRestart and -Force parameters, the cmdlet prompts you as follows (the default is Y):

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

If you specify N or S, the service isn't restarted.

Related content

  • Last updated on