【sql server镜像】SQL SERVER 基于数据库镜像的主从同步(数据库镜像实践汇总)

最新推荐文章于 2024-08-26 03:16:59 发布
转载 最新推荐文章于 2024-08-26 03:16:59 发布 · 1.4k 阅读 · 10 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/gered/p/10601202.html
文章标签：

#数据库 #网络 #操作系统

本文详细介绍了SQL SERVER数据库镜像的概念，包括服务器角色、模式优缺点，以及实施前提和步骤。通过实例展示了如何进行主从同步、故障排查和监控，强调了自动故障转移和见证服务器的角色。此外，还探讨了数据库镜像在高安全性、高性能模式下的运行模式，并提供了常见故障解决方案和镜像监控方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

SQL SERVER 基于数据库镜像的主从同步

Author:chaoqun.guo    createtime:2019-03-26

 

 

目录

SQL SERVER 基于数据库镜像的主从同步... 1

1、概念... 2

1.1、服务器概念... 2

1.2、模式概念... 2

1.3、数据库镜像的优势... 3

1.4、数据库镜像的不足... 3

2、实施前提（基于证书访问实现）... 4

3、实施步骤（基于证书访问实现）... 4

3.1、步骤目录... 4

3.2、实操... 5

3.3、主备切换... 7

4、数据库镜像监控... 9

4.1、数据库镜像监视器... 9

4.2、系统存储过程监视（联机丛书—监视数据库镜像）... 9

5、常见故障排查... 11

【5.1】服务器网络地址 “TCP://xxx:5022″ 无法访问或不存在。... 11

【5.2】TCP://xxx:5022 的数据库镜像连接错误... 11

【5.3】程序如何在主从切换时自动切换连接？... 11

【5.4】孤立用户问题... 11

【5.5】自增键在镜像中的影响... 11

【5.6】镜像服务器与主服务器断开... 11

【5.7】其他相关... 12

附录1：数据库镜像主从+见证中断情况... 13

1.1、1个点中断的情况... 13

1.2、2个点中断的情况... 13

1.3、镜像集群监控概述... 13

附录2：镜像相关信息查阅... 14

2.1、查看master中的证书... 14

2.2、查看镜像端点状态（联机丛书视图参考）... 14

2.3、查看端点连接权限... 14

2.4、常见删除语句... 14

2.5、更多查阅... 14

附录3：加入见证服务器，实现自动故障转移（基于证书）... 15

附录4：基于域账户的镜像搭建... 18

4.1、操作步骤... 18

4.2、操作实践... 18

参考文献... 25

 

 

1、概念

1.1、服务器概念

◆ 主体服务器（Principal Server）

　　其中一个实例为客户端提供服务，这个实例称为"主体服务器"。该服务器"扮演"主体角色"，其数据库副本为当前的"主体数据库"。

 

◆ 镜像服务器（Mirror Server）

　　另一个实例则充当备用服务器，这个实例称为"镜像服务器"（Mirror Server）。该服务器扮演"镜像角色"，其数据库副本为当前的"镜像数据库"。镜像数据库不能供客户端访问，但是可以为镜像数据库创建一个快照，让客户端访问这个快照。

 

◆ 见证服务器（Witness Server）

　　见证服务器并不能用于数据库，只是用来支持自动故障转移。见证服务器验证主体服务器是否保持运行，当见证服务器与主体服务器断开连接之后，如果此时镜像服务器和见证服务器保持相互连接，则镜像服务器启动自动故障转移，成为新的主体服务器。

 

 

1.2、模式概念

数据库镜像会话以同步操作或异步操作运行。

　　在同步操作下，事务将在伙伴双方处提交。由于主体数据库需要等待镜像数据库将日志写入磁盘后返回的确认消息，因此会延长事务滞后时间。在异步操作下，事务不需要等待镜像服务器将日志写入磁盘便可提交，这样可最大程度地提高性能。

