--创建测试表,不能用标识列做主键,因为不能进行正常更新 --在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器 if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]
create table test(id int not null constraint PK_test primary key ,name varchar(10)) go
--创建同步的触发器 create trigger t_test on test for insert,update,delete 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
--进行分布事务处理,如果表用标识列做主键,用下面的方法 BEGIN DISTRIBUTED TRANSACTION delete from openrowset('sqloledb','xz';'sa';'',test.dbo.test) where id in(select id from deleted) insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test) select * from inserted commit tran go
--插入数据测试 insert into test select 1,'aa' union all select 2,'bb' union all select 3,'c' union all select 4,'dd' union all select 5,'ab' union all select 6,'bc' union all select 7,'ddd'
--删除数据测试 delete from test where id in(1,4,6)
--更新数据测试 update test set name=name+'_123' where id in(3,5)
--显示测试的结果 select * from test a full join openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id
--“数据库镜像”是一个主要用于提高数据库可用率的软件解决方案。镜像是基于每个数据库执行的， --仅适用于使用完整恢复模式的数据库。简单恢复模式和大容量日志恢复模式不支持数据库镜像。/* 三个SQL Server 2005的实例，如果在同一台机器上不允许端口相同。 server1主数据库服务器 5011 server2备份数据库服务器 5022 jgj监视服务器 5033 Server1上准备好要镜像的数据库，然后完整备份，将备份还原到Server2服务器上，注意使用NoRecovery选项 准备好以上后，开始执行代码： */ --创建端点 --Server1主数据库服务器 CREATE ENDPOINT DbMirroring AS TCP(LISTENER_PORT=5011) FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED) Go Alter EndPoint DbMirroring STATE=STARTED Go --Server2备用服务器 CREATE ENDPOINT DbMirroring AS TCP(LISTENER_PORT=5022) FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED) Go Alter EndPoint DbMirroring STATE=STARTED Go --JGJ见证服务器 CREATE ENDPOINT DbMirroring STATE=STARTED AS TCP(LISTENER_PORT=5033) FOR DATABASE_MIRRORING(ROLE=WITNESS,ENCRYPTION=SUPPORTED) go --配置安全性 --Server1 Use master Go Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator" GO --Server2 Use master Go Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator" GO --jgj Use master Go Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator" GO --启动镜像 --Server2 ALTER DATABASE DBMirror SET PARTNER=N'TCP://jgj:5011' Go --Server1 ALTER DATABASE DBMirror SET PARTNER=N'TCP://jgj:5022' Go --指定见证服务器端点 ALTER DATABASE DBMirror SET WITNESS=N'TCP://jgj:5033' GO --配置数据库镜像事务安全级别 ALTER DATABASE DBMirror Set SAFETY Full GO --通过系统目录视图查看数据库镜像配置情况 Select * from sys.database_mirroring_endpoints Select * from sys.database_mirroring where database_id=(select database_id from sys.databases where name =’ DBMirror’) --数据库镜像角色手动切换 ALTER DATABASE DBMirror SET PARTNER FAILOVER --客户端重定向 --(使用ADO.Net或SQL Native Client能够自动连接到故障转移后的伙伴) ConnectionString="Data Source=A;Failover Partner=B; Initial Catalog=AdventureWorks;Integrated Security=True;"
1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户（SynUser）
2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
我的电脑--D:\ 新建一个目录,名为: PUB
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
--确定
3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
开始--程序--管理工具--服务
--右键SQLSERVERAGENT
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名（SynUser）
--"密码"中输入该用户的密码
4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
企业管理器
--右键SQL实例--属性
--安全性--身份验证
--选择"SQL Server 和 Windows"
--确定
5.在发布服务器和订阅服务器上互相注册
企业管理器
--右键SQL Server组
--新建SQL Server注册...
--下一步--可用的服务器中,输入你要注册的远程服务器名 --添加
--下一步--连接使用,选择第二个"SQL Server身份验证"
--下一步--输入用户名和密码（SynUser）
--下一步--选择SQL Server组,也可以创建一个新组
--下一步--完成
6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）
(在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
开始--程序--Microsoft SQL Server--客户端网络实用工具
--别名--添加
--网络库选择"tcp/ip"--服务器别名输入SQL服务器名
--连接参数--服务器名称中输入SQL服务器ip地址
--如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
二、 正式配置
1、配置发布服务器
打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:
(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导
(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）
(3) [下一步] 设置快照文件夹
采用默认\\servername\Pub
(4) [下一步] 自定义配置
可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置
否,使用下列默认设置（推荐）
(5) [下一步] 设置分发数据库名称和位置 采用默认值
(6) [下一步] 启用发布服务器 选择作为发布的服务器
(7) [下一步] 选择需要发布的数据库和发布类型
(8) [下一步] 选择注册订阅服务器
(9) [下一步] 完成配置
2、创建出版物
发布服务器B、C、D上
(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令
(2)选择要创建出版物的数据库，然后单击[创建发布]
(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,
SQLSERVER允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。
但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表
(6)选择发布名称和描述
(7)自定义发布属性 向导提供的选择:
是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性
否 根据指定方式创建发布 （建议采用自定义的方式）
(8)[下一步] 选择筛选发布的方式
(9)[下一步] 可以选择是否允许匿名订阅
1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器
方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
如果仍然需要匿名订阅则用以下解决办法
[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅
2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示
(10)[下一步] 设置快照 代理程序调度
(11)[下一步] 完成配置
当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。----------------------------
--====================================================
--发布/订阅的效果最好.
--自己写触发器同步的实时性和可控制性最好.
--====================================================
如果只是简单的数据同步,可以用触发器来实现.下面是例子:
--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test
--创建测试表,不能用标识列做主键,因为不能进行正常更新
--在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]
create table test(id int not null constraint PK_test primary key
,name varchar(10))
go
--创建同步的触发器
create trigger t_test on test
for insert,update,delete
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 openrowset('sqloledb','xz';'sa';'',test.dbo.test)
where id in(select id from deleted)
insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test)
select * from inserted
commit tran
go
--插入数据测试
insert into test
select 1,'aa'
union all select 2,'bb'
union all select 3,'c'
union all select 4,'dd'
union all select 5,'ab'
union all select 6,'bc'
union all select 7,'ddd'
--删除数据测试
delete from test where id in(1,4,6)
--更新数据测试
update test set name=name+'_123' where id in(3,5)
--显示测试的结果
select * from test a full join
openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id
--仅适用于使用完整恢复模式的数据库。简单恢复模式和大容量日志恢复模式不支持数据库镜像。/*
三个SQL Server 2005的实例，如果在同一台机器上不允许端口相同。
server1主数据库服务器 5011
server2备份数据库服务器 5022
jgj监视服务器 5033 Server1上准备好要镜像的数据库，然后完整备份，将备份还原到Server2服务器上，注意使用NoRecovery选项 准备好以上后，开始执行代码：
*/ --创建端点
--Server1主数据库服务器
CREATE ENDPOINT DbMirroring
AS TCP(LISTENER_PORT=5011)
FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)
Go
Alter EndPoint DbMirroring STATE=STARTED
Go --Server2备用服务器
CREATE ENDPOINT DbMirroring
AS TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)
Go
Alter EndPoint DbMirroring STATE=STARTED
Go --JGJ见证服务器
CREATE ENDPOINT DbMirroring
STATE=STARTED
AS TCP(LISTENER_PORT=5033)
FOR DATABASE_MIRRORING(ROLE=WITNESS,ENCRYPTION=SUPPORTED)
go --配置安全性
--Server1
Use master
Go
Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator"
GO --Server2
Use master
Go
Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator"
GO --jgj
Use master
Go
Grant connect on endpoint::"DbMirroringEP" to "jgj\administrator"
GO --启动镜像
--Server2
ALTER DATABASE DBMirror
SET PARTNER=N'TCP://jgj:5011'
Go --Server1
ALTER DATABASE DBMirror
SET PARTNER=N'TCP://jgj:5022'
Go --指定见证服务器端点
ALTER DATABASE DBMirror
SET WITNESS=N'TCP://jgj:5033'
GO --配置数据库镜像事务安全级别
ALTER DATABASE DBMirror Set SAFETY Full
GO
--通过系统目录视图查看数据库镜像配置情况
Select * from sys.database_mirroring_endpoints
Select * from sys.database_mirroring where database_id=(select database_id from sys.databases where name =’ DBMirror’) --数据库镜像角色手动切换
ALTER DATABASE DBMirror SET PARTNER FAILOVER
--客户端重定向
--(使用ADO.Net或SQL Native Client能够自动连接到故障转移后的伙伴)
ConnectionString="Data Source=A;Failover Partner=B;
Initial Catalog=AdventureWorks;Integrated Security=True;"
SQL复制的基本元素包括
出版服务器、订阅服务器、分发服务器、出版物、文章。
SQL复制的工作原理
SQL SERVER 主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器。
SQL SERVER复制技术类型
SQL SERVER提供了三种复制技术，分别是：
1、快照复制(呆会我们就使用这个)
2、事务复制
3、合并复制
只要把上面这些概念弄清楚了那么对复制也就有了一定的理解。
接下来我们就一步一步来实现复制的步骤。
一:先来配置出版服务器
(1)选中指定[服务器]节点。
(2)从[工具]下拉菜单的[复制]子菜单中选择[发布、订阅服务器和分发]命令。
(3)系统弹出一个对话框点[下一步]然后看着提示一直操作到完成。
(4)当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器。同时也生成一个分发数据库(distribution)。
二:创建出版物
(1)选中指定的服务器。
(2)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令。此时系统会弹出一个对话框。
(3)选择要创建出版物的数据库，然后单击[创建发布]。
(4)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)。
(5)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如 ORACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(6)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表。
(7)然后[下一步]直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。
三:设计订阅
(1)选中指定的订阅服务器。
(2)从[工具]下拉菜单中选择[复制]子菜单的[请求订阅]。
(3)按照单击[下一步]操作直到系统会提示检查SQL SERVER代理服务的运行状态，执行复制操作的前提条件是SQL SERVER代理服务必须已经启动。然后[下一步]直到操作完。