SQL Server Failover clustering instance(FCI) with Always On Availability Group

SQL Server Failover clustering instance(FCI) with Always On Availability Group

SQLclust FCI: Server1 and Server2

Availability Group: SQLclust, Server3

Action Plan:

1.Install SQLclust FCI with Server1 and Server2

2.Install SQL Server standalone on Server3

3.Enable AlwaysOn High Availability on the existing FCI instance on Server1 and Server2. restart the SQL Server service 

4.Add Server3 into exist cluster (WSFC)

5.Remove the Server3 as a possible owner of FCI

go to "SQL Server" services/or NetworkName on the Cluster instance under SQL Server(MSSQLSERVER) (Cluster Group/Application/Role) and go to SQL Properties. Under Advanced Policies 

remove the Server3 as a possible owner

select only Server1 and Server2 pair as a possible Owner of SQL Failover Cluster Instance

6.Enable AlwaysOn High Availability on the Server3. restart the SQL Server service 

7.Create a Availability Group with SQLclust and Server3 replica

8.Create a Always on Listener

部署 Always On 可用性群組 需要一個 Windows Server 容錯移轉叢集 (WSFC)。 若要啟用 Always On 可用性群組，SQL Server 執行個體必須位於 WSFC 節點上，且 WSFC 和節點必須在線上。 此外，所指定可用性群組之每個可用性複本都必須位在相同 WSFC 的不同節點上。 唯一的例外狀況是在移轉至另一個 WSFC 期間，可用性群組可以暫時跨兩個叢集。

您可以藉由實作在 WSFC上的 SQL Server FCI，設定在伺服器執行個體層級(server level)的第二層容錯移轉。 (FCI也必須跟AlwaysOn Replica在同一個WSFC上)

可用性複本可由 SQL Server 獨立執行個體或 FCI 執行個體所裝載。但只有一個 FCI 夥伴可以裝載給定可用性群組的複本。在 FCI 上執行可用性複本時，可用性群組(availability group)的可能擁有者清單(possible owners list)只包含使用中 FCI 節點(active FCI node)。

SQL Server 容錯移轉叢集執行個體 (FCI) 不支援依照可用性群組進行自動容錯移轉，因此任何由 FCI 裝載的可用性複本只能設定為手動容錯移轉。

以下情境在企業環境不多見，但須注意:

下面這段的描述是不可設定FCI其中一個節點若有安裝另一個SQL Server instance並成同一個AG的Replica副本，此情境將會違反 Always On 可用性群組 條件約束，造成問題。

簡單來說，當AlwaysOn包含FCI，此FCI的節點上若有另一個SQL Instance不可加入與FCI同一個AG的副本。

When choosing an FCI to host an availability replica for a given availability group, ensure that an FCI failover could not potentially cause a single WSFC node to attempt to host two availability replicas for the same availability group.

The following example scenario illustrates how this configuration could lead to problems:

Marcel configures two a WSFC with two nodes, NODE01 and NODE02. He installs a SQL Server failover cluster instance, fciInstance1, on both NODE01 and NODE02 where NODE01 is the current owner for fciInstance1.

On NODE02, Marcel installs another instance of SQL Server, Instance3, which is a stand-alone instance.

On NODE01, Marcel enables fciInstance1 for Always On availability groups. On NODE02, he enables Instance3 for Always On availability groups. Then he sets up an availability group for which fciInstance1 hosts the primary replica, and Instance3 hosts the secondary replica.

At some point fciInstance1 becomes unavailable on NODE01, and the WSFC causes a failover of fciInstance1 to NODE02. After the failover, fciInstance1 is a Always On availability groups-enabled instance running under the primary role on NODE02. However, Instance3 now resides on the same WSFC node as fciInstance1. This violates the Always On availability groups constraint.

To correct the problem that this scenario presents, the stand-alone instance, Instance3, must reside on another node in the same WSFC as NODE01 and NODE02.

搭配可用性群組使用 WSFC 容錯移轉叢集管理員的限制

不要使用容錯移轉叢集管理員操作可用性群組，例如：

• 請勿在可用性群組的叢集服務 (資源群組) 中加入或移除資源。

• 請勿變更任何可用性群組屬性，例如可能的擁有者和慣用擁有者。 可用性群組會自動設定這些屬性。

• 請勿使用容錯移轉叢集管理員來將可用性群組移到不同的節點或容錯移轉可用性群組。 容錯移轉叢集管理員不會察覺可用性複本的同步處理狀態，而且這樣做可能會造成停機時間延長。 您必須使用 Transact-SQL 或 SQL Server Management Studio。

	Nodes within an FCI	Replicas within an availability group
Uses WSFC	Yes	Yes
Protection level	Instance	Database
Storage type	Shared	Non-shared While the replicas in an availability group do not share storage, a replica that is hosted by an FCI uses a shared storage solution as required by that FCI. The storage solution is shared only by nodes within the FCI and not between replicas of the availability group.
Storage solutions	Direct attached, SAN, mount points, SMB	Depends on node type
Readable secondaries	No*	Yes
Applicable failover policy settings	WSFC quorum FCI-specific Availability group settings**	WSFC quorum Availability group settings
Failed-over resources	Server, instance, and database	Database only

Failover Clustering and Always On Availability Groups (SQL Server)

Adding SQL Server AlwaysOn Availability Groups to existing Failover Clusters