侧边栏壁纸
博主头像
WinJay博主等级

博观而约取 厚积而薄发

  • 累计撰写 319 篇文章
  • 累计创建 169 个标签
  • 累计收到 1 条评论
隐藏侧边栏

SQL Server2014高可用配置(镜像+复制)

WinJay
2021-04-12 / 0 评论 / 0 点赞 / 185 阅读 / 5,912 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-01-20,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

数据库服务器在安装数据库系统后更改计算机名称,将会导致复制订阅出现问题,所以需要将名称更改为一致,以下SQL语句用于处理此问题:

1)先执行如下脚本,看下名称是否一致

use master 
go 
select @@servername
select serverproperty('servername') 

2)如果一致就不需要修改,不一致时执行如下

脚本:

EXEC sp_dropserver 'old_server_name' 
go
EXEC sp_addserver 'current_computer_name','local'
  • 注意:old_server_name为上面查询出的第一行的值,current_computer_name为第二行的值

  • 注意:需要重启服务之后,再次运行第1个脚本,如下:

  • 观察发现已经保持一致。再次新建发布订阅,观察一切正常。

也可以通过批处理的方式处理此问题

if serverproperty('servername') <> @@servername
begin
declare @server sysname
set @server = @@servername
exec sp_dropserver @server = @server
set @server = cast(serverproperty('servername') as sysname)
exec sp_addserver @server = @server , @local = 'LOCAL'
end

数据库服务器在安装数据库系统后更改计算机名称,通过执行下述SQL,会发现数据库的所有者是更名前的计算机名,将其更改为“sa”:

select name, suser_sname(owner_sid) from sys.databases -- where name = 'AH_AnalyticalData'
USE AH_AnalyticalData
GO
sp_changedbowner 'sa'

SQL Server2008R2-镜像数据库实施手册(双机)


文档下载:https://www.winjay.cn/upload/2021/04/SQL%20Server2014%E9%AB%98%E5%8F%AF%E7%94%A8%E9%85%8D%E7%BD%AE%EF%BC%88%E9%95%9C%E5%83%8F+%E5%A4%8D%E5%88%B6%EF%BC%89-35b75eea74984e66920b813a31b3ba5c.docx

一、配置主备机

主机名称为:masterdb01,IP地址为:192.168.1.158

备机名称为:mirrordb01, IP地址为:192.168.1.254

二、主备实例互通

1.创建证书(主备可并行执行)

--主机执行:

--如果有endpoint,master key先删除

  --SELECT * FROM master.sys.database_mirroring_endpoints
  --DROP ENDPOINT Endpoint_Mirroring
  --DROP master key;
USE master;   
ALTER SERVICE MASTER KEY FORCE REGENERATE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy'; 
CREATE CERTIFICATE Matser_cert WITH SUBJECT = 'HOST_A certificate' , 
START_DATE = '2019-09-24', 
EXPIRY_DATE = '2099-09-24'; 

----数据库中已存在主密钥。执行此语句前,请先删除该主密钥

  --DROP CERTIFICATE Matser_cert

----删除数据库主密钥

  --DROP MASTER KEY
--备机执行:

​ --如果有endpoint,master key先删除

--select * from master.sys.database_mirroring_endpoints
--DROP ENDPOINT Endpoint_Mirroring
--drop master key;
USE master; 
ALTER SERVICE MASTER KEY FORCE REGENERATE
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy'; 
CREATE CERTIFICATE Slave_cert WITH SUBJECT = 'HOST_B certificate', 
START_DATE = '2019-09-24', 
EXPIRY_DATE = '2099-09-24'; 

----数据库中已存在主密钥。执行此语句前,请先删除该主密钥

--DROP CERTIFICATE Slave_cert

----删除数据库主密钥

--DROP MASTER KEY

2.创建连接的端点(主备可并行执行)

