SQLServer 复制和数据库镜像 详细配置部署

概述SQLserver 可以把镜像和复制同时部署，结合了双方的高可用性，可以使数据库得到更好的高可用性和容灾的保证。 关于镜像：数据库镜像 关于复制：SQL Server 复制 本章的复制为事务可更新订阅：事务复制的可更新订阅 关于复制和数据库镜像：复制和数据库镜像 理论的东西参考官方文档吧，这里主要是部署配置过程。 下图为本章参考部署的架构图： 本章模拟的服务器： kk-ad 192.168.2.1

sqlserver 可以把镜像和复制同时部署，结合了双方的高可用性，可以使数据库得到更好的高可用性和容灾的保证。

关于镜像：数据库镜像

关于复制：SQL Server 复制

本章的复制为事务可更新订阅：事务复制的可更新订阅

关于复制和数据库镜像：复制和数据库镜像

理论的东西参考官方文档吧，这里主要是部署配置过程。

下图为本章参考部署的架构图：

本章模拟的服务器：

kk-ad	192.168.2.1	DC（域控）
kk-db1	192.168.2.10	主机（Replication + Mirror）
kk-db2	192.168.2.11	镜像（Mirror）
kk-db3	192.168.2.12	见证机（WITnesS）+ 分发
kk-db4	192.168.2.13	订阅（测试1个）

将复制与数据库镜像一起使用时，注意以下要求和注意事项：

1. 主体数据库和镜像数据库必须共享分发服务器。 建议此处使用远程分发服务器，如果发布服务器有意外故障转移，则远程分发服务器可以提供较大的容错能力。

2. 对于合并复制，以及对于使用只读订阅服务器或排队更新订阅服务器的事务复制，复制支持对发布数据库进行镜像。 不支持即时更新对等拓扑中的订阅服务器、Oracle 发布服务器、发布服务器并重新发布。

 

3. 存在于数据库外部的元数据和对象不复制到镜像数据库，包括登录名、作业、链接服务器等等。 如果要求镜像数据库中有元数据和对象，则必须手动复制它们。

配置复制和数据库镜像主要步骤：

1. 配置数据库镜像；（参考 SQLServer 数据库镜像（二）域环境中完整镜像脚本配置）

2. 配置分发服务器；（参考 如何配置发布和分发  ， 如何在分发服务器上启用远程发布服务器）

3. 配置发布和订阅；（参考 如何创建事务性发布的可更新订阅  ，SqlServer 使用脚本创建分发服务及事务复制的可更新订阅）

说明及注意修改的地方：

本测试使用域账号作为数据库登录账号：[KK\UserReplMirror]

对应数据库用户：[UserReplMirror]

测试数据库：[DemoDB]

确保各服务器能相互访问

数据库 [DemoDB]  恢复模式为完整模式（镜像必须）

数据库 [DemoDB]  所有者改为 [sa]

账号 [KK\UserReplMirror] 授予 sysadmin 权限；或者在数据库 [DemoDB] 中授予用户 [UserReplMirror] 数据库角色为 [db_owner] 

复制的代理作业的所有者都改为[sa]

数据库外的对象不会镜像：如登录账户，代理作业 等

1. 配置数据库镜像：

--主体创建数据库USE [master]GOCREATE DATABASE [DemoDB]GOALTER DATABASE [DemoDB] SET RECOVERY FulL WITH NO_WAITGO--主体：创建证书 和 备份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';  GO  CREATE CERTIFICATE Cert_kk_db1_mssqlserver   WITH SUBJECT = 'Cert_kk_db1_mssqlserver',START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01';  GO  BACKUP CERTIFICATE Cert_kk_db1_mssqlserver   TO file = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';  GO  --镜像：创建证书 和 备份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';  GO CREATE CERTIFICATE Cert_kk_db2_mssqlserver WITH SUBJECT = 'Cert_kk_db2_mssqlserver',EXPIRY_DATE = '2018-06-01'; GO BACKUP CERTIFICATE Cert_kk_db2_mssqlserver  TO file = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';  GO --见证：创建证书 和 备份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';  GO CREATE CERTIFICATE Cert_kk_db3_mssqlserver WITH SUBJECT = 'Cert_kk_db3_mssqlserver',EXPIRY_DATE = '2018-06-01'; GO BACKUP CERTIFICATE Cert_kk_db3_mssqlserver  TO file = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';  GO --  交换证书(相互拷贝证书):  /*  主体证书(拷贝到)————>镜像、见证  镜像证书(拷贝到)————>主体、见证  见证证书(拷贝到)————>主体、镜像  */  --  主体(创建用户、还原证书、创建端点)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM windows;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';    GOCREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (ListENER_PORT = 5022,ListENER_IP = ALL)        FOR DATA_MIRRORING         (RolE = PARTNER,AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserver,ENCRYPTION = required ALGORITHM AES)    GO    --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--  镜像(创建用户、还原证书、创建端点)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM windows;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';    GOCREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (ListENER_PORT = 5022,AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver,ENCRYPTION = required ALGORITHM AES)    GO    --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--  见证(创建用户、还原证书、创建端点)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM windows;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM file = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';    GOUSE master;    CREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (ListENER_PORT = 5022,ListENER_IP = ALL)        FOR DATA_MIRRORING         (RolE = WITnesS,AUTHENTICATION = CERTIFICATE Cert_kk_db3_mssqlserver,ENCRYPTION = required ALGORITHM AES)    GO --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--主体：备份数据库USE master;  BACKUP DATABASE [DemoDB]    TO disK = 'C:\Databases\DemoDB.BAK' WITH INIT,FORMAT    GO BACKUP LOG [DemoDB]    TO disK = 'C:\Databases\DemoDB_LOG.BAK' WITH INIT,FORMAT    GO --镜像：还原数据库（norECOVERY）USE master;  RESTORE DATABASE [DemoDB]FROM  disK = N'C:\Databases\DemoDB.BAK'   WITH  file = 1,MOVE N'DemoDB' TO N'C:\Databases\DemoDB.mdf',MOVE N'DemoDB_log' TO N'C:\Databases\DemoDB_log.ldf',NOUNLOAD,norECOVERY,STATS = 10  GO   RESTORE DATABASE [DemoDB]FROM disK = N'C:\Databases\DemoDB_LOG.BAK'   WITH norECOVERYGO --开始镜像 --在【镜像】执行,PARTNER为主服务器  USE [master]  GO  ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.10:5022';  GO    --在【主体】执行,PARTNER为镜像服务器  USE [master]  GO  ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.11:5022';  GO  ALTER DATABASE [DemoDB] SET WITnesS = 'TCP://192.168.2.12:5022'; GO--在主体执行:设置为高安全模式  ALTER DATABASE [DemoDB] SET SAFETY FulL  

