SQL SERVER发布与订阅

时间：2010-08-30 来源：wenbing

1、发布与订阅
用强制订阅实现数据库同步操作
大量和批量的数据可以用数据库的同步机制处理:
//
说明：
发布服务器(分发)操作,订阅服务器(订阅)操作
在客户机器使用强制订阅方式。
测试通过
//
--1:环境
服务器环境( 发布):
机器名称：   devserver
操作系统：Windows   2003   Server
数据库版本：SQL   2005 Server   企业版
客户端 (订阅):
机器名称：pkit-bingxu
操作系统：Windows   2003   Server
数据库版本：SQL   2005 Server   企业版
--2:建用户帐号
在服务器端建立域用户帐号
我的电脑管理->本地用户和组->用户->建立
UserName:dbrepluser
UserPwd:dbrepluser
   隶属于：SQLServer2005MSSQLUser$devserver$MSSQLSERVER
                   SQLServer2005MSSQLAgentUser$devserver$MSSQLSERVER

--3:重新启动服务器MSSQLServer
我的电脑->控制面版->管理工具->服务->MSSQLServer   服务
(更改为：域用户帐号,我们新建的dbrepluser用户   .\dbrepluser,密码:dbrepluser)

在发布服务器和订阅服务器上互相注册
企业管理器
--右键SQL Server组
--新建SQL Server注册...
--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加
--下一步--连接使用,选择第二个"SQL Server身份验证"
--下一步--输入用户名和密码
--下一步--选择SQL Server组,也可以创建一个新组
--下一步--完成

--4:安装发布服务器
A:配置发布服务器
复制->本地发布->创建和管理发布->选择要发布的数据库(SZ)->下一步->快照发布->下一步->
选择要发布的内容->下一步->下一步->下一步->完成
B:强制配置订阅服务器(请求模式,推模式与此雷同)
复制->本地订阅->新建订阅->订阅服务器->新建->SQL   Server数据库->输入客户端服务器名称(ZLP)->使用SQL   Server   身份验证(sa,空密码)->确定->应用->确定
C:初始化订阅
复制监视器->发布服务器(ZEHUADB)->双击订阅->强制新建->下一步->选择启用的订阅服务器->ZLP->
下一步->下一步->下一步->下一步->完成
--5:测试配置是否成功
复制监视器->发布服务器(ZEHUADB)->双击SZ:SZ->点状态->点立即运行代理程序
查看：
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->选择ZLP:SZ(类型强制)->鼠标右键->启动同步处理
如果没有错误标志(红色叉)，恭喜您配置成功
--6:测试数据
--在服务器执行:
选择一个表，执行如下SQL
insert   into   WQ_NEWSGROUP_S   select   '测试成功',5
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->快照->启动代理程序
                                                                              ->ZLP:SZ(强制)->启动同步处理
