AlwaysOn添加新可用性副本實戰

作者：雪竹頻道 2023-07-31 08:16:41

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

前言

一、前期調研工作

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

組件

2、SQL SERVER的啟動賬號及密碼

二、過程

1、配置靜態IP地址

配置SQL SERVER2012服務器，配置靜態IP地址。

2、關閉防火墻和自動更新功能

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

3、安裝net3.5和故障轉移集群功能

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

4、加域并配置DNS

將計算機加入域，將與用戶添加進本地管理員組，并配置DNS地址。

5、安裝SQL SERVER

獨立安裝SQL SERVER，將與用戶設置為SQL SERVER實例和SQL SERVER代理的啟動用戶，重啟兩項服務。
將新節點添加進故障轉移集群中，注意，若該節點不在故障轉移集群中，則無法啟用ALWAYSON高可用性。

6、新節點加入集群

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

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

點擊瀏覽，進入選擇計算機頁面，此時位置為整個域。點擊高級-立即查找，選擇需要添加的計算機，點擊確定，服務器添加成功。

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

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

點擊下一步。

7、啟用ALWAYSON高可用性

添加集群節點成功后，此時可以打開配置管理器，啟用ALWAYSON高可用性。啟用后重啟SQL SERVER 實例。

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

9、添加副本

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

點擊下一步,連接原有的輔助副本。

點擊下一步,添加副本.

點擊下一步,選擇僅連接.點擊下一步進行創建。

點擊下一步,直至可用性組創建成功。

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

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

四、常見問題

1、安裝SQL SERVER2016缺少KB2919355補丁

安裝相關補丁即可。

2、SQL SERVER2016 R包下載

R包需要手動下載。

3、目標主體名稱不正確，無法生成 SSPI 上下文

SQL運行在域用戶下，服務器也在域中能通過IP連接，但是不能通過計算機名連接。

測試計算機名也能正確解析。

最后通過setspn解決。

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

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

這里不需要重啟機器，過了幾分鐘就能連接了。

4、孤立用戶與登陸名的關聯

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

1.新建一個test登錄名，但是不要添加數據庫映射。

2.使用腳本，將孤立用戶test關聯到登錄名test上：

Use [數據庫名]

go

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

五、附錄

1、備份腳本

（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、開啟備份目錄方法

-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1' 
RECONFIGURE

EXEC XP_CMDSHELL 'net use Y: \\172.18.248.98\sharebackup /user:AP1AZRAP3051\bcc_byol_project  6GB!eZ!2m4KmfI1l'

EXEC XP_CMDSHELL 'Dir Y:' 
RECONFIGURE;
GO

3、參考

https://blog.csdn.net/weixin_38357227/article/details/79115005。

責任編輯：姜華來源：今日頭條

SQL Server AlwaysOn