sqlserver 2000数据库同步 同步两个SQLServer数据库的内容

为什么要同步SQL Server 2000 数据库,它都用在什么场合 SQL Server 2000
数据库同步配置的原理 从0开始一步一步配置SQL Server 2000
数据库同步,非常细 已经非常熟练,可以看精品版SQL Server 2000
数据库同步配置 配置SQL Server 2000 数据库同步时的 常见问题
为什么要同步SQL Server 2000
数据库,它都用在什么场合数据实时备份同步,数据库服务器出问题时我们也有其正常工作时的备份
数据实时备份同步,一台服务器负载不起时,可以用来做负载均衡
数据实时备份同步,数据库服务器可以无间断,无损失迁移
主服务器被攻击或当机时另一台服务同步机可以应急
。。。。。可以说好处非常多的。暂时写这么多 SQL Server 2000
数据库同步配置的原理

复制前要做好的准备工作:
1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
我的电脑 控制面板 管理工具 计算机管理 用户和组 右键用户 新建用户
建立一个隶属于administrator组的登陆windows的用户
2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
我的电脑 D:\ 新建一个目录,名为: PUB 右键这个新建的目录 属性 共享
选择”共享该文件夹”
通过”权限”按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
确定
3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
开始 程序 管理工具 服务 右键SQLSERVERAGENT 属性 登陆 选择”此账户”
输入或者选择第一步中创建的windows登录用户名 “密码”中输入该用户的密码
4.设置SQL
Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
企业管理器 右键SQL实例 属性 安全性 身份验证 选择”SQL Server 和 Windows”
确定 5.在发布服务器和订阅服务器上互相注册 企业管理器 右键SQL Server组
新建SQL Server注册… 下一步 可用的服务器中,输入你要注册的远程服务器名
添加 下一步 连接使用,选择第二个”SQL Server身份验证” 下一步
输入用户名和密码 下一步 选择SQL Server组,也可以创建一个新组 下一步 完成
6.对于只能用IP,不能用计算机名的,为其注册服务器别名
(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
开始 程序 Microsoft SQL Server 客户端网络实用工具 别名 添加
网络库选择”tcp/ip” 服务器别名输入SQL服务器名 连接参数
服务器名称中输入SQL服务器ip地址
如果你修改了SQL的端口,取消选择”动态决定端口”,并输入对应的端口号
SQL数据库同步复制 一、建立发布和分发服务器
[欢迎使用配置发布和分发向导]-[选择分发服务器]
-[使”@servername”成为它自己的分发服务器,SQL
Server将创建分发数据库和日志] -[制定快照文件夹]- [自定义配置] –
[否,使用下列的默认配置] – [完成] 上述步骤完成后,
会在当前”@servername” SQL Server数据库里建立了一个distribion库和
一个distributor_admin管理员级别的用户(我们可以任意修改密码)
服务器上新增加了四个作业: [ 代理程序历史记录清除: distribution ] [
分发清除: distribution ] [ 复制代理程序检查 ] [
重新初始化存在数据验证失败的订阅 ] SQL
Server企业管理器里多了一个复制监视器,
当前的这台机器就可以发布、分发、订阅了。 我们再次在SQL
Server企业管理器里[复制]- 右键选择
-[配置发布、订阅服务器和分发],可以看到类似下图: 我们可以在
[发布服务器和分发服务器的属性] 窗口 – [发布服务器] – [新增] –
[确定] – [发布数据库] – [事务]/[合并] – [确定] –
[订阅服务器] – [新增] – [确定] 把网络上的其它SQL
Server服务器添加成为发布或者订阅服务器. 新增一台发布服务器的选项:
我这里新建立的JIN001发布服务器是用管理员级别的数据库用户test连接的,
到发布服务器的管理链接要输入密码的可选框, 默认的是选中的,
在新建的JIN001发布服务器上建立和分发服务器FENGYU/FENGYU的链接的时需要输入distributor_admin用户的密码
到发布服务器的管理链接要输入密码的可选框,也可以不选,
也就是不需要密码来建立发布到分发服务器的链接(这当然欠缺安全,在测试环境下可以使用)
二、新建立的网络上另一台发布服务器(例如JIN001)选择分发服务器
[欢迎使用配置发布和分发向导]-[选择分发服务器] –
使用下列服务器(选定的服务器必须已配置为分发服务器) –
[选定服务器](例如FENGYU/FENGYU) – [下一步] –
[输入分发服务器的distributor_admin用户的密码两次] – [下一步] –
[自定义配置] – [否,使用下列的默认配置] – [下一步] – [完成] –
[确定] 建立一个数据库复制发布的过程: [复制] – [发布内容] –
右键选择 – [新建发布] – [下一步] – [选择发布数据库] –
[选中一个待发布的数据库] – [下一步] – [选择发布类型] –
[事务发布]/[合并发布] – [下一步] – [指定订阅服务器的类型] –
[运行SQL Server 2000的服务器] – [下一步] – [指定项目] –
[在事务发布中只可以发布带主键的表] – [选中一个有主键的待发布的表]
-[在合并发布中会给表增加唯一性索引和 ROWGUIDCOL
属性的唯一标识符字段[rowguid],默认值是newid()] (添加新列将:
导致不带列列表的 INSERT
语句失败,增加表的大小,增加生成第一个快照所要求的时间)
-[选中一个待发布的表] – [下一步] – [选择发布名称和描述] – –
[下一步] – [自定义发布的属性] – [否,根据指定方式创建发布] –
[下一步] – [完成] – [关闭]
发布属性里有很多有用的选项:设定订阅到期(例如24小时)
设定发布表的项目属性:
常规窗口可以指定发布目的表的名称,可以跟原来的表名称不一样。
下图是命令和快照窗口的栏目 ( SQL Server
数据库复制技术实际上是用insert,update,delete操作在订阅服务器上重做发布服务器上的事务操作
看文档资料需要把发布数据库设成完全恢复模式,事务才不会丢失
但我自己在测试中发现发布数据库是简单恢复模式下,每10秒生成一些大事务,10分钟后再收缩数据库日志,
这期间发布和订阅服务器上的作业都暂停,暂停恢复后并没有丢失任何事务更改 )
发布表可以做数据筛选,例如只选择表里面的部分列:
例如只选择表里某些符合条件的记录, 我们可以手工编写筛选的SQL语句:
发布表的订阅选项,并可以建立强制订阅:
成功建立了发布以后,发布服务器上新增加了一个作业: [ 失效订阅清除 ]
分发服务器上新增加了两个作业: [ JIN001-dack-dack-5 ] 类型[ REPL快照
] [ JIN001-dack-3 ] 类型[ REPL日志读取器 ]
上面蓝色字的名称会根据发布服务器名,发布名及第几次发布而使用不同的编号
REPL快照作业是SQL
Server复制的前提条件,它会先把发布的表结构,数据,索引,约束等生成到发布服务器的OS目录下文件
(当有订阅的时候才会生成, 当订阅请求初始化或者按照某个时间表调度生成)
REPL日志读取器在事务复制的时候是一直处于运行状态。(在合并复制的时候可以根据调度的时间表来运行)
建立一个数据库复制订阅的过程: [复制] – [订阅] – 右键选择 –
[新建请求订阅] – [下一步] – [查找发布] –
[查看已注册服务器所做的发布] – [下一步] – [选择发布] –
[选中已经建立发布服务器上的数据库发布名] – [下一步] –
[指定同步代理程序登录] – [当代理程序连接到代理服务器时:使用SQL
Server身份验证] (输入发布服务器上distributor_admin用户名和密码) –
[下一步] – [选择目的数据库] –
[选择在其中创建订阅的数据库名]/[也可以新建一个库名] – [下一步] –
[允许匿名订阅] – [是,生成匿名订阅] – [下一步] – [初始化订阅] –
[是,初始化架构和数据] – [下一步] – [快照传送] –
[使用该发布的默认快照文件夹中的快照文件]
(订阅服务器要能访问发布服务器的REPLDATA文件夹,如果有问题,可以手工设置网络共享及共享权限)

复制的概念

  • [下一步] – [快照传送] – [使用该发布的默认快照文件夹中的快照文件]
  • [下一步] – [设置分发代理程序调度] – [使用下列调度] – [更改] –
    [例如每五分钟调度一次] – [下一步] – [启动要求的服务] –
    [该订阅要求在发布服务器上运行SQLServerAgent服务] – [下一步] –
    [完成] – [确定]
    成功建立了订阅后,订阅服务器上新增加了一个类别是[REPL-分发]作业(合并复制的时候类别是[REPL-合并])
    它会按照我们给的时间调度表运行数据库同步复制的作业 三、SQL
    Server复制配置好后, 可能出现异常情况的实验日志: 1.发布服务器断网,sql
    server服务关闭,重启动,关机的时候,对已经设置好的复制没有多大影响
    中断期间,分发和订阅都接收到没有复制的事务信息 2.分发服务器断网,sql
    server服务关闭,重启动,关机的时候,对已经设置好的复制有一些影响
    中断期间,发布服务器的事务排队堆积起来
    (如果设置了较长时间才删除过期订阅的选项,
    繁忙发布数据库的事务日志可能会较快速膨胀),
    订阅服务器会因为访问不到发布服务器,反复重试
    我们可以设置重试次数和重试的时间间隔(最大的重试次数是9999,
    如果每分钟重试一次,可以支持约6.9天不出错) 分发服务器sql
    server服务启动,网络接通以后,发布服务器上的堆积作业将按时间顺序作用到订阅机器上:
    会需要一个比较长的时间(实际上是生成所有事务的insert,update,delete语句,在订阅服务器上去执行)
    我们在普通的PC机上实验的58个事务100228个命令执行花了7分28秒.
    3.订阅服务器断网,sql
    server服务关闭,重启动,关机的时候,对已经设置好的复制影响比较大,可能需要重新初试化
    我们实验环境(订阅服务器)从18:46分意外停机以, 第二天8:40分重启动后,
    已经设好的复制在8:40分以后又开始正常运行了,
    发布服务器上的堆积作业将按时间顺序作用到订阅机器上
    但复制管理器里出现快照的错误提示,
    快照可能需要重新初试化,复制可能需要重新启动.
    (我们实验环境的机器并没有进行快照初试化,复制仍然是成功运行的)
    四、删除已经建好的发布和定阅可以直接用delete删除按钮
    我们最好总是按先删定阅,再删发布,最后禁用发布的顺序来操作。
    如果要彻底删去SQL Server上面的复制设置, 可以这样操作: [复制] –
    右键选择 [禁用发布] – [欢迎使用禁用发布和分发向导] – [下一步] –
    [禁用发布] – [要在”@servername”上禁用发布] – [下一步] –
    [完成禁用发布和分发向导] – [完成]
    我们也可以用T-SQL命令来完成复制中发布及订阅的创建和删除,
    选中已经设好的发布和订阅, 按属标右键
    可以[生成SQL脚本]。(这里就不详细讲了,
    后面推荐的网站内有比较详细的内容)
    当你试图删除或者变更一个table时,出现以下错误 Server: Msg 3724, Level
    16, State 2, Line 1 Cannot drop the table object_name because it is
    being used for replication.
    比较典型的情况是该table曾经用于复制,但是后来又删除了复制 处理办法:
    复制代码 代码如下: select * from
    sysobjects where replinfo 0 sp_configure allow updates, 1 go
    reconfigure with override go begin transaction update sysobjects set
    replinfo = 0 where replinfo 0 commit transaction go rollback transaction
    go sp_configure allow updates, 0 go reconfigure with override go 疑问:
    在合并复制配置完全后,如果同步代理停止了。我要在程序中去重新启动合并复制的同步代理。请问使用什么命令或存储过程呢?
    解决办法: sp_start_job 指示 SQL Server 代理程序立即执行作业。 示例
    下例启动名为 Nightly Backup 的作业。 USE msdb EXEC sp_start_job
    @job_name = Nightly Backup 2、日志还原功能、、、 复制代码 代码如下: 说明:
    下面的代码演示了如何利用日志还原功能,将主数据库中的数据变化及时反馈到备用数据库中
    备用数据库的数据可以随时用于查询,但不能被更新。
    首先,创建一个演示用的数据库(主数据库) CREATE DATABASE Db_test ON ( NAME
    = Db_test_DATA, FILENAME = c:\Db_test.mdf ) LOG ON ( NAME =
    Db_test_LOG, FILENAME = c:\Db_test.ldf) GO 对数据库进行备份 BACKUP
    DATABASE Db_test TO DISK=c:\test_data.bak WITH FORMAT GO
    把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步) RESTORE
    DATABASE Db_test_bak FROM DISK=c:\test_data.bak WITH
    REPLACE,STANDBY=c:\db_test_bak.ldf ,MOVE Db_test_DATA TO
    c:\Db_test_data.mdf ,MOVE Db_test_LOG TO c:\Db_test_log.ldf GO
    启动 SQL Agent 服务 EXEC master..xp_cmdshell net start
    sqlserveragent,no_output GO
    创建主服务器数据训与备用服务器数据库之间同步的作业 DECLARE @jogid
    uniqueidentifier EXEC msdb..sp_add_job @job_id = @jogid OUTPUT,
    @job_name = N数据同步处理 创建同步处理步骤 EXEC msdb..sp_add_jobstep
    @job_id = @jogid, @step_name = N数据同步, @subsystem = TSQL, @command
    = N 主数据库中进行日志备份 BACKUP LOG Db_test TO DISK=c:\test_log.bak
    WITH FORMAT 备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化
    实际应该时主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立,并且备份文件应该放在主服务器和备用都能访问的共享目录中
    RESTORE LOG Db_test_bak FROM DISK=c:\test_log.bak WITH
    STANDBY=c:\test_log.ldf, @retry_attempts = 5, @retry_interval = 5
    创建调度(每分钟执行一次) EXEC msdb..sp_add_jobschedule @job_id =
    @jogid, @name = N时间安排, @freq_type=4, @freq_interval=1,
    @freq_subday_type=0x4, @freq_subday_interval=1,
    @freq_recurrence_factor=1 添加目标服务器 EXEC
    msdb.dbo.sp_add_jobserver @job_id = @jogid, @server_name = N(local)
    GO 通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成
    下面开始测试是否能实现同步 在主数据库中创建一个测试用的表 CREATE TABLE
    Db_test.dbo.TB_test(ID int) GO 等待1分钟30秒 WAITFOR DELAY 00:01:30 GO
    查询一下备用数据库,看看同步是否成功 SELECT * FROM
    Db_test_bak.dbo.TB_test 结果: ID – 测试成功 GO 最后删除所有的测试
    DROP DATABASE Db_test,Db_test_bak EXEC msdb..sp_delete_job

    @job_name=N数据同步处理 GO

    服务器档机处理说明 使用这种方式建立的数据库同步,当主数据库不可用时
    可以使用以下两种方法使备用数据库可用。 1.
    如果主数据库损坏,无法备份出最新的日志,可以直接使用下面的语句使备用数据库可读写。
    RESTORE LOG Db_test_bak WITH RECOVERY 2.
    如果主数据库可以备份出最新日志,则可以使用下面的语句。
    先备份主数据库的最新的事务日志 BACKUP LOG Db_test TO
    DISK=c:\test_log.bak WITH FORMAT
    再在备用数据库中恢复最新的事务日志,并且使备用数据库可读写 RESTORE LOG
    Db_test_bak FROM DISK=c:\test_log.bak 简单地说: 1.
    你的sql服务要使用指定的windows用户登陆, 而不能使用”本地系统帐户” 2.
    用于登陆sql服务的用户要求对共享目录具有所有权限 3.
    如果你的电脑没有加入到域,
    还必须保证源和目标服务器的sql服务设置的登陆用户是一样的(用户名和密码都一样)
    网络备份主要是权限设置问题,
    参考下面的备份文件共享目录权限设置方法去解决目录的共享权限就可以了
    下面假设是假设A服务器上的数据库备份到B服务器上的共享目录权限设置(两台服务器应该在局域网内,允许目录共享访问)::
    1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:
    我的电脑 控制面板 管理工具 计算机管理 用户和组 右键用户 新建用户
    建立一个隶属于administrator组的登陆windows的用户
    2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作: 我的电脑D:\
    新建一个目录,名为: BAK 右键这个新建的目录 属性共享 选择”共享该文件夹”
    通过”权限”按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
    确定 3.设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户
    开始程序管理工具服务 右键 MSSQLSERVER 属性登陆选择”此账户”
    输入或者选择第一步中创建的windows登录用户名 “密码”中输入该用户的密码
    确定 同样的方法设置 SQLSERVERAGENT 4.在A机器上完成对B机器BAK目录的映射
    5.查询分析器中执行下面的语句,检验是否成功: exec master..xp_cmdshell dir
    映射的盘符 6.A服务器上做备份计划
    备注:创建一个新的用户只是为了让MSSQLSERVER服务的启动帐户与共享目录的有效访问同名且密码相同,这样才能通过验证(所以你也可以用其他有效的用户来代替,只需要满足用户名和密码相同,并且拥有足够的权限)