EXEC [DemoDB].dbo.sp_changedbowner @loginame = N'sa',@map = falseGOEXEC master..sp_addsrvrolemember @loginame = N'KK\UserReplMirror',@rolename = N'sysadmin'GO

2. 配置分发服务器

登录到该服务器。

kk-db3

192.168.2.12

见证机（WITnesS）+ 分发

右键复制，配置分发。

分发服务器选择 “kk-db3” 本地服务器。添加 “KK-DB1 ” （主机）和  “KK-DB2 ” （镜像） 为发布服务器，否则连接不到该分发服务器。

注意：创建分发服务器时，要求数据管理密码。右键“复制”—“分发服务器属性”—“发布服务器”  可看到和设置。

配置用于故障转移的复制代理

可配置参数 PublisherFailoverPartner 的代理：
1 - 复制快照代理（对于所有发布）
2 - 复制日志读取器代理（对于所有事务发布）
4 - 复制合并代理（对于合并订阅）
9 - 复制队列读取器代理（对于支持排队更新订阅的事务发布）

当前为可更新订阅，只要更改1,2,9 就行（分发库执行）

--@parameter_value 为镜像服务exec sp_add_agent_parameter @profile_ID = 1,@parameter_name = N'-PublisherFailoverPartner',@parameter_value = N'kk-db2'exec sp_add_agent_parameter @profile_ID = 2,@parameter_value = N'kk-db2'exec sp_add_agent_parameter @profile_ID = 9,@parameter_value = N'kk-db2'

接着 重启sql Server 代理

3. 配置发布和订阅

登录到服务器

kk-db1

192.168.2.10

主机（Replication + Mirror）

创建本地发布，选择分发服务器。（只有分发中添加了发布服务器的信息，此处才能访问）

这里需要输入在分发服务器设置的管理密码。

@H_367_403@

注：此时在分发服务器（ 192.168.2.12 ）生成的复制相关作业代理，所有者改为 [sa]

登录到服务器

kk-db4

192.168.2.13

订阅

创建订阅：（发布选择 “kk-db1”）

创建完成订阅后，初始化订阅，登录到服务器：

kk-db1

192.168.2.10

主机（Replication + Mirror）

@H_117_502@

登录到服务器：

kk-db3

192.168.2.12

见证机（WITnesS）+ 分发

右键 “复制”—“启动复制监视器”，右键 “添加发布服务器”，将 发布服务器 “kk-db1” 添加，即可看到复制监控情况

配置已完成！~

4. 测试

现在把主体（Replication + Mirror）服务停止：

停止后，到原来的镜像（kk-db2）查看，镜像变成了主体，本地发布也出现了（原来镜像是不存在的）

但是，当我从现在的主体（kk-db2）插入数据时，数据并没有同步到订阅！~

难道是哪里配置不对？？！~~

网上查找后，发现是BUG，原因是：

This problem occurs because Log Reader Agent does not copy the transactions that are marked for replication from the transaction log into the distribution database. Log Reader Agent cannot read past a specific log sequence number (LSN). This specific LSN represents the last LSN that has been hardened to the transaction log of the mirror database.

FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database mirroring session in SQL Server 2005

暂时不修复，用其他方法设置也可以：

（先启动 kk-db1 的sqlserver 服务）

在主体 和 镜像中，设置服务启动参数，添加 -T1448，重启服务即可。

此时停止主体实例，镜像变成主体， *** 作数据可正常和订阅同步了！~

至此，就算完成了，个人测试，也可能有不对的地方。

总结

以上是内存溢出为你收集整理的SQLServer 复制和数据库镜像 详细配置部署全部内容，希望文章能够帮你解决SQLServer 复制和数据库镜像 详细配置部署所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。