Always On 建置規劃設計考量與實作執行注意事項

Always On

建置與規劃設計考量

硬體規格

 1.原廠建議與技術支援

如果使用兩台硬體不一樣的主機來建置，技術上failover cluster通常還是可以建置成功，但微軟官方文件是有建議兩台硬體最好一樣，也就是說如果硬體不一樣

(1) 建置failover cluster過程若出現Cluster Validation失敗，還是有可能無法建置成功，原廠可能不會提供技術支援

(2) 或是建置failover cluster完成後，未來遇到問題，原廠可能不會提供技術支援

PS.建立always on之前須先建立failover cluster

Failover clustering hardware requirements and storage options

https://docs.microsoft.com/en-us/windows-server/failover-clustering/clustering-requirements

Servers: We recommend that you use a set of matching computers that contain the same or similar components.

2.效能

(1) 如果兩台主機硬體規格不一樣，既使CPU時脈與核心數、記憶體大小、磁碟規格(傳輸介面、空間大小)有接近或相同，仍然未來仍有可能遇到效能問題，屆時只能針對當下的效能問題提供修改建議，除了資料庫可以優化的部分之外，還是會有可能需要進行硬體擴充或替換或更換主機

(1) 另外，如果交易量很大且Always On資料同步使用同步模式，而第二台的硬體規格如果差很多，也會影響第一台的效能(Blocking或wait)

版本 Edition:

SQL Server 2016 and above on Standard Edition : Basic Always On availability groups

• Support for one availability database (一個Basic-AG只能有一個DB)
• You may have multiple Basic availability groups connected to a single instance of SQL Server(可以有多個Basic-AG)
• Limit of two replicas (primary and secondary)
• No read access/No backups/No integrity checks on secondary replica

Basic Always On availability groups for a single database

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15

節點數量:

同步認可模式 Synchronous-commit mode節點數量:

在給定的可用性組中，為同步認可模式(synchronous-commit mode)配置的一組 兩個或三個 可用性副本（包括當前的主要副本）

包含主要副本最多3個可以設定為同步認可模式(synchronous-commit mode)

自動容錯移轉 Automatic failover (without data loss)節點數量:

自動容錯移轉只支援3個複本（包括當前的主要副本）(SQL Server 2016開始)

自動容錯移轉只支援2個複本（包括當前的主要副本）(SQL Server 2014, SQL Server 2012)

In SQL Server 2016 both synchronous secondary replicas can be configured as automatic failover partners with the primary replica.

Benefits

This will allow following benefits

• This increases the chances that high availability can be maintained if one of the automatic failover partners is lost.
• Reduces the need to manually manage failovers or to reconfigure the availability group with another automatic failover partner in the event that one of the automatic failover partners is unavailable.

FAQs

1.If both the secondary replicas are SYNCHRONIZED and healthy, which replica with Cluster choose to failover to?

Windows Cluster will attempt to failover to the next replica in the preferred owner list of the availability group, which dictates attempted failover order. The preferred owner list of the availability group role in Cluster will list all the availability group replicas defined for automatic failover.

For example replicas SQL16N1 (primary), SQL16N2 (secondary) and SQL16N3 (secondary replica) are all defined for automatic failover and are in that order in the preferred owner list. In the event that a health issue is detected in SQL16N1, Cluster will attempt to failover to SQL16N2 next. In the event that SQL16N2 is not healthy (for example, an availability database was not SYNCHRONIZED) Cluster will attempt to failover to SQL16N3.

Here is a picture of the availability group role properties from Failover Cluster Manager, note the Up and Down buttons for setting priority of these nodes as owner of the primary replica.

2.Can we set the automatic failover priority, choosing a particular replica for a preferred failover target in SQL Server or by using Failover Cluster Manager?

There is no setting in SQL Server for configuring the preferred owner list settings of the availability group role. In addition, resetting the priority of the nodes in the preferred owner list of the availability group role in Failover Cluster Manager is also not recommended because SQL Server manages Preferred and Possible Owner Properties for AlwaysOn Availability Group/Role ... . When a failover occurs, SQL Server resets the Preferred Owner list, over-writing any change made through Failover Cluster Manager.

