# Mssql AlwaysOn 流程
### 零、環境基本設定
- 關閉自動更新
1.Windows Server Windows+R，輸入cmd，輸入sconfig > 按5 > 按3
2.[服務] > [Windows Update] > 改成[已停用]
- windows 更改rdp port 防火牆開port、時區、語系、硬碟 最後重啟
- 時間語言設定
[設定] > [時間與語言] > [日期和時間]
[設定] > [時間與語言] > [地區]
[設定] > [時間與語言] > [語言]
- 更改登入port
> cmd => regedit
路徑 > 電腦\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp
找到 PortNumber 後裡面進行更改
- 新增防火牆規則
- 重啟
- 安裝SQ2019
- 安裝SSMS
---
## 一、前製作業設置
### 1.1 設定 User 權限及各機完整電腦名稱
※※ 兩台 Server 都要用同一個 Windows User 名稱登入，密碼也必須相同，並且必須套用到
Administrator 權限。目的是要依此成為 WorkGroup Clusters 操作的使用者認證。※※
- 時間語言設定
[設定] > [登入選項] > [密碼]
- 設定系統名稱
進階系統設定 => 電腦名稱
### 1.2修改 hosts 文件取代 DNS 服務
手動修改【C:\Windows\System32\drivers\etc\hosts】檔案,兩台互相新增彼此的DNS資料
### 1.3修改 Windows Server 權限認證方式
在每一台成員伺服器中以 Administrator 權限執行下列 PowerShell 指令碼，讓建立容錯移轉叢集
的使用者有內建系統管理員一樣的權限
```
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\
System -Name LocalAccountTokenFilterPolicy -Value 1
```
### 1.4 防火牆設定
在每一台成員伺服器中以 Administrator 權限執行下列 PowerShell 指令碼
> netsh advfirewall firewall add rule name=“Open Port 5022 for Availability Groups” dir=in action=allow protocol=TCP localport=5022
netsh advfirewall firewall add rule name=“Open Port 1433 for SQL Server” dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name=“Open Port 53 User-Computer Authentication_DNS” dir=in action=allow protocol=TCP localport=53
netsh advfirewall firewall add rule name=“Open Port 58 User-Computer Authentication_Kerberos” dir=in action=allow protocol=TCP localport=58
netsh advfirewall firewall add rule name=“Open Port 3343 Cluster Network” dir=in action=allow protocol=TCP localport=3343
netsh advfirewall firewall add rule name=“Open Port 3343 Cluster Network” dir=in action=allow protocol=UDP localport=3343
netsh advfirewall firewall add rule name=“Open Port 135 RPC” dir=in action=allow protocol=TCP localport=135
netsh advfirewall firewall add rule name=“Open Port 137 System administrator” dir=in action=allow protocol=TCP localport=137
netsh advfirewall firewall add rule name=“Open Port 464 User-Computer Authentication_Kerberos” dir=in action=allow protocol=TCP localport=464
netsh advfirewall firewall add rule name=“Open Port 464 User-Computer Authentication_Kerberos” dir=in action=allow protocol=UDP localport=464
netsh advfirewall firewall add rule name=“Open Port 445 SMB” dir=in action=allow protocol=UDP localport=445
netsh advfirewall firewall add rule name=“Open Port 59998 probes port” dir=in action=allow protocol=TCP localport=59998
---
## 二、容錯移轉叢
### 2.0 安裝 Failover Cluster
[伺服器管理員] > [新增角色及功能] > [Failover Cluster]
### 2.1 建立容錯移轉叢集(WSFC)
首先是透過 PowerShell 來建立，叢集名稱、節點名稱隨各專案自行訂定，建議在主要副本上執行建
立叢集命令
指令碼如下：
```
new-cluster -name sqlcluster -Node sql2016-1,sql2016-2,sql2016-3 -StaticAddress
10.0.0.100 -NoStorage –VAdministrativeAccessPoint DNS
```
### 2.2 透過 Failover Cluster Manager 來建立叢集
透過 Failover Cluster Manager (容錯移轉叢集管理員)來建立容錯移轉叢集，點選 Create Cluster，瀏覽或自行輸入成員伺服器的電腦名稱後按 Add，所有伺服器都加入後按 Next。
- 容錯移轉叢集管理員
[容錯移轉叢集管理員] > [建立叢集] > [輸入所有成員的DNS NAME]
- <font color="Red"> Confirmation 確認 </font>
確認相關設定後按 Next 最後按 Finish 來建立容錯移轉叢集
(加入適合的媒體到叢集中那個勾勾選像最好不要勾選，不然有時候他會將未使用的磁碟機資源自動掛上叢集造成麻煩)
- <font color="Red"> 注意事項 </font>
注意設定容錯移轉叢集
叢集核心資源記得最後要手動設定IP 不能用DHCP 10.0.0.100
- 設定
為了方便在任何一台成員伺服器管理其他台 SQL Server 執行個體，在每一台伺服器使用下列
PowerShell 指令碼來建立防火牆規則，允許遠端伺服器透過預設的 TCP 1433 Port 來存取資料庫引擎。
> PowerShell 指令 :
>> New-NetFirewallRule -DisplayName "SQL Server Database Engine" -Direction Inbound -Program "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -RemoteAddress LocalSubnet -Action Allow
### 2.3 檢查叢集資源
檢查項目:
- 叢集虛擬節點(需要手動設定 IP 再使其上線)
- 叢集規則(其中的規則要 MSSQL 啟用 alwaysOn 時指定了 cluster 後才會出現)
- 叢集節點(電腦名稱，有幾台 server 就有幾個節點)
---
## 三、安裝 SQL Server 並設定組態
### 3.1 TCP/IP 開啟
在 SQL Server Configuration Manager 點選
[SQL Server Network Configuration] > [Protocols for MSSQLSERVER] > [TCP/IP 選項] > [TCP/IP]
啟用 SQL Server 資料庫引擎的 TCP/IP 通訊協定。
### 3.2 啟動 AlwaysOn Availability Groups 功能
- 啟動 SQL Server 資料庫引擎的 AlwaysOn Availability Groups 功能，
和 Active Directory 網域環境中一樣，可以在這個視窗中看到伺服器所在的叢集名稱
- 設定完畢後重新啟用 SQL Server 資料庫服務。
### 3.3 設定 LogOn 登入服務啟動
要注意登入者的部分要改成本機有 Administrator 權限的 Windows User 而且成員 DB 中的 User 名稱及密碼都需要一樣，本機的位置用 『.\』指定，否則在之後要建立 AG 時在通訊驗證的時候會無法
連接。
---
## 四、使用 SSMS 建立 AG 可用群組
### 4.1 新增 Windows User 至 資料庫登入 Log On 中並設定權限
將本地端 Windows User (建立叢集使用的那個 user )新增至 MSSQL 登入帳號中
並將其伺服器角色勾選設定成 sysadmin 使其有管理 AG 的權限
## 4.2 新增資料庫鏡像端點 EndPoint 並指定驗證方式
在每一台 DB Server 使用 SSMS 執行 script 新增 EndPoint 供通訊使用。
```mssql=
USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
```
端點建立後可以在伺服器物件中資料庫鏡像裡找到剛剛建立起來的端點資料
也可以執行命令確認端點種類以及端點狀態
```mssql=
select * from sys.endpoints;
select name, role_desc, state_desc FROM sys.database_mirroring_endpoints;
```
建立完後可以在 [伺服器物件] > [資料庫鏡像] > [Hard_endpoint] 看見
---
## 五、
## 5.1 新增可用性群組
- 將成員加入可用性群組內
[Always On 高可用性] > [可用性群組] > [新增可用性群組]
## 5.2 資料庫還原
- 將完整備份還原
## 5.3 可用性資料庫
- 將剛剛還原的資料庫新增進來進行同步
- 檢查slave是否同步
- <font color="Red">將master的可用性模式更改為非同步認可</font>
- <font color="Red">將slave的可用性模式更改為非同步認可</font>
- <font color="Red">將可讀取次要都改成是</font>
---
# 還原步驟與注意事項(指令與UI介面擇一)
- 備份步驟
完整備份還原 –> 差異備份還原 –> 交易記錄備份還原
- 第一次還原完整備份檔需要先將狀態設定為 "stanby"
``` mssql=
USE [master]
RESTORE DATABASE [VGame] FROM DISK = N'F:\Backup\VGame_backup_2024_01_21_040001_1834256.bak'
WITH FILE = 1,
STANDBY = N'F:\Backup\VGame_RollbackUndo_2024-01-27_17-40-42.bak',
NOUNLOAD,
REPLACE,
STATS = 5
GO
```
- 再來是還原差異備份或是交易紀錄LOG,還原時都是使用 "NORECOVERY"
```mssql=
RESTORE LOG VGame
FROM DISK=N'f:\VGame_backup_2024_01_29_200001_3317245.trn'
WITH NORECOVERY,
STATS = 10
GO
```
- 最後都還原後再執行底下指令,將資料庫 "RECOVERY"
```mssql=
RESTORE DATABASE VGame
WITH RECOVERY
GO
```
---
# 問題整理
1.如果在還原完整備份中途遇到停止回應的狀況,要將資料庫完全刪除重新還原
2.如果在還原差異備份時遇到停止回應強制關閉,要下以下指令進行停止後再重新還原
``` mssql=
RESTORE database VGame with norecovery
```
3.一定要將slave的可用性模式更改為非同步認可,這次遇到在slave大量查詢時IOPS飆高
導致master資料無法寫入
4.
5.
參考文件:https://sdwh.dev/posts/2021/07/Windows-Server-Failover-Cluster-Lab/
Sign in with Wallet
Connect another wallet