sql server分布式事务解决方案

适用环境

操作系统：windows 2003

数据库：sql server 2000/sql server 2003

使用链接服务器进行远程数据库访问的情况

一、  问题现象
在执行分布式事务时，在sql server 2005下收到如下错误：

消息 7391，级别 16，状态 2，过程 xxxxx，第 16 行

无法执行该操作，因为链接服务器 "xxxxx" 的 OLE DB 访问接口 "SQLNCLI" 无法启动分布式事务。

在sql server 2000下收到如下错误：

该操作未能执行，因为 OLE DB 提供程序 'SQLOLEDB' 无法启动分布式事务。

[OLE/DB provider returned message: 新事务不能登记到指定的事务处理器中。 ]

OLE DB 错误跟踪［OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a］。
 

二、  解决方案
1.        双方启动MSDTC服务
MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

2.        打开双方135端口
MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。  

  使用“telnet IP 135 ”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放。

3.        保证链接服务器中语句没有访问发起事务服务器的操作
在发起事务的服务器执行链接服务器上的查询、视图或存储过程中含有访问发起事务服务器的操作，这样的操作叫做环回（loopback），是不被支持的，所以要保证在链接服务器中不存在此类操作。

4.        在事务开始前加入set xact_abort ON语句
对于大多数 OLE DB 提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。

5.        MSDTC设置
打开“管理工具――组件服务”，以此打开“组件服务――计算机”，在“我的电脑”上点击右键。在MSDTC选项卡中，点击“安全配置”按钮。

在安全配置窗口中做如下设置：

l选中“网络DTC访问”

l在客户端管理中选中“允许远程客户端”“允许远程管理”

l在事务管理通讯中选“允许入站”“允许出站”“不要求进行验证”

l保证DTC登陆账户为：NT   Authority/NetworkService





[转载]关于SQL Server 2000和SQL Server 2005分布式事务能否协同工作的测试


引自: http://forum.eviloctal.com/thread-22130-1-2.html


1. MS DTC 背景

2. MSDTC 测试目的

3. MSDTC 测试环境

3.1 本次验证测试环境：

3.2 环境配置

3.3 验证MSDTC

3.4 创建验证用表

4. Linked Server测试

5. 结论

1. MS DTC 背景

主持结婚典礼的牧师先问新娘和新郎“愿意此人成为您的配偶吗？” 如果他们都回答“愿意”，牧师就会宣布他们结婚。

这个情景说明了事务的基本原理：几个独立的实体必须达成一致。如果任何一方不同意，交易就会失败。一旦同意后，事务就会发生。Microsoft Distributed Transaction Coordinator (MS? DTC) 为 COM 结构的其它组件执行这项事务协调任务。在MS DTC，执行者被称为事务管理器。在执行事务保护资源的事务中，其参与者（如关系数据库）被称为资源管理器。

应用程序通过调用事务管理器的 Begin Distributed Transaction 方法开始事务。这样可创建一个代表事务的事务对象。然后应用程序会调用资源管理器来完成事务工作。当某个资源管理器首先代表某个事务工作时，会通过调用事务管理器“登记”到该事务中。随着事务的发展，事务管理器会跟踪每个登记到该事务中的资源管理器。当应用程序成功地完成事务的工作后，它会调用 MS DTC 来“提交”事务。然后 MS DTC 会仔细检查“两阶段” “提交协议”，使所有已登记的资源管理器都提交。两阶段提交协议可确保所有的资源管理器提交此事务，或全都放弃此事务。在第一阶段，MS DTC 询问每个资源管理器是否“准备”提交。如果所有参与者都回答“是”，那么在第二阶段 MS DTC 将向所有参与者广播提交信息。如果事务的任何部分失败，或资源管理器响应准备请求失败，或资源管理器响应“否”，。则调用 Abort 事务方法，该方法可以撤消事务的操作

2. MSDTC 测试目的

通过进行SQL Server 2005和本机SQL Server 2000实例间及和其它独立SQL Server 2000计算机的分布式事务的测试来验证SQL Server不同版本间分布式事务的兼容性及可行性。

3. MSDTC 测试环境

3.1 本次验证测试环境：

? 一台真正的计算机(SQL Server 2000)

? 一台虚拟机(装有SQL Server 2000默认实例及SQL Server 2005命名实例)

? 测试工具:DTCTest &DTCPing

3.2 环境配置

1.关闭两台计算机的防火墙

2.打开SQL Server 2005服务器端的TCP监听

3.打开SQL Server2005远程连接

4. 如下设置MSDTC

3.3 验证MSDTC

首先使用DTC Test来验证两台计算机DTC是不是工作正常，在两台计算机上各建一个ODBC Datasource,在命令行下运行DTCTEST

确认MSDTC工作正常

3.4 创建验证用表

1. 在SQL Server 2005的AdventureWorks数据库中使用如下脚本创建表,此表用于以后的MSDTC的插入，删除，更改及查询测试：

CREATE TABLE DBO.TEST2005