Furthermore, modifying AG resource properties in the cluster manager is not recommended .

Tests have shown you can change the Preferred Owner list in Failover Cluster, and the next automatic failover will abide by the modified preferred owner list. However, once SQL Server resets the preferred owner list, subsequent automatic failovers will proceed with the preferred owner priority set by SQL Server.

Workaround: Configure automatic failover priority by adding replicas to availability group in preferred automatic failover priority

SQL Server will set the preferred owner list priority based on the order in which your availability group replicas are added to the availability group. This can allow you some control over what automatic failover partner Cluster attempts to failover to first.

For example, you have defined an availability group with primary replica SQL16N1 and add SQL16N2 and SQL16N3 as automatic failover partners. If you add SQL16N2 and then SQL16N3 using the Add Replica to Availability Group wizard, SQL Server will add them in the same order to the preferred owner list:

 

When a health event is detected in SQL16N1 (in this example, I shut down SQL16N1 through SSMS Object Explorer), Cluster checks the next node in the preferred owner list which is SQL16N2. Assuming it is healthy and SYNCHRONIZED, Cluster will successfully failover to SQL16N2.

Now, try adding the availability replicas in the reverse order to the availability group, like this. After adding the replicas, check the properties of the availability group role to confirm the order of the preferred owner list:

When a health issue is detected on SQL16N1 (again shut down SQL Server on SQL16N1) we can see the preferred owner list priority is honored and Cluster attempts to failover to SQL16N3 first and successfully.

建置過程:

1.初始化資料庫

在設定過程中，初始資料同步處理步驟，選擇 [略過初始資料同步處理](Skip initial data synchronization) 或 [僅聯結](Join Only)

可以做到不中斷服務，初始化次要資料庫的備份檔之後的交易會自動接續同步到次要資料庫

SQL Server 2016 AlwaysOn Availability Group Enhancements: Multiple Automatic Failover Targets

https://techcommunity.microsoft.com/t5/sql-server-support/sql-server-2016-alwayson-availability-group-enhancements/ba-p/318782

Failover and Failover Modes (Always On Availability Groups)

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2016#SyncCommitWithAuto

Automatic failover provides high availability by ensuring that the database is quickly made available again after the loss of the primary replica. To configure an availability group for automatic failover, you need to set both the current primary replica and at least one secondary replica to synchronous-commit mode with automatic failover. You can have up to three automatic failover replicas.

Configure a flexible automatic failover policy for an Always On availability group

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-flexible-automatic-failover-policy?view=sql-server-ver15

2.AD OU權限

即使建置過程用來登入執行設定Always 0n的網域帳戶(執行者的帳號)，有加入Domain admin群組

也必須設定過程中，設定對整個AD網域或Cluster電腦帳戶所在OU有以下權限

l   Read all properties check box

l   Create Computer objects check box

設定方法:

用來設定Always On時使用的AD網域帳號(通常是未來用來管理SQL Server的網域帳號)，例如: CONTOSO\SqlAdmin

此帳號必須在設定Always on的過程中對整個AD網域有以下權限

l   Read all properties check box

l   Create Computer objects check box

 

設定方法: (以下範例AD網域名為corp，設定Always on的AD名稱帳號為CORP\Install)

To grant the permissions to the installation account, follow these step:

1. Open the Active Directory Administrative Center from Server Manager, if it's not open already.
2. Select corp (local) in the left pane.
3. In the right-hand Tasks pane, verify you see corp (local) in the drop-down, and and then select Properties underneath.

4. Select Extensions, and then select the Advanced button on the Security tab.
5. On the Advanced Security Settings for corp dialog box, select Add.
6. Select Select a principal, search for CORP\Install, and then select OK.
7. Check the boxes next to Read all properties and Create Computer Objects.

8. Select OK, and then select OK again. Close the corp properties window.