同构或异构数据库数据实时同步

同构或异构数据库的数据实时同步同构或异构数据库的数据实时同步 目录目录 同构或异构数据库的数据实时同步 1 目录 1 一 术语解释 1 二 同构数据库的同步 1 1 建立发布服务器 2 2 新建订阅服务器 8 三 异构数据库的同步 24 1 MSDTC 的配置 24 2 创建链接服务器 30 3 创建触发器 34 一 一 术语解释 术语解释 同构 需要同步的两张表一模一样 异构 需要同步的两张表结构不一样 注 本文所说的数据同步两个数据库是部署在不同的服务器上 二 二 同构数据库的同步 同构数据库的同步 这里简单介绍一下 主要采用的 SQLServer2005 自带的数据复制技术 我们这里采 用数据快照的方式同步 复制中涉及到分布式服务器部署的概念 涉及到发布服务器 分发服务器以及订阅服务器 前提 因为需要用到 SQLSERVER 计划作业 所以需要开启 SQLSERVER 代理服务 点击运行键入 services msc 进入服务界面 启动 SQL Server Agent MSSQLSERVER 服务 并设置为自动启动 1 建立发布服务器建立发布服务器 1 连接进入连接进入 SQLSERVER 在根节点下打开复制 右击本 在根节点下打开复制 右击本 地发布 选择新建发布 地发布 选择新建发布 2 进入新建发布向导 选择下一步 进入新建发布向导 选择下一步 3 选择需进行数据同步的源数据库 按下一步 选择需进行数据同步的源数据库 按下一步 4 选择快照发布 按下一步 选择快照发布 按下一步 5 选择需要同步的表和列 按下一步 选择需要同步的表和列 按下一步 6 不需要筛选表行 直接按下一步 不需要筛选表行 直接按下一步 7 按图示选择 然后点击旁边的更改 按图示选择 然后点击旁边的更改 8 点击更改后进入设置时间间隔页面 按图示我们设置点击更改后进入设置时间间隔页面 按图示我们设置 作业执行时间间隔为作业执行时间间隔为 3 分钟 设置完毕后点击确定 然分钟 设置完毕后点击确定 然 后继续下一步 后继续下一步 9 在代理安全性下 选择安全设置 在代理安全性下 选择安全设置 10 如图所示 需要键入本机的如图所示 需要键入本机的 Windows 账号登录名账号登录名 密码以及密码以及 SqlServer 的用户名和密码 然后点击确定 继的用户名和密码 然后点击确定 继 续下一步 续下一步 11 按照图示勾选 生成脚本文件可选可不选 然后点按照图示勾选 生成脚本文件可选可不选 然后点 击下一步 击下一步 12 默认设置 继续下一步 默认设置 继续下一步 13 填写发布名称 然后点击完成填写发布名称 然后点击完成 14 然后开始自动创建发布 成功后点击关闭 然后开始自动创建发布 成功后点击关闭 15 然后再本地发布下出现了我们的发布然后再本地发布下出现了我们的发布 2 新建订阅服务器新建订阅服务器 1 打开复制 右击本地订阅 选择新建订阅 打开复制 右击本地订阅 选择新建订阅 2 点击下一步 选择发布服务器 点下一步点击下一步 选择发布服务器 点下一步 3 这里我们选择推送订阅 视具体情况而选择 然后点这里我们选择推送订阅 视具体情况而选择 然后点 击下一步击下一步 4 选择添加订阅服务器 添加选择添加订阅服务器 添加 SQL 订阅服务器订阅服务器 5 然后会弹出连接到服务器界面 连接到你为其同步数然后会弹出连接到服务器界面 连接到你为其同步数 据的服务器 配置好点击连接 据的服务器 配置好点击连接 注意这里需要输入机器注意这里需要输入机器 名 不能填写名 不能填写 IP 地址地址 6 然后会出现如下界面 选择需要向其同步数据的数据然后会出现如下界面 选择需要向其同步数据的数据 库 并按下一步库 并按下一步 7 这里需要建立连接 点击旁边有省略号的按钮这里需要建立连接 点击旁边有省略号的按钮 8 会出现如下界面 按图示进行选择 下方输入订阅服会出现如下界面 按图示进行选择 下方输入订阅服 务器的数据库名和密码 上方也可以选第一个选项 用务器的数据库名和密码 上方也可以选第一个选项 用 Windows 登录名和密码的方式连接 但由于我示例连接登录名和密码的方式连接 但由于我示例连接 的是虚拟机 的是虚拟机 root 用户的权限不够 在最后配置完成之用户的权限不够 在最后配置完成之 后会有错误提示 其实用机器名后会有错误提示 其实用机器名 Windows 用户名 下边用户名 下边 输入密码的方式也是可以的 我这里选的是第二个选项 输入密码的方式也是可以的 我这里选的是第二个选项 使用使用 SQLSERVER 代理的方式 然后点击确定 代理的方式 然后点击确定 9 直接点击下一步直接点击下一步 10 点击下一步点击下一步 11 点击下一步点击下一步 12 点击下一步点击下一步 13 点击下一步点击下一步 14 点击完成点击完成 15 出现如下界面 然后点击关闭出现如下界面 然后点击关闭 16 然后会在你的发布下出现一个订阅 同时在链接服然后会在你的发布下出现一个订阅 同时在链接服 务器里出现一个链接务器里出现一个链接 链接服务器 同时在远程服务器 也就是你的订阅服务器里的本地订阅里也会出现对服务器数 据的订阅 然后服务器数据库更新 计划会按时间间隔执行 执行时就会用数据 快照的方式同步订阅服务器的数据了 如图这是订阅服务器的本地订阅 需要注意的是 发布服务器的代理服务一定要开 如果订阅服务器的本地订阅里 看不到订阅的话多刷几次就好了 数据库的同步复制到此结束 三 三 异构数据库的同步异构数据库的同步 异构数据库同步用到的技术包括触发器 MSDTC RPC SQLSERVER 链接服务器 实现 原理是需同步数据的双方需要都开启 MSDTC 服务 并进行配置 MSDTC 的配置稍后会讲 到 因为 MSDTC 的通信依赖于 RPC 所以有防火墙的话双方都需要开启 135 端口 telnet 可以试试 135 端口是不是通的 两个数据库服务器应该都支持远程连接 在 SQLSERVER 的 配置管理器里可以进行配置 然后建立链接服务器 然后为服务器上需要同步的表分别建 立触发器 在触发器里进行 SQL 同步语句的编写 这样就实现了异构数据库的数据实时同 步 1 MSDTC 的配置的配置 1 双方启动双方启动 MSDTC 服务服务 MSDTC 服务提供分布式事务服务 如果要在数据库中使用分布式事务 必须在参与的 双方服务器启动 MSDTC Distributed Transaction Coordinator 服务 操作步骤 开始 设置 控制面板 管理工具 组件服务 打开组件服务节点 右击我的电脑 属性 选择 MSDTC 选项卡 点击安全配置 根据图片所示将必要项打上勾点击确定重启服务即可 2 打开双方打开双方 135 端口端口 MSDTC 服务依赖于 RPC Remote Procedure Call RPC 服务 RPC 使用 135 端口 保证 RPC 服务启动 如果服务器有防火墙 保证 135 端口不被防火墙挡住 使用 telnet IP 135 命令测试对方端口是否对外开放 也可用端口扫描软件 比如 Advanced Port Scanner 扫描端口以判断端口是否开放 操作 步骤 1 向防火添加 msdtc exe 点击添加程序 点击浏览 找到 system32 文件夹下的 msdtc exe 文件 点打开即可 步骤 2 添加 135 端口 点击添加端口 填入 135 点确定即可 步骤 3 SQL2005 设置 打开 SQL2005 企业管理器 右击根节点 打开服务器属性窗口将 需要将分布式事务用于服务器到服务器 的项勾上 即可 2 创建链接服务器创建链接服务器 1 打开本地数据库打开本地数据库 选择根节点选择根节点 服务器对象服务器对象 链接链接 服务器 右键单击链接服务器 选择新建链接服务器服务器 右键单击链接服务器 选择新建链接服务器 2 常规配置 链接服务器名字可以随便起 但是产品名常规配置 链接服务器名字可以随便起 但是产品名 称必须为大写的称必须为大写的 SQLSERVER 3 安全性配置 需要输入远程服务器的用户名和密码安全性配置 需要输入远程服务器的用户名和密码 4 服务器选项 服务器选项 Rpc 和和 Rpc Out 设置为设置为 true 然后点 然后点 击确定击确定 OK 3 创建触发器创建触发器 在创建触发器之前 可以先试试链接服务器是否成功建立 用 Select 语句搜 索尝试一下 如 select from ITSV testCopy1 dbo SY LatestSensorData 注意格式 链接服务器别名 数据库 dbo 表名 如果成功的话继续创建触发器 不成功的话检查原因 创建触发器语法 use expoxian go CREATE TRIGGER Tri LatestSensorData ON SY LatestSensorData FOR INSERT AS BEGIN Insert into CCTV testCopy1 dbo SY LatestSensorData f gatewayId f uploadTime f c ode1 f code2 f code3 f code4 f code5 f code6 SELECT f gatewayId f uploadTime f code1 f code2 f code3 f code4 f code5 f code6 from Inserted END 删除触发器语法 drop trigger Tri LatestSensorData 针对表