如何同步两个SQLServer数据库的内容?程序代码可以有版本管理CVS进行同步管理可是数据库同步就非常麻烦只能自己改了一个后再去改另一个如果忘记了更改另一个经常造成两个数据库的结构或内容上不一致各位有什么好的方法吗?
一分发与复制
用强制订阅实现数据库同步操作 大量和批量的数据可以用数据库的同步机制处理:
//
说明
为方便操作所有操作均在发布服务器(分发服务器)上操作并使用推模式
在客户机器使用强制订阅方式
有疑问联系作者zlp@hotmailcom
二测试通过
:环境
服务器环境:
机器名称 ZehuaDb
操作系统Windows Server
数据库版本SQL Server 个人版
客户端
机器名称Zlp
操作系统Windows Server
数据库版本SQL Server 个人版
:建用户帐号
在服务器端建立域用户帐号
我的电脑管理>本地用户和组>用户>建立
UserName:zlp
UserPwd:zlp
:重新启动服务器MSSQLServer
我的电脑>控制面版>管理工具>服务>MSSQLServer 服务
(更改为域用户帐号我们新建的zlp用户 \zlp密码:zlp)
:安装分发服务器
A:配置分发服务器
工具>复制>配置发布订阅服务器和分发>下一步>下一步(所有的均采用默认配置)
B:配置发布服务器
工具>复制>创建和管理发布>选择要发布的数据库(SZ)>下一步>快照发布>下一步>选择要发布的内容>下一步>下一步>下一步>完成
C:强制配置订阅服务器(推模式拉模式与此雷同)
工具>复制>配置发布订阅服务器和分发>订阅服务器>新建>SQL Server数据库>输入客户端服务器名称(ZLP)>使用SQL Server 身份验证(sa空密码)>确定>应用>确定
D:初始化订阅
复制监视器>发布服务器(ZEHUADB)>双击订阅>强制新建>下一步>选择启用的订阅服务器>ZLP>下一步>下一步>下一步>下一步>完成
:测试配置是否成功
复制监视器>发布服务器(ZEHUADB)>双击SZ:SZ>点状态>点立即运行代理程序
查看
复制监视器>发布服务器(ZEHUADB)>SZ:SZ>选择ZLP:SZ(类型强制)>鼠标右键>启动同步处理
如果没有错误标志(红色叉)恭喜您配置成功
:测试数据
在服务器执行:
选择一个表执行如下SQL
insert into WQ_NEWSGROUP_S select 测试成功
复制监视器>发布服务器(ZEHUADB)>SZ:SZ>快照>启动代理程序>ZLP:SZ(强制)>启动同步处理
去查看同步的 WQ_NEWSGROUP_S 是否插入了一条新的记录
测试完毕通过
:修改数据库的同步时间一般选择夜晚执行数据库同步处理
(具体操作略) :D
/*
注意说明
服务器一端不能以(local)进行数据的发布与分发需要先删除注册然后新建注册本地计算机名称
卸载方式工具>复制>禁止发布>是在ZehuaDb上静止发布卸载所有的数据库同步配置服务器
注意发布服务器分发服务器中的SQLServerAgent服务必须启动
采用推模式: D:\Microsoft SQL Server\MSSQL\REPLDATA\unc 目录文件可以不设置共享
拉模式:则需要共享~!
*/
少量数据库同步可以采用触发器实现同步单表即可
三配置过程中可能出现的问题
在SQL Server 里设置和使用数据库复制之前应先检查相关的几台SQL Server服务器下面几点是否满足
MSSQLserver和Sqlserveragent服务是否是以域用户身份启动并运行的（\administrator用户也是可以的）
如果登录用的是本地系统帐户local将不具备网络功能会产生以下错误:
进程未能连接到Distributor @Server name
(如果您的服务器已经用了SQL Server全文检索服务 请不要修改MSSQLserver和Sqlserveragent服务的local启动
会照成全文检索服务不能用请换另外一台机器来做SQL Server 里复制中的分发服务器)
修改服务启动的登录用户需要重新启动MSSQLserver和Sqlserveragent服务才能生效
检查相关的几台SQL Server服务器是否改过名称(需要srvid=的本地机器上srvname和datasource一样)
在查询分析器里执行:
use master
select srvidsrvnamedatasource from sysservers
如果没有srvid=或者srvid=（也就是本机器）但srvname和datasource不一样 需要按如下方法修改:
USE master
GO
设置两个变量
DECLARE @serverproperty_servername varchar()
@servername varchar()
取得Windows NT 服务器和与指定的 SQL Server 实例关联的实例信息
SELECT @serverproperty_servername = CONVERT(varchar() SERVERPROPERTY(ServerName))
返回运行 Microsoft SQL Server 的本地服务器名称
SELECT @servername = CONVERT(varchar() @@SERVERNAME)
显示获取的这两个参数
select @serverproperty_servername@servername
如果@serverproperty_servername和@servername不同(因为你改过计算机名字)再运行下面的
删除错误的服务器名
EXEC sp_dropserver @server=@servername
添加正确的服务器名
EXEC sp_addserver @server=@serverproperty_servername @local=local
修改这项参数需要重新启动MSSQLserver和Sqlserveragent服务才能生效
这样一来就不会在创建复制的过程中出现错误了
检查SQL Server企业管理器里面相关的几台SQL Server注册名是否和上面第二点里介绍的srvname一样
不能用IP地址的注册名
（我们可以删掉IP地址的注册新建以SQL Server管理员级别的用户注册的服务器名）
这样一来就不会在创建复制的过程中出现错误了
检查相关的几台SQL Server服务器网络是否能够正常访问
如果ping主机IP地址可以但ping主机名不通的时候需要在
winnt\system\drivers\etc\hosts (WIN)
windows\system\drivers\etc\hosts (WIN)
文件里写入数据库服务器IP地址和主机名的对应关系
例如
localhost
oracledb oracledb
fengyu fengyu
bj_db bj_db
或者在SQL Server客户端网络实用工具里建立别名例如:
系统需要的扩展存储过程是否存在(如果不存在需要恢复):
sp_addextendedproc xp_regenumvalues@dllname =xpstardll
go
sp_addextendedproc xp_regdeletevalue@dllname =xpstardll
go
sp_addextendedproc xp_regdeletekey@dllname =xpstardll
go
sp_addextendedproc xp_cmdshell @dllname =xplogdll
接下来就可以用SQL Server企业管理器里[复制]> 右键选择 >[配置发布订阅服务器和分发]的图形界面来配置数据库复制了
下面是按顺序列出配置复制的步骤:
建立发布和分发服务器
[欢迎使用配置发布和分发向导]>[选择分发服务器]>[使@servername成为它自己的分发服务器SQL Server将创建分发数据库和日志]
>[制定快照文件夹]> [自定义配置] > [否使用下列的默认配置] > [完成]
上述步骤完成后 会在当前@servername SQL Server数据库里建立了一个distribion库和一个distributor_admin管理员级别的用户(我们可以任意修改密码)
服务器上新增加了四个作业:
[ 代理程序历史记录清除: distribution ]
[ 分发清除: distribution ]
[ 复制代理程序检查 ]
[ 重新初始化存在数据验证失败的订阅 ]
SQL Server企业管理器里多了一个复制监视器 当前的这台机器就可以发布分发订阅了
我们再次在SQL Server企业管理器里[复制]> 右键选择 >[配置发布订阅服务器和分发]
我们可以在 [发布服务器和分发服务器的属性] 窗口> [发布服务器] > [新增] > [确定]> [发布数据库] > [事务]/[合并] > [确定] > [订阅服务器] > [新增] > [确定]
把网络上的其它SQL Server服务器添加成为发布或者订阅服务器
新增一台发布服务器的选项
我这里新建立的JIN发布服务器是用管理员级别的数据库用户test连接的
到发布服务器的管理链接要输入密码的可选框 默认的是选中的
在新建的JIN发布服务器上建立和分发服务器FENGYU/FENGYU的链接的时需要输入distributor_admin用户的密码到发布服务器的管理链接要输入密码的可选框也可以不选也就是不需要密码来建立发布到分发服务器的链接(这当然欠缺安全在测试环境下可以使用)
新建立的网络上另一台发布服务器(例如JIN)选择分发服务器
[欢迎使用配置发布和分发向导]>[选择分发服务器]
> 使用下列服务器(选定的服务器必须已配置为分发服务器) > [选定服务器](例如FENGYU/FENGYU)
> [下一步] > [输入分发服务器（例如FENGYU/FENGYU）的distributor_admin用户的密码两次]
> [下一步] > [自定义配置] > [否使用下列的默认配置]
> [下一步] > [完成] > [确定]
建立一个数据库复制发布的过程:
[复制] > [发布内容] > 右键选择 > [新建发布]
> [下一步] > [选择发布数据库] > [选中一个待发布的数据库]
> [下一步] > [选择发布类型] > [事务发布]/[合并发布]
> [下一步] > [指定订阅服务器的类型] > [运行SQL Server 的服务器]
> [下一步] > [指定项目] > [在事务发布中只可以发布带主键的表] > [选中一个有主键的待发布的表]
>[在合并发布中会给表增加唯一性索引和 ROWGUIDCOL 属性的唯一标识符字段[rowguid]默认值是newid()]
(添加新列将: 导致不带列列表的 INSERT 语句失败增加表的大小增加生成第一个快照所要求的时间)
>[选中一个待发布的表]
> [下一步] > [选择发布名称和描述] >
> [下一步] > [自定义发布的属性] > [否根据指定方式创建发布]
> [下一步] > [完成] > [关闭]
发布属性里有很多有用的选项设定订阅到期(例如小时)
设定发布表的项目属性:
常规窗口可以指定发布目的表的名称可以跟原来的表名称不一样
下图是命令和快照窗口的栏目
( SQL Server 数据库复制技术实际上是用insertupdatedelete操作在订阅服务器上重做发布服务器上的事务操作
看文档资料需要把发布数据库设成完全恢复模式事务才不会丢失
但我自己在测试中发现发布数据库是简单恢复模式下每秒生成一些大事务分钟后再收缩数据库日志
这期间发布和订阅服务器上的作业都暂停暂停恢复后并没有丢失任何事务更改 )
发布表可以做数据筛选例如只选择表里面的部分列:
例如只选择表里某些符合条件的记录 我们可以手工编写筛选的SQL语句:
发布表的订阅选项并可以建立强制订阅:
成功建立了发布以后发布服务器上新增加了一个作业: [ 失效订阅清除 ]
分发服务器上新增加了两个作业:
[ JINdackdack ] 类型[ REPL快照 ]
[ JINdack ] 类型[ REPL日志读取器 ]
上面蓝色字的名称会根据发布服务器名发布名及第几次发布而使用不同的编号
REPL快照作业是SQL Server复制的前提条件它会先把发布的表结构数据索引约束等生成到发布服务器的OS目录下文件
(当有订阅的时候才会生成 当订阅请求初始化或者按照某个时间表调度生成)
REPL日志读取器在事务复制的时候是一直处于运行状态(在合并复制的时候可以根据调度的时间表来运行)
建立一个数据库复制订阅的过程:
[复制] > [订阅] > 右键选择 > [新建请求订阅]
> [下一步] > [查找发布] > [查看已注册服务器所做的发布]
> [下一步] > [选择发布] > [选中已经建立发布服务器上的数据库发布名]
> [下一步] > [指定同步代理程序登录] > [当代理程序连接到代理服务器时:使用SQL Server身份验证]
(输入发布服务器上distributor_admin用户名和密码)
> [下一步] > [选择目的数据库] > [选择在其中创建订阅的数据库名]/[也可以新建一个库名]
> [下一步] > [允许匿名订阅] > [是生成匿名订阅]
> [下一步] > [初始化订阅] > [是初始化架构和数据]
> [下一步] > [快照传送] > [使用该发布的默认快照文件夹中的快照文件]
(订阅服务器要能访问发布服务器的REPLDATA文件夹如果有问题可以手工设置网络共享及共享权限)
> [下一步] > [快照传送] > [使用该发布的默认快照文件夹中的快照文件]
> [下一步] > [设置分发代理程序调度] > [使用下列调度] > [更改] > [例如每五分钟调度一次]
> [下一步] > [启动要求的服务] > [该订阅要求在发布服务器上运行SQLServerAgent服务]
> [下一步] > [完成] > [确定]
成功建立了订阅后订阅服务器上新增加了一个类别是[REPL分发]作业(合并复制的时候类别是[REPL合并])
它会按照我们给的时间调度表运行数据库同步复制的作业
SQL Server复制配置好后 可能出现异常情况的实验日志:
发布服务器断网sql server服务关闭重启动关机的时候对已经设置好的复制没有多大影响
中断期间分发和订阅都接收到没有复制的事务信息
分发服务器断网sql server服务关闭重启动关机的时候对已经设置好的复制有一些影响
中断期间发布服务器的事务排队堆积起来
(如果设置了较长时间才删除过期订阅的选项 繁忙发布数据库的事务日志可能会较快速膨胀)
订阅服务器会因为访问不到发布服务器反复重试
我们可以设置重试次数和重试的时间间隔(最大的重试次数是 如果每分钟重试一次可以支持约天不出错)
分发服务器sql server服务启动网络接通以后发布服务器上的堆积作业将按时间顺序作用到订阅机器上:
会需要一个比较长的时间(实际上是生成所有事务的insertupdatedelete语句在订阅服务器上去执行)
我们在普通的PC机上实验的个事务个命令执行花了分秒
订阅服务器断网sql server服务关闭重启动关机的时候对已经设置好的复制影响比较大可能需要重新初试化
我们实验环境(订阅服务器)从:分意外停机以 第二天:分重启动后 已经设好的复制在:分以后又开始正常运行了 发布服务器上的堆积作业将按时间顺序作用到订阅机器上 但复制管理器里出现快照的错误提示 快照可能需要重新初试化复制可能需要重新启动(我们实验环境的机器并没有进行快照初试化复制仍然是成功运行的)
删除已经建好的发布和定阅可以直接用delete删除按钮
我们最好总是按先删定阅再删发布最后禁用发布的顺序来操作
如果要彻底删去SQL Server上面的复制设置 可以这样操作:
[复制] > 右键选择 [禁用发布] > [欢迎使用禁用发布和分发向导]
> [下一步] > [禁用发布] > [要在@servername上禁用发布]
> [下一步] > [完成禁用发布和分发向导] > [完成]
我们也可以用TSQL命令来完成复制中发布及订阅的创建和删除 选中已经设好的发布和订阅 按属标右键可以[生成SQL脚本](这里就不详细讲了 后面推荐的网站内有比较详细的内容)
当你试图删除或者变更一个table时出现以下错误
Server: Msg Level State Line
Cannot drop the table object_name because it is being used for replication
比较典型的情况是该table曾经用于复制但是后来又删除了复制
处理办法
select * from sysobjects where replinfo >
sp_configure allow updates
go
reconfigure with override
go
begin transaction
update sysobjects set replinfo = where replinfo >
commit transaction
go
rollback transaction
go
sp_configure allow updates
go
reconfigure with override
go
疑问:
在合并复制配置完全后如果同步代理停止了我要在程序中去重新启动合并复制的同步代理请问使用什么命令或存储过程呢？
解决办法:
sp_start_job
指示 SQL Server 代理程序立即执行作业
示例
下例启动名为 Nightly Backup 的作业
USE msdb
EXEC sp_start_job @job_name = Nightly Backup
日志还原功能
/*说明
下面的代码演示了如何利用日志还原功能将主数据库中的数据变化及时反馈到备用数据库中
备用数据库的数据可以随时用于查询但不能被更新（备用数据库只读）
*/
首先创建一个演示用的数据库(主数据库)
CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA
FILENAME = c:\Db_testmdf )
LOG ON
( NAME = Db_test_LOG
FILENAME = c:\Db_testldf)
GO
对数据库进行备份
BACKUP DATABASE Db_test TO DISK=c:\test_databak WITH FORMAT
GO
把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步)
RESTORE DATABASE Db_test_bak FROM DISK=c:\test_databak
WITH REPLACESTANDBY=c:\db_test_bakldf
MOVE Db_test_DATA TO c:\Db_test_datamdf
MOVE Db_test_LOG TO c:\Db_test_logldf
GO
启动 SQL Agent 服务
EXEC masterxp_cmdshell net start sqlserveragentno_output
GO
创建主服务器数据训与备用服务器数据库之间同步的作业
DECLARE @jogid uniqueidentifier
EXEC msdbsp_add_job
@job_id = @jogid OUTPUT
@job_name = N数据同步处理
创建同步处理步骤
EXEC msdbsp_add_jobstep
@job_id = @jogid
@step_name = N数据同步
@subsystem = TSQL
@command = N
主数据库中进行日志备份
BACKUP LOG Db_test TO DISK=c:\test_logbak WITH FORMAT
备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化
实际应该时主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立并且备份文件应该放在主服务器和备用都能访问的共享目录中
RESTORE LOG Db_test_bak FROM DISK=c:\test_logbak WITH STANDBY=c:\test_logldf
@retry_attempts =
@retry_interval =
创建调度(每分钟执行一次)
EXEC msdbsp_add_jobschedule
@job_id = @jogid
@name = N时间安排
@freq_type=
@freq_interval=
@freq_subday_type=x
@freq_subday_interval=
@freq_recurrence_factor=
添加目标服务器
EXEC msdbdbosp_add_jobserver
@job_id = @jogid
@server_name = N(local)
GO
通过上述处理主数据库与备用数据库之间的同步关系已经设置完成
下面开始测试是否能实现同步
在主数据库中创建一个测试用的表
CREATE TABLE Db_testdboTB_test(ID int)
GO
等待分钟秒（由于同步的时间间隔设置为分钟所以要延时才能看到效果）
WAITFOR DELAY ::
GO
查询一下备用数据库看看同步是否成功
SELECT * FROM Db_test_bakdboTB_test
/*结果:
ID
（所影响的行数为 行）
*/
测试成功
GO
最后删除所有的测试
DROP DATABASE Db_testDb_test_bak
EXEC msdbsp_delete_job @job_name=N数据同步处理
GO
/*===========================================================*/
/*服务器档机处理说明
使用这种方式建立的数据库同步当主数据库不可用时（例如主数据库损坏或者停机检修）
可以使用以下两种方法使备用数据库可用
*/
如果主数据库损坏无法备份出最新的日志可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）
RESTORE LOG Db_test_bak WITH RECOVERY
如果主数据库可以备份出最新日志则可以使用下面的语句
先备份主数据库的最新的事务日志
BACKUP LOG Db_test TO DISK=c:\test_logbak WITH FORMAT
再在备用数据库中恢复最新的事务日志并且使备用数据库可读写（升级为主数据库）
RESTORE LOG Db_test_bak FROM DISK=c:\test_logbak
简单地说:
你的sql服务要使用指定的windows用户登陆 而不能使用本地系统帐户
用于登陆sql服务的用户要求对共享目录具有所有权限
如果你的电脑没有加入到域 还必须保证源和目标服务器的sql服务设置的登陆用户是一样的(用户名和密码都一样)
网络备份主要是权限设置问题 参考下面的备份文件共享目录权限设置方法去解决目录的共享权限就可以了
下面假设是假设A服务器上的数据库备份到B服务器上的共享目录权限设置(两台服务器应该在局域网内允许目录共享访问)::
机器AB创建一个同名的windows用户用户组设置为administrators并设置相同的密码做为备份文件夹文件夹的有效访问用户操作:
我的电脑控制面板管理工具计算机管理用户和组右键用户新建用户建立一个隶属于administrator组的登陆windows的用户
在B机器器上新建一个共享目录做为备份文件的存放目录操作:
我的电脑D:\ 新建一个目录名为: BAK右键这个新建的目录属性共享选择共享该文件夹通过权限按纽来设置具体的用户权限保证第一步中创建的用户具有对该文件夹的所有权限确定
设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户
开始程序管理工具服务右键 MSSQLSERVER属性登陆选择此账户输入或者选择第一步中创建的windows登录用户名密码中输入该用户的密码确定同样的方法设置 SQLSERVERAGENT
在A机器上完成对B机器BAK目录的映射
查询分析器中执行下面的语句检验是否成功:
exec masterxp_cmdshell dir 映射的盘符
A服务器上做备份计划
备注:创建一个新的用户只是为了让MSSQLSERVER服务的启动帐户与共享目录的有效访问同名且密码相同这样才能通过验证(所以你也可以用其他有效的用户来代替只需要满足用户名和密码相同并且拥有足够的权限)
