Skip to content

take-an-availability-group-offline-sql-server.md

Latest commit

 

History

History
80 lines (52 loc) · 5.32 KB

take-an-availability-group-offline-sql-server.md

File metadata and controls

80 lines (52 loc) · 5.32 KB
title Alter availability group offline
description Steps to set your Always On availability group offline
ms.custom seo-lt-2019
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology high-availability
ms.topic conceptual
helpviewer_keywords
Availability Groups [SQL Server], take offline
ms.assetid 50f5aad8-0dff-45ef-8350-f9596d3db898
author rothja
ms.author jroth
manager craigg

Take an Availability Group Offline (SQL Server)

This topic describes how to take an AlwaysOn availability group from the ONLINE state to the OFFLINE state by using [!INCLUDEtsql] in [!INCLUDEssSQL11SP1] and later versions. There is no data loss for synchronous-commit databases because if any synchronous-commit replica is not synchronized, the OFFLINE operation raises an error and leaves the availability group ONLINE. Keeping the availability group online protects unsynchronized synchronous-commit databases from possible data loss. After an availability group goes offline, its databases become unavailable to clients and you cannot bring the availability group back online. Therefore, take an availability group offline only to migrate the availability group resources from one WSFC cluster to another.

During a cross-cluster migration of [!INCLUDEssHADR], if any applications connect directly to the primary replica of an availability group, the availability group must be taken offline. Cross-cluster migration of [!INCLUDEssHADR] supports OS upgrade with minimal downtime of availability groups. The typical scenario is to use cross-cluster migration of [!INCLUDEssHADR] for OS upgrade to [!INCLUDEwin8] or [!INCLUDEwin8srv]. For more information, see Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade.

Before You Begin

Caution

Use the OFFLINE option only for a cross-cluster migration of availability group resources.

Prerequisites

  • The server instance on which you enter the OFFLINE command must be running [!INCLUDEssSQL11SP1] or above (Enterprise edition or above).

  • The availability group must currently be online.

Recommendations

Before you take the availability group offline, delete the availability group listener or listeners. For more information, see Remove an Availability Group Listener (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.

Using Transact-SQL

To take an availability group offline

  1. Connect to a server instance that hosts an availability replica for the availability group. This replica can be the primary replica or a secondary replica.

  2. Use the ALTER AVAILABILITY GROUP statement, as follows:

    ALTER AVAILABILITY GROUP group_name OFFLINE

    where group_name is the name of the availability group.

Example

The following example takes the AccountsAG availability group offline.

ALTER AVAILABILITY GROUP AccountsAG OFFLINE;

Follow Up: After the Availability Group Goes Offline

  • Logging of OFFLINE operation: The identity of the WSFC node where the OFFLINE operation was initiated is stored in both the WSFC cluster log and the SQL ERRORLOG.

  • If you did not delete the availability group listener before taking the group offline: If you are migrating the availability group to another WSFC cluster, delete the VNN and VIP of the listener. You can delete them by using either the Failover Cluster Management console, the Remove-ClusterResource PowerShell cmdlet, or cluster.exe. Note that cluster.exe is deprecated on Windows 8.

Related Tasks

Related Content

See Also

AlwaysOn Availability Groups (SQL Server)