关于分布式事务配置及使用@win2003 and sqlserver2k

  在多台SQL Server服务器之间进行事务处理时，我们用到分布式事务技术。分布式事务由 Microsoft 分布式事务处理协调器 (MS DTC)）这类事务管理器或其它支持 X/Open XA 分布式事务处理规范的事务管理器进行协调。Microsoft提供了MSDTC(Distributed Transaction Coordinator)服务支持分布式事务。

基本做法是，在两台SQL Server服务器上启动MSDTC服务，并在本地服务器上为另一台服务器建立Linked Server，这样本地服务器就可以访问另一台服务器。当对链接服务器执行分布式查询时，请对每个要查询的数据源指定完全合法的、由四部分组成的表名。这个由四部分组成的名称的格式应是：linked_server_name.catalog.schema.object_name

分布式事务通过两阶段提交(2PC)保证事务一致性。基本原理是，

准备阶段

当事务管理器收到提交请求时，它给该事务所涉及的所有资源管理器发送一个准备命令。然后，每个资源管理器将尽力使该事务持久，并且所有保存该事务日志映象的缓冲区将被刷新到磁盘中。当每个资源管理器完成准备阶段时，它会向事务管理器返回准备成功或准备失败的消息。

提交阶段

如果事务管理器收到所有资源管理器发来的准备成功消息，它将给每个资源管理器发送提交命令。然后资源管理器就可以完成提交。如果所有资源管理器都报告提交成功，那么事务管理器则向应用程序发送一个成功提示。如果有资源管理器报告准备失败，那么事务管理器将给每个资源管理器发送一个回滚命令，并向应用程序表示提交失败。

一 配置LinkedServer
1. 执行 sp_addlinkedserver 创建链接服务器
-- 创建一个名为 LinkSQLSrvr 的链接服务器，以便对运行于网络名称为 NetSQLSrvr 的服务器上的 SQL Server 实例进行操作
sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'NetSQLSrvr'
2. 将每个需要访问链接服务器的本地 SQL Server 登录映射为链接服务器上的 SQL Server 授权登录。
--将本地登录 Joe 的访问权限映射到名为 LinkedSQLSrvr 的链接服务器上的 SQL Server 授权登录 Visitor。
sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'Joe', N'Visitor', N'VisitorPwd'
二 配置并启动MSDTC
在2边SQL Server上都要做配置。
1 配置MSDTC
1) 通过Administrative Tools菜单找到Component Services,点击进入"组件服务管理工具"，展开"组件服务"树，右键单击"我的电脑"，然后选择"属性"。在 MSDTC 选项卡中，点击按钮进入“Security Configuration安全配置”确保选中了下列选项：
网络 DTC 访问(Network DTC Access)，网络管理(Allow Remote Clients, Allow Remote Administration)，网络事务(Allow Inbound, allow outbound, Enable TIP)，XA 事务(Enable XA transactions)；
2) "DTC 登录帐户"一定要设置为"NT AuthorityNetworkService"。
3) 单击"确定"。这样将会提示您"MS DTC 将会停止并重新启动。所有的依赖服务将被停止。请按'是'继续"。单击"是"继续。单击"确定"关闭"我的电脑"属性窗口。
提示：在命令提示行中，运行"net stop msdtc"，"net start msdtc"可停止、启动msdtc服务。
2 配置Windows防火墙
运行firewall.cpl打开Windows防火墙
1) 可以关掉Windows防火墙；
2) MSDTC依赖于RPC,RPC使用的端口是TCP 135 Port。所以，在exception中增加msdtc.exe和TCP 135 Port的例外，这样，windows防火墙就可以给msdtc“放行”了。

三 分布式编程示例

1> 简单示例，当往本地表中插入数据时同步网链接数据库表中插入记录。使用分布式事务控制。
调用：
DBCC TRACEON (3604, 7300)--用跟踪看更详细错误信息.
SET XACT_ABORT On
declare @ret int
set @ret = 0
begin distributed tran
insert into users(user_name, addr)
select '大宝', '北京同仁堂' --user_name, addr
set @ret = @ret + @@error

insert into linkedserver.DBPerfLog.dbo.temp_users (user_name, addr)
select user_name, addr
from users where user_id = @user_id
set @ret = @ret + @@error

if @ret <> 0
rollback tran

select * from users
select * from szretailtest01.DBPerfLog.dbo.users
if @ret <> 0
rollback tran
else
commit tran

2> 采用触发器同步不同服务器上数据
--测试环境: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

--以下操作在本机进行
--在本机上建表及做同步处理的工作
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 identity(1,1) 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

四 相关信息
1> Sp_addlinkedserver：创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft® SQL Server™，则可执行远程存储过程。
2> sp_addlinkedsrvlogin
3> select @@SERVERNAME
4> sp_serveroption linkedserver5> sp_setnetname remote1, <remote server name>
6> sp_linkedservers
7> SET XACT_ABORT ON
8> begin distributed tran9> OPENROWSET：包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的、特殊的方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 提供程序的能力，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，然而 OPENROWSET 只返回第一个。
10> OPENQUERY：在给定的链接服务器（一个 OLE DB 数据源）上执行指定的直接传递查询。可以在查询的 FROM 子句中像引用表名那样引用 OPENQUERY 函数。依据 OLE DB 提供程序的能力，还可以将 OPENQUERY 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，但是 OPENQUERY 只返回第一个。
11> OPENDATASOURCE：不使用链接的服务器名，而提供特殊的连接信息，并将其作为四部分对象名的一部分。