Microsoft? SQL Server? 2000
的复制是在数据库之间对数据和数据库对象进行复制和分发并进行同步以确保其一致性的一组技术。

使用复制可以将数据分发到不同位置,通过局域网、使用拨号连接、通过
Internet
分发给远程或移动用户。复制还能够使用户提高应用程序性能,根据数据的使用方式物理分隔数据,或者跨越多个服务器分布数据库处理。

—————————————————————————SQL复制的基本元素包括

发布服务器、订阅服务器、分发服务器、发布、项目

发布服务器发布服务器是提供数据以便复制到其它服务器的服务器。发布服务器可以具有一个或多个发布,每个发布代表一组逻辑相关的数据。除了指定其中哪些数据需要复制之外,发布服务器还检测事务复制期间发生更改的数据并维护有关此站点上所有发布的信息。

分发服务器分发服务器是作为分发数据库宿主并存储历史数据和/或事务以及元数据的服务器。分发服务器的角色根据执行的复制类型而不同。有关更多信息,请参见复制类型。远程分发服务器是独立于发布服务器并配置为复制的分发服务器的服务器。本地分发服务器是既配置为复制的发布服务器又配置为复制的分发服务器的服务器。

订阅服务器订阅服务器是接收复制数据的服务器。订阅服务器订阅的是发布而不是发布中分离的项目;并且订阅服务器只订阅其需要的发布,而不是发布服务器上所有可用的发布。根据复制的类型和所选择的复制选项,订阅服务器还可以将数据更改传播回发布服务器或将数据重新发布到其它订阅服务器。

