Distributed Transactions
As you learned on Chapter 13, "Maintaining Data Consistency: Transactions and Locks," you can consider a group of Transact-SQL statements as part of the same transaction. If the data affected by a transaction is spread across different servers, you need to create a distributed transaction.
To create a distributed transaction, you must start the Microsoft Distributed Transaction Coordinator service (MS-DTC), and the connection must use the SET_XACT_ABORT ON setting.
MS-DTC implements a two-phase commit mechanism to guarantee transaction consistency across different servers. This process can be described as follows:
You connect to a SQL Server instance and start a distributed transaction, using the SET XACT_ABORT ON and BEGIN DISTRIBUTED TRANSACTION statements.
You send a DML statement to another instance or server. MS-DTC running on your server must contact the MS-DTC running on the other server to start a distributed transaction and to send the DML statement to be executed remotely.
You can send other commands to other instances or servers, including the server you are connected to. In every case, MS-DTC will check whether this connection already has a distributed transaction with the target server.
When your operations have terminated and you want to commit all changes, send the COMMIT TRAN statement.
MS-DTC takes control of the commit process and asks every participant server whether they are ready to commit.
Every server answers the commit request, sending an affirmative or negative vote.
MS-DTC counts the votes received. If there is one negative vote, it informs every participant server that they must roll back the operation. If all votes are affirmative, MS-DTC informs them that they can finally commit.
Listing 15.23 shows an example of a distributed transaction, where you can update from two different instances as part of the same transaction.
Code Listing 15.23. Use Distributed Transactions to Maintain Transaction Consistency Across Multiple Servers
|
-- This setting is required
-- to start Distributed Transactions
SET XACT_ABORT ON
GO
-- Start a Distributed Transaction
BEGIN DISTRIBUTED TRANSACTION
-- Modify data locally
UPDATE Northwind.dbo.Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
-- Modify the same data remotely
UPDATE SQLBEInst2.Northwind.dbo.Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
-- Confirm changes
COMMIT TRANSACTION
GO
(12 row(s) affected)
(12 row(s) affected)
You can create the DistSalesInfo distributed partitioned view, as shown in Listing 15.24. This view is created based on the tables SalesInfoNorth and SalesInfoSouth that you created in the local server with Listing 15.22. Now you must create the SalesInfoEast and SalesInfoWest tables in the SQLBEInst2 instance, using the same script from Listing 15.22.
You can modify a record from the distributed partitioned view, as in Listing 15.24, to change the RegionID field from North (3) to East (1). You can see how the record has been moved automatically from the SalesInfoNorth table in the local server to the SalesInfoEast table in the linked server. The output shows one row less in the local SalesInfoNorth table, and one more row in the remote SalesInfoEast table.
In this case, it is not necessary to start a distributed transaction, because SQL Server does it automatically for you, executing this statement in autocommitted mode.
Code Listing 15.24. Use Distributed Transactions to Maintain Transaction Consistency Across Multiple Servers
|
----------------------------------------------------
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE
--
-- Execute this script in the SQLBEInst2 instance
--
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE
----------------------------------------------------
USE Northwind
GO
-- RegionID = 1 East
CREATE TABLE SalesInfoEast (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 1),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_East
PRIMARY KEY (OrderID, RegionID))
GO
-- RegionID = 2 West
CREATE TABLE SalesInfoWest (
OrderID int NOT NULL,
RegionID int NOT NULL
CHECK (RegionID = 2),
SaleDate datetime,
Amount money,
EmployeeID int,
CONSTRAINT PK_SI_West
PRIMARY KEY (OrderID, RegionID))
GO
--------------------------------------------
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE
--
-- Execute from here in the SQLBE instance
-- and make sure that MS-DTC is running
--
-- NOTE NOTE NOTE NOTE NOTE NOTE NOTE NOTE
--------------------------------------------
USE Northwind
GO
SET XACT_ABORT ON
GO
-- Populate the new table with information
-- from the same table in the SQLBE instance
INSERT SQLBEInst2.Northwind.dbo.SalesInfoEast
SELECT *
FROM SalesInfoEast
INSERT SQLBEInst2.Northwind.dbo.SalesInfoWest
SELECT *
FROM SalesInfoWest
GO
-- Create a View that gets the entire
-- SalesInfo informations
-- Note the use of UNION ALL
CREATE VIEW DistSalesInfo
AS
SELECT *
FROM Northwind.dbo.SalesInfoNorth
UNION ALL
SELECT *
FROM Northwind.dbo.SalesInfoSouth
UNION ALL
SELECT *
FROM SQLBEInst2.Northwind.dbo.SalesInfoEast
UNION ALL
SELECT *
FROM SQLBEInst2.Northwind.dbo.SalesInfoWest
GO
SELECT COUNT(*) AS NorthBefore
FROM SalesInfoNorth
SELECT COUNT(*) AS EastBefore
FROM SQLBEInst2.Northwind.dbo.SalesInfoEast
UPDATE DistSalesInfo
SET RegionID = 1
WHERE OrderID = 10602
SELECT COUNT(*) AS NorthAfter
FROM SalesInfoNorth
SELECT COUNT(*) AS EastAfter
FROM SQLBEInst2.Northwind.dbo.SalesInfoEast
GO
NorthBefore
-----------
147
EastBefore
-----------
417
NorthAfter
-----------
146
EastAfter
-----------
418
What's Next?
In this chapter, you learned how to work with data from different instances, different servers, or even different environments.
To execute the exercises in this chapter, review Appendix A, "Using SQL Server Instances," where you can learn how to set up multiple SQL Server 2000 instances in the same server.
In this book, we tried to show you how to use SQL Server 2000 from a database developer's point of view. Now you can try to apply these techniques to your own database environment.
You can obtain support and updated code from this book on
http://www.sqlserverbyexample.com
You can find extra SQL Server support in the Microsoft SQL Server public newsgroups, where Microsoft SQL Server engineers, SQL Server Most Valuable Professionals (MVP), and many SQL Server professionals try every day to learn a bit more about SQL Server and share their knowledge with their colleagues:
news://msnews.microsoft.com/microsoft.public.sqlserver.ce news://msnews.microsoft.com/microsoft.public.sqlserver.clients news://msnews.microsoft.com/microsoft.public.sqlserver.clustering news://msnews.microsoft.com/microsoft.public.sqlserver.connect news://msnews.microsoft.com/microsoft.public.sqlserver.datamining news://msnews.microsoft.com/microsoft.public.sqlserver.datawarehouse news://msnews.microsoft.com/microsoft.public.sqlserver.dts news://msnews.microsoft.com/microsoft.public.sqlserver.fulltext news://msnews.microsoft.com/microsoft.public.sqlserver.mseq news://msnews.microsoft.com/microsoft.public.sqlserver.odbc news://msnews.microsoft.com/microsoft.public.sqlserver.olap news://msnews.microsoft.com/microsoft.public.sqlserver.programming news://msnews.microsoft.com/microsoft.public.sqlserver.replication news://msnews.microsoft.com/microsoft.public.sqlserver.security news://msnews.microsoft.com/microsoft.public.sqlserver.server news://msnews.microsoft.com/microsoft.public.sqlserver.setup news://msnews.microsoft.com/microsoft.public.sqlserver.tools news://msnews.microsoft.com/microsoft.public.sqlserver.xml news://msnews.microsoft.com/microsoft.public.ae.arabic.sqlserver news://msnews.microsoft.com/microsoft.public.de.sqlserver news://msnews.microsoft.com/microsoft.public.es.sqlserver news://msnews.microsoft.com/microsoft.public.espanol.sqlserver.administracion news://msnews.microsoft.com/microsoft.public.espanol.sqlserver.olap news://msnews.microsoft.com/microsoft.public.fr.sqlserver news://msnews.microsoft.com/microsoft.public.il.hebrew.sqlserver news://msnews.microsoft.com/microsoft.public.jp.sqlserver.server