AlwaysOn添加新可用性副本實(shí)戰(zhàn)
作者：雪竹頻道
SQL Server一種高可用性和災(zāi)備性解決方案，AlwaysOn 可用性組允許在多個(gè)數(shù)據(jù)庫(kù)副本之間同步數(shù)據(jù)，并提供了故障轉(zhuǎn)移和自動(dòng)故障恢復(fù)功能，以確保數(shù)據(jù)庫(kù)的持續(xù)可用性和數(shù)據(jù)保護(hù)。
前言
SQL Server一種高可用性和災(zāi)備性解決方案，AlwaysOn 可用性組允許在多個(gè)數(shù)據(jù)庫(kù)副本之間同步數(shù)據(jù)，并提供了故障轉(zhuǎn)移和自動(dòng)故障恢復(fù)功能，以確保數(shù)據(jù)庫(kù)的持續(xù)可用性和數(shù)據(jù)保護(hù)。
一、前期調(diào)研工作
1、數(shù)據(jù)庫(kù)版本及組件
- 版本查詢
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的啟動(dòng)賬號(hào)及密碼
二、過(guò)程
1、配置靜態(tài)IP地址
配置SQL SERVER2012服務(wù)器，配置靜態(tài)IP地址。
2、關(guān)閉防火墻和自動(dòng)更新功能
關(guān)閉防火墻和自動(dòng)更新功能
3、安裝net3.5和故障轉(zhuǎn)移集群功能
安裝.NET Framework 3.5功能和故障轉(zhuǎn)移集群功能。
4、加域并配置DNS
將計(jì)算機(jī)加入域，將與用戶添加進(jìn)本地管理員組，并配置DNS地址。
5、安裝SQL SERVER
- 獨(dú)立安裝SQL SERVER，將與用戶設(shè)置為SQL SERVER實(shí)例和SQL SERVER代理的啟動(dòng)用戶，重啟兩項(xiàng)服務(wù)。
- 將新節(jié)點(diǎn)添加進(jìn)故障轉(zhuǎn)移集群中，注意，若該節(jié)點(diǎn)不在故障轉(zhuǎn)移集群中，則無(wú)法啟用ALWAYSON高可用性。
6、新節(jié)點(diǎn)加入集群
打開(kāi)故障轉(zhuǎn)移集群管理器，連接到現(xiàn)有集群后，右擊節(jié)點(diǎn)，選擇添加節(jié)點(diǎn)。
此時(shí)進(jìn)入添加節(jié)點(diǎn)向?qū)?，進(jìn)入驗(yàn)證階段，點(diǎn)擊下一步。
點(diǎn)擊瀏覽，進(jìn)入選擇計(jì)算機(jī)頁(yè)面，此時(shí)位置為整個(gè)域。點(diǎn)擊高級(jí)-立即查找，選擇需要添加的計(jì)算機(jī)，點(diǎn)擊確定，服務(wù)器添加成功。
一直點(diǎn)擊下一步，進(jìn)入驗(yàn)證階段。
驗(yàn)證完成即進(jìn)入添加節(jié)點(diǎn)向?qū)щA段。
點(diǎn)擊下一步。
7、啟用ALWAYSON高可用性
添加集群節(jié)點(diǎn)成功后，此時(shí)可以打開(kāi)配置管理器，啟用ALWAYSON高可用性。啟用后重啟SQL SERVER 實(shí)例。
8、數(shù)據(jù)庫(kù)的進(jìn)行備份和還原
先測(cè)試在WIN-P4節(jié)點(diǎn)用域帳號(hào)能否登錄到其他節(jié)點(diǎn)。測(cè)試成功后，可以進(jìn)行完備-日志備-完整還原-日志還原。
（1）全庫(kù)備份
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）恢復(fù)norecovery
- 恢復(fù)全庫(kù)
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- 日志恢復(fù)
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
GO9、添加副本
在主副本上選擇可用性組-DB01，選擇添加副本，此時(shí)跳出添加副本向?qū)Ы缑?。在輔助副本中是沒(méi)有添加副本這個(gè)選項(xiàng)的。
點(diǎn)擊下一步,連接原有的輔助副本。
點(diǎn)擊下一步,添加副本.
點(diǎn)擊下一步,選擇僅連接.點(diǎn)擊下一步進(jìn)行創(chuàng)建。
點(diǎn)擊下一步,直至可用性組創(chuàng)建成功。
注意:若只對(duì)一個(gè)數(shù)據(jù)庫(kù)進(jìn)行了還原操作，則在添加副本時(shí)會(huì)提示副本創(chuàng)建失敗，原因是為還原的數(shù)據(jù)庫(kù)無(wú)法連接。退出后會(huì)發(fā)現(xiàn)其實(shí)已經(jīng)添加副本成功，做過(guò)還原操作的數(shù)據(jù)庫(kù)也已同步成功。而失敗的數(shù)據(jù)庫(kù)雖然同步失敗，但是會(huì)出現(xiàn)在可行性數(shù)據(jù)庫(kù)列表中。此時(shí)只需要對(duì)該數(shù)據(jù)庫(kù)進(jìn)行完備-日志備-完整還原-日志還原后，在可用性數(shù)據(jù)庫(kù)列表下選擇該數(shù)據(jù)庫(kù)，右鍵選擇連接，該數(shù)據(jù)庫(kù)即可成為可用性數(shù)據(jù)庫(kù)中的一部分并正常同步。
10、驗(yàn)證主備庫(kù)是否同步
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在輔助副本創(chuàng)建只讀賬號(hào)
主副本都創(chuàng)建只讀用戶的情況：
step1: 在主副本創(chuàng)建用戶ze_bi：通過(guò)TSQL查詢SID
select sid from sys.sql_logins where name = 'ze_bi'
step2: 查詢出主副本sid后，在輔助副本上創(chuàng)建ze_bi：
create login ze_bi with password='passwd', sid=主副本查詢的sid四、常見(jiàn)問(wèn)題
1、安裝SQL SERVER2016缺少KB2919355補(bǔ)丁
安裝相關(guān)補(bǔ)丁即可。
2、SQL SERVER2016 R包下載
R包需要手動(dòng)下載。
3、目標(biāo)主體名稱不正確，無(wú)法生成 SSPI 上下文
SQL運(yùn)行在域用戶下，服務(wù)器也在域中能通過(guò)IP連接，但是不能通過(guò)計(jì)算機(jī)名連接。
測(cè)試計(jì)算機(jī)名也能正確解析。
最后通過(guò)setspn解決。
setspn -D MSSQLSvc/<servername.domainname>:1433 <servername>
setspn -D MSSQLSvc/<servername.domainname> <servername>這里不需要重啟機(jī)器，過(guò)了幾分鐘就能連接了。
4、孤立用戶與登陸名的關(guān)聯(lián)
可以通過(guò)以下方法解決該問(wèn)題。
1.新建一個(gè)test登錄名，但是不要添加數(shù)據(jù)庫(kù)映射。
2.使用腳本，將孤立用戶test關(guān)聯(lián)到登錄名test上：
Use [數(shù)據(jù)庫(kù)名]
go
sp_change_users_login 'update_one', 'test', 'test'五、附錄
1、備份腳本
（1）全庫(kù)備份
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）恢復(fù)norecovery
- 恢復(fù)全庫(kù)
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- 日志恢復(fù)
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
GO2、開(kāi)啟備份目錄方法
-- 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;
GO3、參考
https://blog.csdn.net/weixin_38357227/article/details/79115005。
責(zé)任編輯：姜華
來(lái)源：
今日頭條