发布发布是一个数据库中的一个或多个项目的集合。这种多个项目的分组使得指定逻辑相关的一组数据和数据库对象以一起复制变得更容易。

项目项目是指定要复制的数据表、数据分区或数据库对象。项目可以是完整的表、某几列、某几行、存储过程或视图定义、存储过程的执行、视图、索引视图或用户定义函数。

订阅订阅是对数据或数据库对象的复本的请求。订阅定义将接收的发布和接收的时间、地点。订阅的同步或数据分发可以由发布服务器或订阅服务器请求。发布可以支持强制订阅和请求订阅的混合。

—————————————————————————SQL复制的工作原理SQL
SERVER
主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器,负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器分发服务器包含有一个分发数据库,可接收数据的所有改变,并保存这些改变,再把这些改变分发给订阅服务器

精品版SQL Server 2000 数据库同步配置

SQL
SERVER复制技术类型,三种复制技术,分别是(详细的说明参考SQL联机帮助):1、快照复制2、事务复制3、合并复制

下介绍实现复制的步骤。(以快照复制为例)

准备工作:

1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户我的电脑–控制面板–管理工具–计算机管理–用户和组–右键用户–新建用户–建立一个隶属于administrator组的登陆windows的用户

2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:

我的电脑–D:\ 新建一个目录,名为:
PUB–右键这个新建的目录–属性–共享–选择”共享该文件夹”–通过”权限”按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限–确定