去查看同步的   WQ_NEWSGROUP_S   是否插入了一条新的记录
测试完毕，通过。
--7修改数据库的同步时间,一般选择夜晚执行数据库同步处理
(具体操作略)   :D
/*
注意说明：
服务器一端不能以(local)进行数据的发布与分发,需要先删除注册，然后新建注册本地计算机名称
特别注意：配制请求定阅的时候必须在订阅服务器上注册发布服务器
卸载方式：工具->复制->禁止发布->是在"ZehuaDb"上静止发布,卸载所有的数据库同步配置服务器
注意：发布服务器、分发服务器中的SQLServerAgent服务必须启动
              采用推模式:   "D:\Microsoft   SQL   Server\MSSQL\REPLDATA\unc"   目录文件可以不设置共享
              请求模式:则需要共享~!
*/
少量数据库同步可以采用触发器实现,同步单表即可：
========================================================
========================================================
在SQL   Server   2000里设置和使用数据库复制之前，应先检查相关的几台SQL   Server服务器下面几点是否满足：
          1、MSSQLserver和Sqlserveragent服务是否是以域用户身份启动并运行的（.\administrator用户也是可以的）
                  如果登录用的是本地系统帐户local，将不具备网络功能，会产生以下错误:
                  进程未能连接到Distributor   '@Server   name'
              (如果您的服务器已经用了SQL   Server全文检索服务,   请不要修改MSSQLserver和Sqlserveragent服务的local启动。
                会照成全文检索服务不能用。请换另外一台机器来做SQL   Server   2000里复制中的分发服务器。)
              修改服务启动的登录用户，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
          2、检查相关的几台SQL   Server服务器是否改过名称(需要srvid=0的本地机器上srvname和datasource一样)
                在查询分析器里执行:
                    use   master
                    select   srvid,srvname,datasource   from   sysservers
                如果没有srvid=0或者srvid=0（也就是本机器）但srvname和datasource不一样,   需要按如下方法修改:
                    USE   master
                  GO
                  --   设置两个变量
                  DECLARE   @serverproperty_servername     varchar(100),
                                  @servername         varchar(100)
                  --   取得Windows   NT   服务器和与指定的   SQL   Server   实例关联的实例信息
                  SELECT   @serverproperty_servername   =   CONVERT(varchar(100),   SERVERPROPERTY('ServerName'))
                  --   返回运行   Microsoft   SQL   Server   的本地服务器名称
                  SELECT   @servername   =   CONVERT(varchar(100),   @@SERVERNAME)
                  --   显示获取的这两个参数
                  select   @serverproperty_servername,@servername
                  --如果@serverproperty_servername和@servername不同(因为你改过计算机名字),再运行下面的
                  --删除错误的服务器名
                  EXEC   sp_dropserver   @server=@servername
                  --添加正确的服务器名
                  EXEC   sp_addserver   @server=@serverproperty_servername,   @local='local'
                修改这项参数，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
                这样一来就不会在创建复制的过程中出现18482、18483错误了。
          3、检查SQL   Server企业管理器里面相关的几台SQL   Server注册名是否和上面第二点里介绍的srvname一样
                  不能用IP地址的注册名。
              （我们可以删掉IP地址的注册，新建以SQL   Server管理员级别的用户注册的服务器名）
                这样一来就不会在创建复制的过程中出现14010、20084、18456、18482、18483错误了。
          4、检查相关的几台SQL   Server服务器网络是否能够正常访问
                  如果ping主机IP地址可以，但ping主机名不通的时候，需要在
                          winnt\system32\drivers\etc\hosts       (WIN2000)
                          windows\system32\drivers\etc\hosts   (WIN2003)
                                文件里写入数据库服务器IP地址和主机名的对应关系。
                        例如：
                        127.0.0.1               localhost
                        192.168.0.35         oracledb         oracledb
                        192.168.0.65         fengyu02         fengyu02
                        202.84.10.193       bj_db               bj_db
                          或者在SQL   Server客户端网络实用工具里建立别名，例如:
              5、系统需要的扩展存储过程是否存在(如果不存在，需要恢复):
                    sp_addextendedproc   'xp_regenumvalues',@dllname   ='xpstar.dll'
                    go
                    sp_addextendedproc   'xp_regdeletevalue',@dllname   ='xpstar.dll'
                    go
                    sp_addextendedproc   'xp_regdeletekey',@dllname   ='xpstar.dll'
                    go
                    sp_addextendedproc   xp_cmdshell   ,@dllname   ='xplog70.dll'
   注：对发布服务器增加新的表时，只要在发布服务器上启用复制监控器-> 右击订阅服务器->从新初始化订阅

----------------------------------------------------------------------------------------------------

一个手工同步的方案

--定时同步服务器上的数据
--例子:
--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--服务器上的表(查询分析器连接到服务器上创建)
create table [user](id int primary key,number varchar(4),name varchar(10))
go

--以下在局域网(本机操作)
--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO

create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go

--创建触发器,维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go

--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go

exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
go

--创建同步处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_synchro]
GO

create proc p_synchro
as
--set XACT_ABORT on

--启动远程服务器的MSDTC服务
--exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output

--启动本机的MSDTC服务
--exec master..xp_cmdshell 'net start msdtc',no_output
--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGIN DISTRIBUTED TRANSACTION

--同步删除的数据
delete from srv_lnk.test.dbo.[user]
where id not in(select id from [user])

--同步新增的数据
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null

--同步修改的数据
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1

--同步后更新本机的标志
update [user] set state=0 where isnull(state,1)=1
--COMMIT TRAN
go

--创建作业,定时执行数据同步的存储过程
if exists(SELECT 1 from msdb..sysjobs where name='数据处理')
EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理'
exec msdb..sp_add_job @job_name='数据处理'

--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_synchro' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名
exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔

--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 00000 --0点执行
go

本文来自CSDN博客，转载请标明出处：file:///G:/彭华斌/学习/SQL%20SERVER发布与订阅%20-%20bingtingabc的专栏%20-%20CSDN博客.mht