在SQL SERVER 2008之后，主库和镜像库之间的日志流传送会默认使用压缩，压缩一方面降低了网络压力，另一方面增大了镜像两端的CPU压力。 可以打开 TF 1462 来关闭日志流压缩，SQL SERVER 2005 上日志传送没有使用压缩。

 

根据是否同步操作以及是否支持自动故障转移功能，数据库镜像有以下三种运行模式。

（1） 高安全性模式：主库把事务日志数据信息发给从库，从库返回事务日志持久化确认信息，确认同步后，事务将在主从库一起提交。

（2） 高性能模式：主库把事务日志数据信息发给从库，发完后无需等待从库返回确认信息。

（3） 自动故障转移模式（高可用模式）：在高安全模式运行时，可以添加见证服务器，从而实现自动故障转移。

 

 

1.3、数据库镜像的优势

数据库镜像技术有以下优势：

 

（1）消除存储方面的单一故障点：不用共享磁盘

（2）提高数据库可用性：快速自动/手动主从切换

（3）增强的数据保护：提供完整的数据冗余

（4）自动页修复：2008企业版在某些类型的错误导致页损坏，使其无法读取后，在 SQL Server 2008 企业版或更高版本上运行的数据库镜像伙伴（主体或镜像）将尝试自动修复该页。无法读取该页的伙伴将从其伙伴请求该页的新副本。如果此请求成功，则将以可读副本替换不可读的页，并且这通常会解决该错误。

 

1.4、数据库镜像的不足

镜像数据库技术有以下不足之处：

 

（1）版本限制

　　对于标准版的 SQL Server 实例，只可以使用"高安全模式"，即主体数据库与镜像数据库必须实现同步操作。在这种运行模式时，如果任何一个伙伴遇到性能问题，都将使同步操作带来较大的延时。通常标准版的 SQL Server 受到一些技术限制导致性能不能提升，从而使同步操作的延时更加明显。

 

（2）镜像数据库的访问限制

　　镜像数据库甚至不可以提供只读访问，只有通过创建快照才能访问，因此镜像数据库的利用率不高。

由于数据库镜像技术存在上述不足，SQL Server 后续产品将删除此项功能，建议改用 AlwaysOn 可用性组。SQL Server 2016 标准版中已经引入了"AlwaysOn 基本可用性组"，用来替代数据库镜像技术。

  

2、实施前提（基于证书访问实现）

（1）实例版本：必须是2005 SP1及以上（且兼容级别也要在这个版本及以上），且主从版本一致。

（2）数据库版本：必须是标准版及更高的开发/评估/企业，且只有企业版/开发版才能实现高性能模式。且主从一致。

（3）通信：确认网络能ping通，确定端点端口（默认一般是5022）与实例端口（默认1433）可以telnet 通实现访问。

（4）磁盘：足够的磁盘空间（全备复制+事务备复制+还原空间+预留空间）。最好主从是相同的目录（不同会造成无法加文件）。

（5）限制：不支持 FILESTREAM。不能在主体上创建它。不能为包含 FILESTREAM 文件组的数据库配置数据库镜像。

（6）系统：32位系统下，单实例最多支持10个数据库做镜像。

（7）主数据库：镜像的数据库对象不能是系统数据库。主库必须是完整恢复模式。且主从库必须是相同的数据库名。

（8）从数据库：利用主数据库的相关备份进行还原，必须以norecovery模式（RESTORING 状态）

（9）权限：登录名具有实施步骤权限，最好sysadmin。可能还需要实例账户拥有一定程度的windows权限，最好是admin组；

（10）端点：镜像两端的加密算法必须保持一致，否则无法搭建。（因为不同版本默认加密算法不一样）小版本没关系，但前提是从库可以还原主库。

 

 

3、实施步骤（基于证书访问实现）

3.1、步骤目录

主服务器host_A

从服务器host_B

【1】在master中，创建数据库秘钥

【1】创建master数据库秘钥

【2】基于【1】中秘钥，创建服务器实例加密证书的出站证书

