前言

SQL Server一種高可用性和災備性解決方案，AlwaysOn 可用性組允許在多個資料庫副本之間同步資料，并提供了故障轉移和自動故障恢複功能，以確定資料庫的持續可用性和資料保護。

一、前期調研工作

1.1 資料庫版本及元件

版本查詢

DECLARE @Version NVARCHAR(128) 
SET @Version = 
    CONVERT(NVARCHAR(128),SERVERPROPERTY ('ProductVersion'))
SELECT
  CASE
     WHEN @Version like '11%'   THEN 'SQL SERVER 2012'
     WHEN @Version like '12%'   THEN 'SQL SERVER 2014'
     WHEN @Version like '13%'   THEN 'SQL SERVER 2016'    
     ELSE 'Unknown'
  END AS 'Sql Server Version Name',
  SERVERPROPERTY('ProductVersion') AS ProductVersion,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductLevel') AS ProductLevel

元件

1.2 SQLSERVER的啟動賬号及密碼

二、過程

2.1配置靜态IP位址

配置SQL SERVER2012伺服器，配置靜态IP位址。

2.2 關閉防火牆和自動更新功能

關閉防火牆和自動更新功能

2.3 安裝net3.5和故障轉移叢集功能

安裝.NET Framework 3.5功能和故障轉移叢集功能。

2.4 加域并配置DNS

将計算機加入域，将與使用者添加進本地管理者組，并配置DNS位址。

2.5 安裝SQL SERVER

獨立安裝SQL SERVER，将與使用者設定為SQL SERVER執行個體和SQL SERVER代理的啟動使用者，重新開機兩項服務。

将新節點添加進故障轉移叢集中，注意，若該節點不在故障轉移叢集中，則無法啟用ALWAYSON高可用性。

2.6 新節點加入叢集

打開故障轉移叢集管理器，連接配接到現有叢集後，右擊節點，選擇添加節點。

此時進入添加節點向導，進入驗證階段，點選下一步。

點選浏覽，進入選擇計算機頁面，此時位置為整個域。點選進階-立即查找，選擇需要添加的計算機，點選确定，伺服器添加成功。

一直點選下一步，進入驗證階段。

驗證完成即進入添加節點向導階段。

點選下一步

2.7.啟用ALWAYSON高可用性

添加叢集節點成功後，此時可以打開配置管理器，啟用ALWAYSON高可用性。啟用後重新開機SQL SERVER 執行個體。

2.8 資料庫的進行備份和還原

先測試在WIN-P4節點用域帳号能否登入到其他節點。測試成功後，可以進行完備-日志備-完整還原-日志還原。

1、全庫備份

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = '\\172.18.248.98\sharebackup\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '.BAK'  
   BACKUP DATABASE @name TO DISK = @fileName  WITH STATS=10, COMPRESSION
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

2、日志備份

DECLARE @name NVARCHAR(256) -- database name  
DECLARE @path NVARCHAR(512) -- path for backup files  
DECLARE @fileName NVARCHAR(512) -- filename for backup  
DECLARE @fileDate NVARCHAR(40) -- used for file name
 
-- specify database backup directory
SET @path = '\\172.18.248.98\sharebackup\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR READ_ONLY FOR  
SELECT name 
FROM master.sys.databases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @fileName = @path + @name + '.TRN'  
   BACKUP LOG @name TO DISK = @fileName  WITH STATS=10, COMPRESSION
 
   FETCH NEXT FROM db_cursor INTO @name   
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor

3、恢複norecovery

恢複全庫

RESTORE DATABASE xuejinnewpro FROM DISK = 'E:\AlwaysonBackup\xuejinnewpro.BAK'
WITH NORECOVERY, STATS = 5
GO
RESTORE DATABASE XUEJINPRO FROM DISK = 'E:\AlwaysonBackup\XUEJINPRO.BAK'
WITH NORECOVERY, STATS = 5
GO

日志恢複

RESTORE log xuejinnewpro FROM DISK = 'E:\logbackup\xuejinnewpro.TRN'
WITH NORECOVERY, STATS = 5
GO

RESTORE log XUEJINPRO FROM DISK = 'E:\logbackup\XUEJINPRO.TRN'
WITH NORECOVERY, STATS = 5
GO

2.9.添加副本

在主副本上選擇可用性組-DB01，選擇添加副本，此時跳出添加副本向導界面。在輔助副本中是沒有添加副本這個選項的.

點選下一步,連接配接原有的輔助副本.

點選下一步,添加副本.

點選下一步,選擇僅連接配接.點選下一步進行建立

點選下一步,直至可用性組建立成功.

注意:若隻對一個資料庫進行了還原操作，則在添加副本時會提示副本建立失敗，原因是為還原的資料庫無法連接配接。退出後會發現其實已經添加副本成功，做過還原操作的資料庫也已同步成功。而失敗的資料庫雖然同步失敗，但是會出現在可行性資料庫清單中。此時隻需要對該資料庫進行完備-日志備-完整還原-日志還原後，在可用性資料庫清單下選擇該資料庫，右鍵選擇連接配接，該資料庫即可成為可用性資料庫中的一部分并正常同步。

2.10 驗證主備庫是否同步

SELECT DISTINCT ar.replica_server_name, drcs.database_name, drs.database_id, drs.synchronization_state_desc, drs.database_state_desc
FROM sys.availability_replicas ar 
JOIN sys.dm_hadr_database_replica_states drs 
ON ar.replica_id=drs.replica_id 
JOIN sys.dm_hadr_database_replica_cluster_states drcs
ON drs.group_database_id=drcs.group_database_id

三、SQLServer AlwaysOn在輔助副本建立隻讀賬号

主副本都建立隻讀使用者的情況：

step1: 在主副本建立使用者ze_bi：通過TSQL查詢SID

select sid from sys.sql_logins where name = 'ze_bi'

step2: 查詢出主副本sid後，在輔助副本上建立ze_bi：
create login ze_bi  with password='passwd', sid=主副本查詢的sid

四、常見問題

4.1 安裝SQLSERVER2016缺少KB2919355更新檔

安裝相關更新檔即可。

4.2 sqlserver2016 R包下載下傳

R包需要手動下載下傳

4.3 目标主體名稱不正确，無法生成 SSPI 上下文

SQL運作在域使用者下，伺服器也在域中能通過IP連接配接，但是不能通過計算機名連接配接

測試計算機名也能正确解析

最後通過setspn解決

setspn -D MSSQLSvc/<servername.domainname>:1433 <servername>

 setspn -D MSSQLSvc/<servername.domainname> <servername>

這裡不需要重新開機機器，過了幾分鐘就能連接配接了

4.4 孤立使用者與登陸名的關聯

可以通過以下方法解決該問題。

1.建立一個test登入名，但是不要添加資料庫映射。

2.使用腳本，将孤立使用者test關聯到登入名test上：

Use [資料庫名]

go

sp_change_users_login 'update_one', 'test', 'test'

五、附錄

5.1 備份腳本