Tests on whether SQL Server 2000 and SQL Server 2005 distributed transactions work together

server| distributed

Tests on whether SQL Server 2000 and SQL Server 2005 distributed transactions work together
MS DTC

1. MS DTC Background

2. The purpose of the MSDTC test

3. MSDTC test Environment

3.1 Verification test Environment:

3.2 Environment Configuration

3.3 Verifying MSDTC

3.4 Creating validation tables

4. Linked Server Test

5. Conclusion

1. MS DTC Background

The priest who presided over the wedding ceremony asked the bride and groom, "Would you like this person to be your spouse?" "If they all answer" yes ", the priest will announce their marriage.

This scenario illustrates the fundamentals of the transaction: several independent entities must agree. If either party disagrees, the deal will fail. Once agreed, the transaction will occur. Microsoft Distributed Transaction Coordinator (MS? DTC) performs this transaction coordination task for other components of the COM structure. In Ms DTC, the performer is called the transaction manager. In a transaction that performs a transaction protection resource, its participants, such as a relational database, are called resource managers.

The application starts the transaction by invoking the start distributed Transaction method of the transaction manager. This creates a transaction object that represents the transaction. The application then invokes the resource manager to complete the transaction work. When a resource manager works on behalf of a transaction, it is "enlisted" into the transaction by invoking the transaction manager. As the transaction progresses, the transaction manager keeps track of each resource manager that is enlisted in the transaction. When the application completes the transaction successfully, it invokes the MS DTC to "commit" the transaction. MS DTC then examines the "two-phase" commit protocol carefully so that all registered resource managers are committed. The two-phase commit protocol ensures that all the resource managers commit the transaction, or all of them abandon the transaction. In the first phase, MS DTC asks each resource manager if it is "ready" to commit. If all participants answered yes, then in the second phase MS DTC broadcasts submission information to all participants. If any part of the transaction fails, or if the resource manager fails to respond to a prepare request, or the resource manager responds to "no". Call the Abort transaction method, which can undo the operation of the transaction

2. The purpose of the MSDTC test

Verify the compatibility and feasibility of distributed transactions between SQL Server versions by using a test for distributed transactions between SQL Server 2005 and native SQL Server 2000 instances and with other stand-alone SQL Server 2000 computers.

3. MSDTC test Environment

3.1 Verification test Environment:

? A real computer (SQL Server 2000)

? One virtual machine (with SQL Server 2000 default instance and SQL Server 2005 named instance)

? Test tools: Dtctest &dtcping

3.2 Environment Configuration

1. Close the firewall of two computers

2. Open TCP Monitor on SQL Server 2005 server side

3. Open SQL Server2005 Remote connection

4. Set up MSDTC as follows

3.3 Verifying MSDTC

First use the DTC test to verify that the DTC is working properly, build an ODBC Datasource on both computers, and run dtctest at the command line

Confirm that MSDTC is working properly

3.4 Creating validation tables

1. In SQL Server 2005 's AdventureWorks database, use the following script to create a table for insertion, deletion, change, and query testing of later MSDTC:

CREATE TABLE DBO. TEST2005

(

ID1 INT IDENTITY (1,1),

DES VARCHAR (100)

)

2. Create a table in the pubs database of SQL Server 2000 using the following script, which is used for insertion, deletion, change, and query testing of later MSDTC:

CREATE TABLE DBO. TEST2000

(

ID1 INT IDENTITY (1,1),

DES VARCHAR (100)

)

4. Linked Server Test

4.1 SQL Server 2000 linked server and test accordingly

In SQL Server 2005, use the wizard or script to create a linked server that points this linked server to the SQL Server 2000 database 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 '

In SQL Server 2005, expand the Object Browser to the linked server directory, you can see that there is already a linked server called MISDBS01 exists, the following linked server validation. The verification step will use Select,delete,update,insert to operate the test2000 under the pubs database in SQL Server 2000, respectively.

/******

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

Similarly, verifying that the YUKON\DW connects to the local SQL Server 2000 default instance is also successful.

The above tests are all working properly, so the test shows that MSDTC works, and from the linked server's creation script we can also find that SQL Native Client provider used in SQL Server 2005 to connect to SQL Server 2000, as described in MSDN, we can see that it's normal to be successful:

The following table shows the OLE DB providers that have been tested with SQL Server distributed queries. All this providers support being referenced in a SELECT statement from 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 Server 7.0 or later	Microsoft SQL Native Client OLE DB Provider	Yes	Yes	Yes	Yes

4.2 SQL Server2000 added in SQL Server link the server and test accordingly Add a new linked server in SQL Server 2000 Enterprise Manager or directly using T-SQL statements, which points to SQL Server 2005. Linked server.in theSQL Server 2000to expand the Object Browser inLinked Serverdirectory, you can see that there is already a callYukon\dwof theLinked serverexists, the following for thisLinked serverfor verification. The verification steps are usedSelect,delete,update,insertto theSQL Server 2005inAdventureWorksdatabase under thetest2005perform the appropriate action.  /****** retrive DATA from REMOTE server ******/begin distributed tran    SELECT * FROM [YUKON\D W]. ADVENTUREWORKS. Dbo. Test2005commit tran/****** INSERT DATA to REMOTE server ******/set xact_abort Onbegin Distributed nbsp INSERT [YUKON\DW]. ADVENTUREWORKS. Dbo. TEST2005 (DES)     SELECT ' A ' COMMIT tran/****** DELETE REMOTE SERVER ' S data ******/ set xact_ ABORT onbegin distributed tran    DELETE from  [YUKON\DW]. ADVENTUREWORKS. Dbo. TEST2005 COMMIT tran /****** UPDATE REMOTE SERVER ' S data ******/set xact_abort onbegin distributed tran & nbsp;  UPDATE [YUKON\DW]. ADVENTUREWORKS. Dbo. test2005     SET des= ' B ' COMMIT tran The above test shows that the process of linking to SQL Server 2005 by a native SQL Server 2000 connection is also successful, In fact, now is the use of SQLOLEDB to join. Similarly, verifying that the other computed SQL Server 2000 connection links to this instance of SQL Server 2005 is validated and works, and that SQL Server 2005 still supports SQL Server2000 provider in this regard.   4. Conclusions VisibleSQL Server 2000andSQL Server 2005distributed transactions between different versions can work together.