【2】基于【1】中秘钥，创建服务器实例加密证书的出站证书

【3】使用服务器实例的证书为该服务器实例创建端点。

【3】使用服务器实例的证书为该服务器实例创建端点。

【4】将证书备份到文件，并将其安全地复制到从服务器。

【4】将证书备份到文件，并将其安全地复制到主服务器。

【5】为从服务器创建登录名。

【5】为主服务器创建登录名。

【6】创建一个使用该登录名（【5】中的）的用户

【6】创建一个使用该登录名（【5】中的）的用户

【7】使用证书对【6】中用户授权

【7】使用证书对【6】中用户授权

【8】对【5】中登录名进行连接端点的授权

【8】对【5】中登录名进行连接端点的授权

【9】主数据库设置为完全恢复模式，并以全备传输到从服务器

【9】以norecovery模式还原需要镜像的库

【10】（后做）设置镜像伙伴为从库

【10】（先做）设置镜像伙伴为主库

【11】核验，基于SSMS与T-SQL

【11】核验，基于SSMS与T-SQL

 

 

 

 

 

 

3.2、实操

主服务器：SQLSVR1, 192.168.1.1

从服务器：SQLSVR2, 192.168.1.2

--1、创建 master 数据库主密钥

Use master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

GO

 

--2、对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

 

USE master;

CREATE CERTIFICATE SQLSVR1_cert	WITH SUBJECT = 'SQLSVR1 certificate for database mirroring', 
   start_date='2019-03-01',expiry_date='2030-01-01';

GO

 

--3、 使用主服务器实例的证书 SQLSVR1_cert 为主服务器 SQLSVR1 创建端点。
Use master;

CREATE ENDPOINT [默认的镜像端点]
   STATE = STARTED AS TCP (
      LISTENER_PORT=5022
      , LISTENER_IP = ALL
   ) 

FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE SQLSVR1_cert
最低0.47元/天 解锁文章