--主机执行:
CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE Matser_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); 
--备机执行:
CREATE ENDPOINT Endpoint_Mirroring 
STATE = STARTED 
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
FOR
DATABASE_MIRRORING 
( AUTHENTICATION = CERTIFICATE Slave_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

3.备份证书以备建立互联

--------------主备可并行执行

--主机执行:
BACKUP CERTIFICATE Matser_cert TO FILE = 'C:\SQLBackup\Matser_cert.cer';
--备机执行:
BACKUP CERTIFICATE Slave_cert TO FILE = 'C:\SQLBackup\Slave_cert.cer';

4. 互换证书

--将备份到C:\SQLBackup\的证书进行互换,即Matser_cert.cer复制到备机的C:\SQLBackup\。Slave_cert.cer复制到主机的C:\SQLBackup\。

5. 添加登陆名、用户

--------------主备可并行执行

**--**以下操作只能通过命令行运行,通过图形界面无法完成。

--主机执行:
CREATE LOGIN Slave_01_login WITH PASSWORD = 'ayhrrfl_19brsyj'; 
CREATE USER Slave_01_user FOR LOGIN Slave_01_login; 
CREATE CERTIFICATE Slave_cert AUTHORIZATION Slave_01_user FROM FILE = 'C:\SQLBackup\Slave_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Slave_01_login];
--备机执行:
CREATE LOGIN Matser_01_login WITH PASSWORD = 'ayhrrfl_19brsyj'; 
CREATE USER Matser_01_user FOR LOGIN Matser_01_login; 
CREATE CERTIFICATE Matser_cert AUTHORIZATION Matser_01_user FROM FILE = 'C:\SQLBackup\Matser_cert.cer'; 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Matser_01_login];

三、 建立镜像关系

以下步骤是针对每个数据库进行的,例如:现有主机中有7个数据库以下过程就要执行7次。

手工同步登录名和密码

​ 数据库镜像的缺点之一是无法维护登录名,所以需要我们手工维护登录。
​ 通常来说数据库都将会有若干个用户作为访问数据库的用户,并且数据库会有相应的登录名,但是在备机中缺少与之相对应的登录名,例如业务系统使用’myuser’作为登录名访问数据库,但是在备机中没有’myuser’这个登录名,因此一旦主备切换,业务系统就无法登录数据库了,这种情况称为'孤立用户'。在主机和备机数据库上建立相同用户名及密码即可。

准备备机数据库(主机备份及镜像还原)

在主机上备份数据库,先做完整备份,再做日志事务备份。
  • 主数据必须设置成完整模式进行备份,如下图:

    image-20210412152248801
    上图中将“恢复模式”选成“完整模式”。

    • 备份数据库,如下图:

image-20210412152616852

备份时将“备份类型”选成“完整”。

  • 备份事务日志,如下图:

    image-20210412153607540

将“备份类型”选成“事务日志”且备份目录与备份数据库的目录一致。

在备机上使用主机的备份文件进行还原(将主机的备份文件拷贝到备机上,)。

A. 还原要点

数据库还原时,在“还原”菜单选项中,完整备份选择“文件和文件组”进行还原,日志事务备份还原选择 “事务日志”。

B. 还原数据库

https://images2017.cnblogs.com/blog/58846/201801/58846-20180124165229444-1953242796.png

在还原数据库的时候需要使用选上**“WITH NORECOVERY”****。如图所示:**

https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151526858-1940661671.jpg

上二个截图对应的中文界面如下:

https://img-blog.csdnimg.cn/20190228144023604.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

https://img-blog.csdnimg.cn/20190228145156186.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

还原的路径建议设置成和主体服务器一致的路径。

如果数据库还原成功,将会变成如下所示的样子:

https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151540749-915727141.jpg

C. 还原日志备份

在还原日志备份的时候需要使用选上**“WITH NORECOVERY”** (重要,否则后面执行镜像会报错)****。如图所示:

https://img-blog.csdnimg.cn/20190228150400589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

3**、启动镜像操作****(先备机后主机)**

--备机执行:

ALTER DATABASE AH_AnalyticalData SET PARTNER = 'TCP://192.168.1.158:5022';

--说明:AH_AnalyticalData为数据库名。192.168.1.158为主机IP地址。