(

ID1 INT IDENTITY(1,1),

DES VARCHAR(100)

)

2. 在SQL Server 2000的Pubs数据库中使用如下脚本创建表,此表用于以后的MSDTC的插入，删除，更改及查询测试：

CREATE TABLE DBO.TEST2000

(

ID1 INT IDENTITY(1,1),

DES VARCHAR(100)

)

4. Linked Server测试

4.1 SQL Server2005中加入SQL Server 2000 链接服务器，并进行相应的测试

在SQl Server 2005中，使用向导或者以下脚本来创建一个Linked Server，使此Linked Server指向SQL Server 2000数据库misdbs01

/****** Object: LinkedServer [MISDBS01] Script Date: 10/27/2005 17:39:33 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'MISDBS01', @srvproduct=N'SQL Server'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MISDBS01', @optname=N'use remote collation', @optvalue=N'true'

在SQL Server 2005中展开对象浏览器到Linked Server 目录下，可以看到已经有一个叫misdbs01的linked server存在，下面对此linked server进行验证。验证步骤将分别用SELECT,DELETE,UPDATE,INSERT来对SQL Server 2000 中PUBS数据库下的test2000进行相应操作。

/******

RETRIVE DATA FROM REMOTE SERVER

******/

BEGIN DISTRIBUTED TRAN

SELECT * FROM MISDBS01.PUBS.DBO.TEST2000

COMMIT TRAN

/******

INSERT DATA TO REMOTE SERVER

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

INSERT MISDBS01.PUBS.DBO.TEST2000(DES)

SELECT 'A'

COMMIT TRAN

/******

DELETE REMOTE SERVER'S DATA

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

DELETE FROM MISDBS01.PUBS.DBO.TEST2000

COMMIT TRAN

/******

UPDATE REMOTE SERVER'S DATA

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

UPDATE MISDBS01.PUBS.DBO.TEST2000

SET DES='B'

COMMIT TRAN

同理验证YUKON/DW连接本地的SQL Server 2000默认实例，也是成功的。

上面的测试全部正常工作，由此可见测试可见MSDTC是能够正常工作的，从Linked Server的创建脚本中我们也可以发现其实SQL Server 2005中使用的SQL Native Client provider来连接SQL Server 2000的，参照MSDN中的如下描述，我们可以知道能够正常成功是正常的：

The following table shows the OLE DB providers that have been tested with SQL Server distributed queries. All these providers support being referenced in a SELECT statement by specifying a pass-through query in the OPENQUERY and OPENROWSET functions.

Data source

    

Provider name

    

Use in four-part names

    

Use in pass-through queries and commands

    

Use in INSERT, UPDATE, or DELETE

    

Use in distributed transactions

SQL Server7.0 or later

    

Microsoft SQL Native Client OLE DB Provider

    

Yes

    

Yes

    

Yes

    

Yes

4.2 SQL Server2000中加入SQL Server 2005 链接服务器，并进行相应的测试

先在SQL Server 2000企业管理器或者直接使用T-SQL语句新增加一个Linked Server,此Linked Server指向SQL Server 2005.

在SQL Server 2000中展开对象浏览器到Linked Server 目录下，可以看到已经有一个叫YUKON/DW的linked server存在，下面对此linked server进行验证。验证步骤将分别用SELECT,DELETE,UPDATE,INSERT来对SQL Server 2005 中AdventureWorks数据库下的test2005进行相应操作。

/******

RETRIVE DATA FROM REMOTE SERVER

******/

BEGIN DISTRIBUTED TRAN

SELECT * FROM [YUKON/DW].ADVENTUREWORKS.DBO.TEST2005

COMMIT TRAN

/******

INSERT DATA TO REMOTE SERVER

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

INSERT [YUKON/DW].ADVENTUREWORKS.DBO.TEST2005(DES)

SELECT 'A'

COMMIT TRAN

/******

DELETE REMOTE SERVER'S DATA

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

DELETE FROM [YUKON/DW].ADVENTUREWORKS.DBO.TEST2005

COMMIT TRAN

/******

UPDATE REMOTE SERVER'S DATA

******/

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

UPDATE [YUKON/DW].ADVENTUREWORKS.DBO.TEST2005

SET DES=’B’

COMMIT TRAN

由上面的测试可以看出由本机SQL Server 2000连接链接到SQL Server 2005的过程也是成功的，其实现在是使用的SQLOLEDB来进行联接的。

同理验证由其它计算的SQL Server 2000连接链接到此SQL Server 2005实例，经过验证，也是可以正常工作的，可见SQL Server 2005依然在这方面支持SQL Server2000的Provider.

4. 结论

可见SQL Server 2000和SQL Server 2005间不同版本间的分布式事务能够协同工作。

sql server分布式事务解决方案

引自：http://www.cnblogs.com/chnking/archive/2007/04/04/699891.html

适用环境

操作系统：windows 2003

数据库：sql server 2000/sql server 2005

使用链接服务器进行远程数据库访问的情况