200万优质内容无限畅学
sqlserver镜像设置
linhao's 博客
12-27 1967
本案例是双机热备，只设置主体服务器（主）和镜像服务器（从），不设置见证服务器设置镜像前先检查是否启用了主从服务器数据库的 TCP/IP协议 和 RemoteDAC（1）打开SQL Server配置管理器，选择“SQL Server网络配置”-“MSSQLSERVER的协议”，在右侧启用TCP/IP协议，打开TCP/IP属性把IP地址设置为本地服务器地址。
sqlserver2008基于发布/订阅功能实现主从数据库数据实时同步
u010736534的博客
04-21 2166
sqlserver2008基于发布/订阅功能实现主从数据库数据实时同步 需求描述：项目部署2台web服务器采用负载均衡，2台db主从数据库，为确保主数据库发生故障时，通过快速切到从数据库服务器，程序不挂掉并且从数据库能最大化还原数据，减少数据丢失，基于sqlserver发布/订阅功能实现主从数据库数据实时同步。一、发布/订阅配置1、将主（发布服务器）数据库数据备份并在从（订阅服务器）数据库上还原，...
生产环境实战 SQL Server 2016 镜像
06-19
本课程同样适合于SQL Server 2016/2014/2008R2/2005 课时1：镜像架构介绍 课时2：搭建镜像 前期准备工作 课时3：不自动故障转移的高安全模式(同步) 课时4：镜像-高性能模式(异步) 课时5：自动故障转移的高安全模式(同步)
【项目笔记】SQL Server 2012 数据库镜像
wjy27的博客
10-13 1111
双数据库镜像，如果主数据库出问题时，就自动连接到备数据库，带配置见证服务器。
sqlserver2012主从搭建
01-30
从创建域控,搭建集群到高可用自动转移设置全程图文齐全, 原创!
SQL Server 2019搭建主从热备（多图）
Crazy_young_man的博客
02-09 3828
SQL Server 2019搭建主从热备（多图）
SQL Server 数据库镜像
热门推荐
July
01-15 1万+
文章目录故障转移群集技术 vs 日志传送技术数据库镜像 vs 故障转移群集技术数据库镜像 vs 日志传送技术数据库镜像的设计目的2.4.1 数据库镜像的基本概念基本术语和角色镜像数据库的作用镜像技术的限制数据库镜像会话见证服务器的作用见证服务器的的特点主体、镜像、见证服务器实例通信原理主体数据库和镜像数据库数据同步原理数据压缩的优点发送队列重做队列2.4.2 数据库镜像操作模式事务安全性同步传送...
sql server主从同步
weixin_48960094的博客
07-11 1222
SyncNavigator是一款功能强大的数据库同步软件，适用于SQLSERVER, MySQL，具有自动/定时同步数据、无人值守、故障自动恢复、同构/异构数据库同步、断点续传和增量同步等功能，支持Windows xp以上所有操作系统，适用于大容量数据库快速同步。 安装包下载地址:https://www.syncnavigator.cn/Setup.zip 帮助文档地址:https://www.syncnavigator.cn/Help_zh-CN.chm Web文档地址:https://...
SQLServer 镜像配置操作
码斯克的专栏
09-22 2391
SQLServer 镜像配置操作
SqlServer2008 数据库同步的两种方式 (发布、订阅)
weixin_33753003的博客
05-31 636
　　上篇中说了通过SQL JOB的方式对数据库的同步，这一节作为上一节的延续介绍通过发布订阅的方式实现数据库之间的同步操作。发布订阅份为两个步骤：1、发布。2订阅。首先在数据源数据库服务器上对需要同步的数据进行发布，然后在目标数据库服务器上对上述发布进行订阅。发布可以发布一张表的部分数据，也可以对整张表进行发布。下面分别介绍发布、订阅的过程。 　　1、发布。发布需要用实际的服务器名称，不能使用服...
sql server 检查主从是否同步
weixin_33093403的博客
08-26 342
我整理的一些关于【数据库】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/bLN8S1SQL Server 主从同步状态检查 在现代企业应用中，数据库的高可用性和数据的一致性是至关重要的。SQL Server 提供了多种复制和数据同步的解决方案，主从数据库模式（也称为主从复制...
sqlserver数据库同步
ChinaHuanyang的专栏
11-07 3362
 一、确认数据库运行环境是否配置正确打开SQL Server Management Studio，新建查询： select * from sys.servers GO //这里可得到原来的计算机名称。然后将其记录下来（复制即可）  看这里的name是否和你的服务器的计算机名称一样,如果一样可以跳到文档(二)，否则请按如下操作更改 新建查询：
