服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

2023-08-01 07:00未知服务器之家 Sql Server

前言 SQL Server一种高可用性和灾备性解决方案,AlwaysOn 可用性组允许在多个数据库副本之间同步数据,并提供了故障转移和自动故障恢复功能,以确保数据库的持续可用性和数据保护。 一、前期调研工作 1、数据库版本及组件 版本

AlwaysOn添加新可用性副本实战

前言

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
  • 组件

AlwaysOn添加新可用性副本实战

2、SQL SERVER的启动账号及密码

AlwaysOn添加新可用性副本实战

二、过程

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高可用性。

AlwaysOn添加新可用性副本实战

6、新节点加入集群

打开故障转移集群管理器,连接到现有集群后,右击节点,选择添加节点。

AlwaysOn添加新可用性副本实战

此时进入添加节点向导,进入验证阶段,点击下一步。

AlwaysOn添加新可用性副本实战

点击浏览,进入选择计算机页面,此时位置为整个域。点击高级-立即查找,选择需要添加的计算机,点击确定,服务器添加成功。

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

一直点击下一步,进入验证阶段。

AlwaysOn添加新可用性副本实战

验证完成即进入添加节点向导阶段。

AlwaysOn添加新可用性副本实战

点击下一步。

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

7、启用ALWAYSON高可用性

添加集群节点成功后,此时可以打开配置管理器,启用ALWAYSON高可用性。启用后重启SQL SERVER 实例。

AlwaysOn添加新可用性副本实战

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,选择添加副本,此时跳出添加副本向导界面。在辅助副本中是没有添加副本这个选项的。

AlwaysOn添加新可用性副本实战

点击下一步,连接原有的辅助副本。

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

点击下一步,添加副本.

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

点击下一步,选择仅连接.点击下一步进行创建。

AlwaysOn添加新可用性副本实战

AlwaysOn添加新可用性副本实战

点击下一步,直至可用性组创建成功。

AlwaysOn添加新可用性副本实战

注意:若只对一个数据库进行了还原操作,则在添加副本时会提示副本创建失败,原因是为还原的数据库无法连接。退出后会发现其实已经添加副本成功,做过还原操作的数据库也已同步成功。而失败的数据库虽然同步失败,但是会出现在可行性数据库列表中。此时只需要对该数据库进行完备-日志备-完整还原-日志还原后,在可用性数据库列表下选择该数据库,右键选择连接,该数据库即可成为可用性数据库中的一部分并正常同步。

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。

延伸 · 阅读

精彩推荐