http://lwl0606.cmszs.com/archives/ms-sql-server-2005duplicate.html
创建数据库复制
*注意: 下面例子是分发服务器和发布服务器放在同一台机器上
*别名设置, 别名的设置尤其重要, 一定要设置好别名, 复制才能配置成功.
前言: MSSQL2005使用已越来越广泛, 其中的复制功能也是倍受关注, 下文是我实际开发使用中总结的一些经验, 希望与大家分享!
准备:
为方便管理参与复制的数据, 可以远程监控复制服务情况, 必须给要监控的数据库添加别名.
应为用ip,port这种方式登录是不能正常监控数据库复制情况的. 添加别名的方法是:
选择 程序->miscrosoft server 2005->配置工具->SQL SERVER配置管理器
在SQL Native Client配置的别名中增添要监控的机器, 其中主数据库的别名是一定要添加的, 从数据库只要添加要监控的数据库即可
如127.0.0.70(机器名是server1) 1433 则分别填写如下:
别名:server1
端口:1433
服务器:127.0.0.70
协议:tcp/ip
点击确定.
添加别名成功后, 则直接用别名就可登录远程数据库了, 如上例, 直接用server1再输入帐号密码就可以正常登录了.
由于复制过程中只能使用机器名, 不能使用ip和端口号, 所以别名的设置尤其重要, 一定要设置好别名, 复制才能配置成功.
1.创建window帐号
在发布服务器server1建立以下window用户
SnapshotAgent
LogReadAgent
DistributeAgent
2.为快照目录添加对应权限，假设快照目录为d:\DB_BACKUP\snapshot_data
为SnapshotAgent添加对d:\DB_BACKUP\snapshot_data的读,写权限
若使用FTP传输快照, 请打开FTP服务器, 并指定SnapshotAgent用户登录的目录为快照目录d:\DB_BACKUP\snapshot_data, 并开启读写权限.
3.配置分发服务器
在sql server management studio中选择服务器,选择”复制”–>”配置分发”
选择自己充当分发服务器
设置快照文件夹, 若要使用请求订阅, 快照文件夹必须为网络地址; 推送订阅不需要
设置分发数据库及其路径, 假设分发数据库为distribution
生成配置分发
4.为发布数据库设置用户权限
为发布数据库实例设置以下登录名(使用window帐号登录)
server1\SnapshotAgent
server1\LogReadAgent
server1\DistributeAgent
为server1\SnapshotAgent, server1\LogReadAgent, server1\DistributeAgent设置分发数据库distribution的db_owner权限
为server1\SnapshotAgent, server1\LogReadAgent, server1\DistributeAgent设置发布数据库cms2的db_owner权限
5.设置发布数据库
在sql server management studio中选择服务器,选择”复制”–>”本地发布”–>”新建发布”
选择要发布的数据库cms2
选择事务性发布
选择要复制的表,试图和存储过程
选择创建快照(若不用快照初始化, 可不选)
为快照代理设置window用户server1\SnapshotAgent
为日志读取器代理设置window用户server1\LogReadAgent
创建发布
*保证DistributeAgent在发布服务器的发布访问列表中, 在”复制”–>”本地发布”–>”[cms2]:cms2″属性–>发布访问列表
*若使用快照初始化,必须先完成初始化快照才能开始订阅
*若要使用ftp传输快照, 在”复制”–>”本地发布”–>”[cms2]:cms2″属性–>FTP快照, 选择允许快照从FTP下载, 并使用SnapshotAgent登录
6.创建订阅数据库及设置相关权限
在订阅服务器中创建订阅数据库, 必须与发布数据库的名称保持一致, 为cms2(可以在这里就用server1的数据库备份初始化订阅数据库)
为订阅数据库实例设置以下登录名(使用SQL SERVER帐号登录)
SubscriberAgent
为SubscriberAgent设置订阅数据库cms2的db_owner权限
7.设置订阅数据库
在sql server management studio中选择服务器,选择”复制”–>”本地订阅”–>”新建订阅”
选择发布服务器及其发布
选择”推送订阅”(一定要推送订阅, 请求订阅会消耗大量资源)
选择订阅数据库
为分发代理设置window用户server1\DistributeAgent(没有看错,一定要是server1)
为连接到订阅服务器的SQL SERVER帐号设为SubscriberAgent
选择同步计划为”连续运行”
选择”初始化”并设为”立即”(若前面已用备份初始化订阅数据库则不用初始化)
创建订阅
8.检查
在sql server management studio中选择发布服务器,选择”复制”–>”本地发布”–>”[cms2]:cms2″属性–>启动复制监视器
看看是否一切正常, 没有报错
若一切ok在发布数据库的表中修改或新增数据, 在订阅数据库中就能看到对应表也做了相对的修改或新增的数据.
9.注意/优化
a.为帮助减少用户活动与复制代理活动之间的争用，请为发布和订阅数据库设置下列选项：
ALTER DATABASE 数据库名
SET READ_COMMITTED_SNAPSHOT ON
b.发布时只选择要同步的数据, 不要什么都复制
c.订阅的方式, 是推送还是请求要根据实际应用情况决定
d.对有INDENTITY设置的表(如主键设置了自增长的表), 必须在表的主键属性中设置not for replication.
e.一定要注意初始化时保证源数据库和目标数据库的数据一致性, 否则复制开始后会有很多报错.
10.跳过错误的过程
a.手动操作
在发布服务器的分发数据库上执行下面sql:
exec sp_helpsubscriptionerrors ‘发布服务器别名’
, ‘发布数据库’
, ‘发布复制名’
, ‘订阅服务器别名’
, ‘订阅数据库’;
获取最后时间的xact_seqno, 如0×00010471000001F1001900000000
在订阅服务器的订阅数据库上执行下面sql:
exec sp_setsubscriptionxactseqno ‘发布服务器别名’
, ‘发布数据库’
, ‘发布复制名’
, 0×00010471000001F1001900000000
跳过最后一个错误的过程, 直接执行下面的过程
具体的存储过程格式:
sp_helpsubscriptionerrors [ @publisher = ] ‘publisher’
, [ @publisher_db = ] ‘publisher_db’
, [ @publication = ] ‘publication’
, [ @subscriber = ] ’subscriber’
, [ @subscriber_db = ] ’subscriber_db’
sp_setsubscriptionxactseqno [ @publisher = ] ‘publisher’
, [ @publisher_db = ] ‘publisher_db’
, [ @publication = ] ‘publication’
, [ @xact_seqno = ] xact_seqno
b.修改复制代理配置文件,自动跳过指定错误
在“分发服务器属性 - <分发服务器>”对话框的“常规”页上，单击“默认配置文件”。
选择分发代理 - 点击右边new按钮,
选择”已默认代理配置文件”为蓝本, 在”-SkipErrors”栏上加入要跳过的错误编号(多个用:号分割),
如自动跳过”应用复制的命令时在订阅服务器上找不到该行(Source: MSSQLServer, Error number: 20598)”
2601:2627:20598
11.对发布数据库中的表的新增或删除后, 一般要进行一次reinitial行为, 这种操作最好放在负载较低的晚上执行.
12.服务器的机器名一定要在安装SQLSERVER2005前设定好,
否则安装SQLSERVER2005时系统机器名修改了, SQL的对应机器名是很难修改的.
记住只要删除对应数据库引擎并重新安装即可, 不用全部删除重新安装.