SQL Server数据同步
qq_37086007的博客
08-31 1万+
SQL Server数据库数据同步的步骤 1、设置登录名密码、查看登录名状态、查看服务器名称 1）选择用户名“sa”右键选择“属性”，如图1　： 图1  2）设置密码，如图2　： 图2 3）设置状态、“确认”，如图3　： 图3 4）查看服务器名称，在sqlserver中新建查询，输入sql语句 select @@SERVERNAME，查询到本服务器的别名，如图4　： ...
实验二：SQL server 2005高可用性之----数据库镜像
anjichan4261的博客
11-04 341
如转载，请注明出处：http://blog.youkuaiyun.com/robinson_0612/archive/2009/11/04/4769060.aspx SQL server 2005高可用性之数据库镜像，是SQL server 2005的新技术之一，是一种基于软件的高可用性解决方案，可以对不同服务器或同一服务器不同实例之间的数据库实验无数据延迟，自动故障转移的热备份...
如何实现SQLSERVER2005数据库同步（数据库镜像）技术 SQLServer2005同步复制技术实现
香柏傲雪的知识栏目
03-15 1820
<br /><br />步骤一、   预备工作<br />1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户 <br />--管理工具 <br />--计算机管理 <br />--用户和组 <br />--右键用户 <br />--新建用户 <br />--建立一个隶属于administrator组的登陆windows的用户（SynUser）<br />2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作: <br />我的电
SQL Server实例间同步登录用户
2401_85789772的博客
07-18 883
目前来看，作业等其他实例对象的同步还比较难实现，比如作业分为很多步骤，而且作业包含的命令也比较复杂，作业也支持调用其他子系统，比如 PowerShell ，ActiveX，CmdExec等数据库外部程序和命令，用动态SQL方式很难处理。（3）删除用户：如果从库存在同名用户，就不新建用户，否则新建用户，为了尽可能减少对线上生产环境影响，不做删除用户操作，所以如果从库存在同名用户并且用户SID不同，建议手动删除用户由脚本自动同步主库用户过来。4、高可靠性，使用SQLServer自带原生工具，足够简单高效。
SQL server 数据库同步（发布与订阅）【解决办法】
神秘人的博客
04-28 6539
介绍SQLserver服务器的数据库订阅与发布的办法
SQL Server主从同步配置
weixin_40225165的博客
11-29 4130
SQL Server
SQL Server 2008 数据库通过镜像同步备份（数据库热备）
weixin_30883777的博客
09-10 262
数据库镜像运行模式： 异步数据库镜像模式（异步，高性能模式） 同步数据库镜像模式（同步，高安全模式） 参考资料： http://technet.microsoft.com/zh-cn/library/bb934127%28v=sql.105%29.aspx http://technet.microsoft.com/zh-cn/library/ms190941%28v=sql...
sql server数据库主从同步
最新发布
01-23
### SQL Server 数据库主从同步配置最佳实践 #### 配置SQL Server 主从同步概述 为了实现高可用性和数据冗余，SQL Server 提供了多种复制方式来设置主从同步。最常用的方式是通过事务复制(Transactional Replication)，它允许实时或接近实时的数据传输。 #### 准备工作 确保主数据库与镜像数据库使用相同的实例账户，并重新启动数据库服务 `SQL Server (MSSQLSERVER)`[^2]。这一步骤对于确保两个环境的一致性至关重要。 #### 创建发布者(Publisher) 和订阅者(Subscriber) 1. **创建发布者** - 打开 SQL Server Management Studio 并连接到要作为发布者的服务器。 - 展开“复制”，右键单击“本地出版物(Local Publications)”并选择“新建出版物(New Publication)...”。 ```sql USE [distribution] GO EXEC sp_adddistributor @distributor = N'DIST_SERVER_NAME', @password = N'dist_password' GO ``` 2. **定义文章(Articles)** - 在向导中指定要发布的表或其他对象。可以选择整个表、视图或是查询结果集的一部分。 3. **设定快照代理(Snapshot Agent)属性** - 可以立即生成初始快照或将现有备份用于初始化目的。 #### 添加订阅者 - 连接到发布者所在的服务器，在“复制监视器(Copy Monitor)”下找到对应的出版物。 - 选择“新建推送订阅(New Push Subscription...)”或“新建拉取订阅(New Pull Subscription...)" 来添加新的订阅者节点。 ```sql -- 推送订阅示例 exec sp_addsubscription @publication = 'YourPublicationName', @subscriber = 'SUBSCRIBER_SERVER_NAME', @destination_db = 'TargetDatabase', @sync_type = 'automatic', @article = 'all', @update_mode = 'read only'; go ``` #### 日志传送(Log Shipping) 除了传统的复制机制外，还可以考虑采用日志传送技术来进行灾难恢复准备。此功能会定期将主服务器上的交易日志备份发送给次级站点，并应用这些更改保持两者之间尽可能一致的状态。 #### 故障排查常见问题 当遇到同步失败的情况时，可以从以下几个方面入手： - 检查网络连通性：确认源端和目标端之间的通信正常无阻塞； - 查看错误日志：查看 SQL Server 错误日志和服务管理控制台中的记录寻找线索； - 调整权限设置：验证登录账号是否拥有足够的权利执行必要的操作； - 更新统计信息：有时过期的统计数据可能导致性能下降甚至同步中断；
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值