一、 问题现象

在执行分布式事务时，在sql server 2005下收到如下错误：

消息 7391，级别 16，状态 2，过程 xxxxx，第 16行

无法执行该操作，因为链接服务器 "xxxxx"的 OLE DB访问接口 "SQLNCLI"无法启动分布式事务。

在sql server 2000下收到如下错误：

该操作未能执行，因为 OLE DB提供程序 'SQLOLEDB'无法启动分布式事务。

[OLE/DB provider returned message:新事务不能登记到指定的事务处理器中。 ]

OLE DB错误跟踪［OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a］。

 

二、 解决方案

1.       双方启动MSDTC服务

MSDTC服务提供分布式事务服务，如果要在数据库中使用分布式事务，必须在参与的双方服务器启动MSDTC（Distributed Transaction Coordinator）服务。

2.       打开双方135端口

MSDTC服务依赖于RPC（Remote Procedure Call (RPC)）服务,RPC使用135端口，保证RPC服务启动，如果服务器有防火墙，保证135端口不被防火墙挡住。  

 使用“telnet IP 135 ”命令测试对方端口是否对外开放。也可用端口扫描软件（比如Advanced Port Scanner）扫描端口以判断端口是否开放。

3.       保证链接服务器中语句没有访问发起事务服务器的操作

在发起事务的服务器执行链接服务器上的查询、视图或存储过程中含有访问发起事务服务器的操作，这样的操作叫做环回（loopback），是不被支持的，所以要保证在链接服务器中不存在此类操作。

4.       在事务开始前加入set xact_abort ON语句

对于大多数 OLE DB提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。

5.       MSDTC设置

打开“管理工具――组件服务”，以此打开“组件服务――计算机”，在“我的电脑”上点击右键。在MSDTC选项卡中，点击“安全配置”按钮。

在安全配置窗口中做如下设置：

l        选中“网络DTC访问”

l        在客户端管理中选中“允许远程客户端”“允许远程管理”

l        在事务管理通讯中选“允许入站”“允许出站”“不要求进行验证”

l        保证DTC登陆账户为：NT  Authority/NetworkService

6.       链接服务器和名称解析问题

建立链接sql server服务器，通常有两种情况:

l        第一种情况，产品选”sql server”

EXEC sp_addlinkedserver

  @server='linkServerName',

  @srvproduct = N'SQL Server'

这种情况，@server（linkServerName）就是要链接的sqlserver服务器名或者ip地址。

l        第二种情况，访问接口选“Microsoft OLE DB Provider Sql Server”或“Sql Native Client”

EXEC sp_addlinkedserver  

  @server=' linkServerName ',

  @srvproduct='',

  @provider='SQLNCLI',

  @datasrc='sqlServerName'

这种情况，@datasrc（sqlServerName）就是要链接的实际sqlserver服务器名或者ip地址。

 

Sql server数据库引擎是通过上面设置的服务器名或者ip地址访问链接服务器，DTC服务只通过服务器名地址访问链接服务器，所以要保证数据库引擎和DTC都能通过服务器名或者ip地址访问到链接服务器。

数据库引擎和DTC解析服务器的方式不太一样，下面分别叙述

6.1      数据库引擎

第一种情况的@server或者第二种情况的@datasrc设置为ip地址时，数据库引擎会根据ip地址访问链接服务器，这时不需要做名称解析。

第一种情况的@server或者第二种情况的@datasrc设置为sql server服务器名时，需要做名称解析，就是把服务器名解析为ip地址。

有两个办法解析服务器名：

一是在sql server客户端配置中设置一个别名，将上面的服务器名对应到链接服务器的ip地址。

二是在“C:/WINDOWS/system32/drivers/etc/hosts”文件中增加一条记录：

xxx.xxx.xxx.xxx  服务器名

作用同样是把服务器名对应到链接服务器的ip地址。

6.2      DTC

不管哪一种情况，只要@server设置的是服务器名而不是ip地址，就需要进行名称解析，办法同上面第二种办法，在hosts文件中增加解析记录，上面的第一种办法对DTC不起作用。

如果@server设置的是ip地址，同样不需要做域名解析工作。

 

7.      远程服务器上的名称解析

分布式事务的参与服务器是需要相互访问的，发起查询的服务器要根据机器名或ip查找远程服务器的，同样远程服务器也要查找发起服务器，远程服务器通过发起服务器的机器名查找服务器，所以要保证远程服务器能够通过发起服务器的机器名访问到发起服务器。

一般的，两个服务器在同一网段机器名能就行很好的解析，但是也不保证都能很好的解析，所以比较保险的做法是：

在远程服务器的在“C:/WINDOWS/system32/drivers/etc/hosts”文件中增加一条记录：

xxx.xxx.xxx.xxx  发起服务器名

参考：

zjcxc(邹建)的解决方案 (http://topic.csdn.net/t/20040608/12/3073266.html)

微软How to troubleshoot error 7391 that occurs when you use a linked server in SQL Server