3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)

开始–程序–管理工具–服务–右键SQLSERVERAGENT–属性–登陆–选择”此账户”–输入或者选择第一步中创建的windows登录用户名–“密码”中输入该用户的密码

4.设置SQL
Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)

企业管理器–右键SQL实例–属性–安全性–身份验证–选择”SQL Server 和
Windows”–确定

5.在发布服务器和订阅服务器上互相注册企业管理器–右键SQL
Server组–新建SQL
Server注册…–下一步–可用的服务器中,输入你要注册的远程服务器名
–添加–下一步–连接使用,选择第二个”SQL
Server身份验证”–下一步–输入用户名和密码(一般是sa,密码最好设置一下非常复杂的)–下一步–选择SQL
Server组,也可以创建一个新组–下一步–完成

6.对于只能用IP,不能用计算机名的,为其注册服务器别名(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)开始–程序–Microsoft
SQL
Server–客户端网络实用工具–别名–添加–网络库选择”tcp/ip”–服务器别名输入SQL服务器名–连接参数–服务器名称中输入SQL服务器ip地址–如果你修改了SQL的端口,取消选择”动态决定端口”,并输入对应的端口号

==============================================================================

正式开始:

1.配置发布服务器

a. 选中指定 [服务器] 节点b. 从 [工具] 下拉菜单的 [复制]
子菜单中选择 [发布、订阅服务器和分发] 命令c. 系统弹出一个对话框点
[下一步]
然后看着提示操作–直到”指定快照文件夹”–在”快照文件夹”中输入准备工作中创建的目录:
\\服务器名\pub一[下一步] 直操作到完成。d.
当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器同时也生成一个分发数据库(distribution)


2.创建发布a. 选中指定的服务器b. 从 [工具] 菜单的 [复制] 子菜单中选择
[创建和管理发布] 命令。此时系统会弹出一个对话框c.
选择要创建发布的数据库,然后单击 [创建发布]d. 在 [创建发布向导]
的提示对话框中单击 [下一步]
系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)e.
单击 [下一步]
系统要求指定可以订阅该发布的数据库服务器类型,SQLSERVER允许在不同的数据库如
ORACLE或ACCESS之间进行数据复制。但是在这里我们选择运行”SQL SERVER
2000″的数据库服务器f. 单击 [下一步]
,选择要发布的对象(如表,视图,存储过程,一般是表)g. 然后 [下一步]
直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。


3.设计订阅a. 选中指定的订阅服务器b. 从 [工具] 下拉菜单中选择 [复制]
子菜单的 [请求订阅]c. 按照提示单击 [下一步]
操作直到系统会提示检查SQL
SERVER代理服务的运行状态,执行复制操作的前提条件是SQL
SERVER代理服务必须已经启动。d. 单击 [完成] 完成订阅操作。

发表评论

电子邮件地址不会被公开。 必填项已用*标注