AlwaysOn添加新可用性副本实战-51CTO.COM

前言

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。