--主机执行:

ALTER DATABASE AH_AnalyticalData SET PARTNER = 'TCP://192.168.1.254:5022';

--说明:AH_AnalyticalData为数据库名。192.168.1.254为备机IP地址。

到此,SQL镜像热备配置完成。以下为镜像配置界面: https://img-blog.csdnimg.cn/20190301093539396.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

说明:目前为止,还没有配置见证服务器,所以上图为空,另外运行模式只有“高性能(异步)”和“高安全(同步)”可选择。

img

目前主体数据库和镜像数据库的状态显示界面如下:

img

img

四、 数据库复制

参考:https://www.cnblogs.com/datazhang/p/6142349.html

img

img

img

img

img

img

创建完成之后还有关键的一步,就是在主体和镜像服务器上执行 DBCC TRACEON(1448,-1),如果可以重启最好加入到实例的启动参数中去。如果不设置该参数,会报复制的事务正等待下一次日志备份或等待镜像伙伴更新

五、 测试

测试一:

在主体服务器中,点击下图中的“故障转移按钮”:然后观察主体和镜像数据库角色的切换。

https://img-blog.csdnimg.cn/20190228153425511.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

关闭主体服务器的SQL服务,然后观察各数据库状态,见下图:

https://img-blog.csdnimg.cn/20190301090000850.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size_16,color_FFFFFF,t_70

测试二:

连接发布库,向测试表中插入数据并查询

USE mydb

INSERT INTO Admin( user_name) VALUES('master' )

SELECT * FROM admin WHERE user_name='master'

https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185128179-190289702.png

连接订阅库,查询插入数据

USE mydb

SELECT * FROM admin WHERE user_name='master'

https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185157054-159743473.png

故障切换:

停止主体数据库服务,过一会在镜像库执行强制接收

use master ;

alter database mydb set partner FORCE_SERVICE_ALLOW_DATA_LOSS; --强制接收

切换成功后,新的主体数据库显示‘主体,已断开连接’

在新的主体服务器执行:

USE mydb

INSERT INTO Admin( user_name) VALUES('mirror' )

SELECT * FROM admin WHERE user_name='mirror'

https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185243991-1316868120.png

在订阅服务器查看:

USE mydb

SELECT * FROM admin WHERE user_name='mirror'

测试成功(测试成功的图居然忘记截了。。。)

当宕机的原主体数据库连接上来后,现在的主体数据库状态由“主体,已断开”变成“主体,挂起”,此时需要在现主体数据库上执行恢复操作,主体数据库状态变为“主体,已同步”

use master ;

alter database mydb set partner resume;

六、 常见命令

--切换主备(在主机principle数据库所在HOST上执行)

use master;

ALTER DATABASE testdb set partner failover;

-- 备机强制切换(在备机上数据库状态非同步状态下可执行)

use master;

ALTER DATABASE testdb set partner force_service_allow_data_loss;

--恢复镜像

use master;

ALTER DATABASE testdb set partner resume;

--取消见证服务器

ALTER DATABASE testdb SET WITNESS OFF ;

--取消镜像(在主机principle数据库所在HOST上执行)

ALTER DATABASE testdb SET PARTNER OFF;

--设置镜像数据库还原为正常

RESTORE DATABASE testdb WITH RECOVERY;

备份主数据库出现:Backup a database on a HDD with a different sector size,可以执行以下语句备份:

BACKUP DATABASE MyDB TO DISK = N'D:\MyDB.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup', STATS = 10, FORMAT

七、 总结

要进行以上sql server的镜像设置一定要使用sql server 的配置管理器开启TCP/IP协议,如下图:

https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151629155-1359476577.jpg

如果没有启用TCP/IP协议则只能在同一个网段内的机器配置镜像,前面的配置步骤里面所用到的IP地址要换成对应的实例名。同一个网段配置并使用镜像的时实性、传输速率更高,适用于大数据量的同步,跨网段或者跨公网的sql server 镜像一般适用于数据量小,时实性要求不高的数据同步,而且数据库在公网上同步也不安全。

0

评论区