Microsoft Transaction Server FAQ: Databases and Transactions

Microsoft Corporation

November 26, 1997

Contents

Databases and Microsoft Transaction Server
Using an Oracle Database with Microsoft Transaction Server
Transactions
Resource Dispenser and Resource Managers
Data Access Methods
ODBC Drivers
ODBC Connection Pooling
Transaction Processing Monitors and Microsoft Transaction Server
Message Queuing Systems and Microsoft Transaction Server
Transaction Internet Protocol (TIP)
X/Open DTP and XA
OLE Transactions

Databases and Microsoft Transaction Server

What benefits does Microsoft Transaction Server provide for ODBC compliant databases?

Microsoft® Transaction Server (MTS) provides the following benefits to Open Database Connectivity (ODBC compliant) databases:

• Database connection pooling

  The system maintains a pool of preallocated database connections. When an application component opens a database connection, the system locates a free connection in the pool and allocates it to the component. When the application closes a database connection the connection is returned to the pool. This makes opening and closing database connections faster and less expensive.

• Automatic transaction enlistment

  When a transactional application component opens a database connection, Microsoft Transaction Server automatically enlists the database connection in the component's current transaction. This makes application development easier. The application benefits from transactions with less application programming.

• Transparent access to databases on NT, UNIX, and other platforms

  An application can update databases residing on Microsoft Windows NT® operating system, UNIX, IBM AS/400, IBM MVS, Tandem, and other systems. The location of the database is transparent to the application developer. This is possible because the application opens a database connection by specifying a ODBC DSN. The DSN specifies the name and location of the database. The DSN permits the application located on the Windows NT system to transparently communicate with databases distributed across a wide variety of platforms.

• Distributed transactions

  All of the databases that an application accesses can be updated under the control of a single atomic transaction. If the transaction commits, all of the application's updates are made permanent in all of the databases. If the transaction aborts, all of the application's updates are rolled back in all of the databases. This is true even when the databases are distributed across a collection of Windows NT, UNIX, IBM AS/400, IBM MVS, Tandem, and other systems.

Which databases fully support Microsoft Transaction Server now?

The following databases now fully support Microsoft Transaction Server. This includes the ability to participate in distributed transactions.

Which databases will support Microsoft Transaction Server in the future?

The following databases will fully support Microsoft Transaction Server in the future. This includes the ability to participate in distributed transactions.

1. A transactional Microsoft Transaction Server application on Windows NT can connect to an IBM DB/2 database on Windows NT over TCP/IP, SNA, NETBIOS, or SPX/IPX.
2. A transactional Microsoft Transaction Server application on Windows NT can connect to an IBM DB/2 database on IBM's AIX over TCP/IP, SNA, or SPX/IPX.
3. A transactional Microsoft Transaction Server application on Windows NT that wishes to connect to an IBM DB/2 database on AS/400 or MVS must go through a gateway running on either OS/2 or AIX.  The Windows NT system can connect to the gateway over TCP/IP, SNA, NETBIOS, or SPX/IPX. The gateway system can only connect to AS/400 or MVS over SNA.

In a future release, IBM may make it possible for a Microsoft Transaction Server application running on Windows NT to connect directly to an AS/400 or MVS system over either TCP/IP or SNA.

Which databases partially support Microsoft Transaction Server?

The following databases partially support Microsoft Transaction Server. The notes indicate the restrictions that apply.

Which databases do not support Microsoft Transaction Server?

The following databases do not support Microsoft Transaction Server.

What must an ODBC compliant database do to support Microsoft Transaction Server?

An ODBC compliant database must do the following to support Microsoft Transaction Server:

1. It must provide an ODBC driver on  Microsoft Windows NT or Windows 95® operating systems.
   
   
2. The ODBC driver must be thread-safe and not require thread affinity.
   
   
3. The ODBC driver must support the SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) call that enlists the ODBC connection in the component's current transaction.
   
   
4. The database must support either the XA or the OLE Transactions standard for transaction coordination.

For databases that do not support transactions, only the first two requirements apply.

What must other databases do to support Microsoft Transaction Server?

A database that provides an interface other than ODBC must do the following to support Microsoft Transaction Server:

1. The database should include a resource dispenser that pools database connections. This makes it much cheaper for Microsoft Transaction Server components to connect to and disconnect from the database.
   
   
2. The resource dispenser must be thread-safe and not require thread affinity.
   
   
3. The resource dispenser should automatically enlist database connections in the component's current transaction.
   
   
4. The database must support either the XA or the OLE Transactions standard for transaction coordination.

For databases that do not support transactions, only the first two requirements apply.

What is Microsoft doing to help database vendors integrate their products with Microsoft Transaction Server?

We are doing a great deal to assist database and ODBC driver vendors to support Microsoft Transaction Server. We have contacted all of the leading database and ODBC-driver vendors and offered the following assistance:

1. We provide on-site technical briefings on Microsoft Transaction Server.
   
   
2. We provide prerelease versions of Microsoft Transaction Server at no charge.
   
   
3. We offer free on-site and remote technical assistance from the Microsoft Transaction Server development team.
   
   
4. We provide the Microsoft Transaction Server Software Development Kit (SDK) release. The SDK includes the Microsoft Transaction Server software and documentation needed to integrate a database with Microsoft Transaction Server.
   
   
5. We collaborate on product testing.

Can I access databases on UNIX and other platforms and can these databases participate in transactions?

Yes, an application will be able to access databases distributed across a mixture of Windows NT, UNIX, IBM AS/400, IBM MVS, Tandem and other systems. This is possible because an ODBC driver on one system can communicate with a database server running on another system. This is a standard feature of most ODBC drivers.

If the ODBC driver and database support transactions, all of the work done on behalf of the application component will be performed under the control of a single atomic distributed transaction. The Microsoft Distributed Transaction Coordinator running on the Windows NT system will coordinate the transaction.

If I access databases on UNIX and other platforms using transactions, must Microsoft Distributed Transaction Coordinator be running on those platforms?

A Microsoft Transaction Server transaction can include databases on a mixture of Windows NT systems with UNIX systems, IBM AS/400, IBM MVS systems, Tandem systems, and the like. Those systems need not be running Microsoft Distributed Transaction Coordinator.

This is possible because the Distributed Transaction Coordinator running on the Windows NT system acts as the transaction coordinator. The Microsoft Distributed Transaction Coordinator communicates with the ODBC driver running on the Windows NT system to tell it the outcome of the transaction. The ODBC driver relays this information to the database on the remote UNIX, AS/400, MVS, Tandem, or other system. The database then commits or aborts the transaction as necessary. There is no need to have a Distributed Transaction Coordinator on the system containing the database server.

If I access databases on UNIX and other platforms using transactions, must Tuxedo, TopEnd, or Encina be running on those platforms?

You do not need Tuxedo, TopEnd, or Encina on the other platforms.

Distributed Transaction Coordinator on Windows NT is acting as the transaction manager. Distributed Transaction Coordinator on Windows NT communicates with the ODBC driver running on the Windows NT system to tell it the outcome of the transaction. The ODBC driver communicates this information to the database on the UNIX or other system. The database then commits or aborts the transaction as necessary. No transaction manager is required on the system containing the database server.

Can an Access database be used with Microsoft Transaction Server?

Microsoft Access databases can be read and updated from Microsoft Transaction Server components.

Access does not support distributed transactions; hence any Microsoft Transaction Server component that reads or updates an Access database must be marked "Does not support transactions."

When using Microsoft Access with Microsoft Transaction Server you must use the version 3.51 or later of the Access Driver. Earlier versions of the Access driver did not support connection pooling.

You may use the following data access methods with Microsoft Access.

You cannot use DAO with Microsoft Transaction Server because when DAO is given incomplete or incorrect information about an ODBC data source, Access displays a dialog box to prompt the user for the missing or incorrect information. This dialog box may cause the application to hang if it is running on an unattended server or if it does not have desktop access.

Access could avoid this problem by calling SQLDriverConnect with the SQL_DRIVER_NOPROMPT flag. This flag prevents dialog boxes from being displayed. However, Access does not currently set this flag

ODBC connection pooling only works when the SQL_DRIVER_NOPROMPT flag is set; therefore, Microsoft Transaction Server denies a connection to any component that does not use this flag.

Can a Microsoft Visual FoxPro® database be used with Microsoft Transaction Server?

Visual FoxPro databases can be read and updated from Microsoft Transaction Server components.

Visual FoxPro does not support distributed transactions; hence any Microsoft Transaction Server component that reads or updates a Visual FoxPro database must be marked "Does not support transactions."

Result sets are not updateable in the Visual FoxPro ODBC driver.  That means that while you can get a scrollable cursor with the Visual FoxPro ODBC driver, it will not be updateable.  You can send SQL Update statements to the source tables, and so on.

You may use the following data access methods with Visual FoxPro.

Using an Oracle Database with Microsoft Transaction Server

Can I use an Oracle database with MTS?

Yes, the Microsoft Transaction Server 2.0 release supports Oracle databases.

Can an Oracle database participate in Microsoft Transaction Server transactions?

Yes, Oracle databases can participate in Microsoft Transaction Server transactions. This is possible because Oracle 7.3.3 for Windows NT supports XA and we have enhanced the Microsoft Oracle ODBC driver to work with Microsoft Transaction Server.

What versions of Oracle work with MTS?

Oracle 7.3.3 is the first release of Oracle that supports transactions with Microsoft Transaction Server because it is the first release of Oracle on Windows NT that supports XA.

Microsoft Transaction Server also works with Oracle 8. You must access Oracle 8 using the Oracle 7.3.3 client. Microsoft Transaction Server does not currently support Oracle 8 clients.

Microsoft Transaction Server also works with Oracle Parallel Server.

What ODBC drivers can I use with an Oracle database?

You must use the Microsoft Oracle ODBC Driver supplied with Microsoft Transaction Server release 2.0. It is currently the only Oracle ODBC driver that works with Microsoft Transaction Server.

Does ODBC connection pooling work with Oracle databases?

Yes, connection pooling works with Oracle databases. Connection pooling is built into the ODBC 3.0 and 3.5 Driver Managers; therefore, connection pooling works provided the Oracle ODBC Driver you use is thread-safe. Oracle database connections are pooled when you use the Microsoft Oracle ODBC Driver.

Can I access Oracle databases on UNIX and other platforms and can these databases participate in transactions?

Yes, you can access Oracle databases on UNIX and other platforms and these Oracle databases can participate in transactions. For example, you can update a Microsoft SQL Server database on one Windows NT system, an Oracle database on another Windows NT system, and an Oracle database on a UNIX system under a single atomic transaction. If the transaction commits, all three databases are updated. If the transaction aborts, all work done on all three databases is backed out. Microsoft Transaction Server is intended to interoperate with any Oracle platform accessible from Windows NT or Windows 95.

During the Microsoft Transaction Server 2.0 beta program, Microsoft Transaction Server was used with Oracle databases on the following platforms:

• Digital UNIX
  
  
• HP/UX
  
  
• IBM AIX
  
  
• Windows NT
  
  
• Sun Solaris

If I access Oracle databases on UNIX and other platforms using transactions, must Microsoft Distributed Transaction Coordinator be running on those platforms?

A Microsoft Transaction Server component can update Oracle databases on both Windows NT and other systems including UNIX. All of the updates made by the component can be performed under the control of a single distributed atomic transaction. The other systems need not be running Microsoft Distributed Transaction Coordinator.

This is possible because the Distributed Transaction Coordinator running on the Windows NT system acts as the transaction coordinator. The Microsoft Distributed Transaction Coordinator communicates with the Microsoft Oracle ODBC driver running on the Windows NT system to tell it the transaction's outcome. The ODBC driver relays this information to the Oracle database on the UNIX or other system. The Oracle database then commits or aborts the transaction as necessary. There is no need to have a Distributed Transaction Coordinator on the system containing the Oracle database.

If I access Oracle databases on UNIX and other platforms using transactions, must Tuxedo, TopEnd, or Encina be running on those platforms?

You do not need Tuxedo, TopEnd, or Encina on the other platforms.

Microsoft Distributed Transaction Coordinator on Windows NT is acting as the transaction manager. Distributed Transaction Coordinator on Windows NT communicates with the Microsoft Oracle ODBC driver running on the Windows NT system to tell it the outcome of the transaction. The ODBC driver communicates this information to the Oracle database on the UNIX or other system. The Oracle database then commits or aborts the transaction as necessary. No Tuxedo, TopEnd, or Encina transaction manager is required on the UNIX or other system.

What data access methods work with Oracle?

The following data access methods work with Oracle:

Can I use Oracle's OCI with Microsoft Transaction Server?

You can use Oracle's OCI interfaces with Microsoft Transaction Server but Oracle does not provide a resource dispenser for OCI. As a result, OCI connections are not pooled and OCI connections cannot be enlisted in Microsoft Transaction Server transactions.

Transactions

When does Microsoft Transaction Server automatically initiate a transaction?

Microsoft Transaction Server initiates a transaction when an application component marked as "Requires a transaction" is first invoked, provided the component's caller is not already part of a transaction. If the caller is already part of a transaction, then the called component will join the caller's transaction.

A new transaction is always initiated when an application component is invoked that is marked as "Requires a new transaction."

When Microsoft Transaction Server initiates a transaction on behalf of a component, it records the transaction in the component's object context.

How does the transaction get propagated to the database?

The ODBC Driver Manager and the ODBC Driver are responsible for propagating the transaction to the database. When an application component opens a database connection, the ODBC Driver Manager is invoked. The ODBC Driver Manager checks the component's object context to see whether the component is participating in a transaction. If so, the ODBC Driver Manager calls the ODBC Driver's SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) procedure passing it the transaction. The ODBC Driver passes the transaction to the database server. If the database supports OLE Transactions, then the OLE transaction is passed directly to the database server.

If the database supports XA, then the ODBC Driver converts the OLE transaction identifier into an XA Transaction Identifier (XID) with help from Microsoft Distributed Transaction Coordinator. The XID is then passed to the database server using the XA protocol.

How does the transaction get propagated to other application components?

When a transactional component invokes another component that is marked "Requires a transaction" or "Supports transactions," Microsoft Transaction Server automatically propagates the transaction from the calling component's object context to the called component's object context. This is true whether the called component is in the same package as its caller, in a different package on the same machine, or is in a package on a remote machine.

When does Microsoft Transaction Server automatically commit or abort a transaction?

Microsoft Transaction Server commits or aborts the transaction when the component that triggered creation of the transaction completes its work. The component signals that its work is complete by calling either SetComplete or SetAbort. SetComplete causes the transaction to commit; SetAbort causes the transaction to abort.

Microsoft Transaction Server will also commit or abort the transaction if the client releases its last reference to the component.

The following rules summarize when a transaction is committed or aborted.

• If the component calls SetComplete, the transaction commits as soon as the method call returns to the client, even if the client maintains a reference to the component.
  
  
• If the component calls SetAbort, the transaction aborts as soon as the method call returns to the client, even if the client maintains a reference to the component.
  
  
• If the component calls DisableCommit, the transaction aborts when the client releases its last reference to the component.
  
  
• If the component calls EnableCommit, the transaction commits when the client releases its last reference to the component.

If the component never calls SetAbort, SetComplete, DisableCommit, or EnableCommit, then the transaction commits when the client releases its last reference to the MTS component.

Can my application access the transaction identifier located in the object context?

No, the application component cannot access the transaction identifier in the object context.

Why does Microsoft Transaction Server support automatic transactions?

Automatic transactions make it much easier to develop reliable component-based applications.

When using Microsoft Transaction Server, the programmer writes very little code that manages transactions. MTS automatically begins a transaction on behalf of an application component based upon the components' transaction property. Application programmers use the data access methods they are familiar with including ADO, RDO, OLE DB, JDBC, and ODBC. The underlying resource dispensers, such as the ODBC Driver Manager, transparently enlist the resources they manage in the component's transaction. When each component completes its work, it informs Microsoft Transaction Server whether it wishes to commit or abort the transaction. When the final component completes its work, Microsoft Transaction Server determines the outcome of the transaction. If all of the components that participated in the transaction signified that the transaction should commit, then Microsoft Transaction Server will commit the transaction. If any component signified that the transaction should abort, Microsoft Transaction Server aborts the entire transaction.

Automatic Transactions make it easy to compose applications from independently authored components. Each component is marked to indicate its transaction requirements. Microsoft Transaction Server automatically initiates transactions and propagates transactions from component to component to satisfy these requirements. If any component that is participating in the transaction cannot commit the transaction, Microsoft Transaction Server ensures that the entire transaction is aborted. This makes it possible to build an application from components while allowing each component to enforce its portion of the business rules.

Can an application client explicitly initiate and control transactions?

Microsoft Transaction Server supports both client-controlled transactions and automatic transactions. A client may explicitly control transactions by creating a Transaction Context Object. The client may then call the transaction context object's CreateInstance method to invoke subordinate components. Microsoft Transaction Server will start a transaction, associate it with the transaction context object, and propagate the transaction to each of the called components that is marked either "Requires a transaction" or "Supports transactions." When the client is ready to commit the transaction, it calls the transaction context object's Commit method. Alternatively, the client can abort the transaction by calling the transaction context object's Abort method.

Although Microsoft Transaction Server supports client-controlled transactions, we encourage component developers to allow Microsoft Transaction Server to automatically initiate and manage transactions. There are two reasons for allowing Microsoft Transaction Server to manage transactions automatically.

Applications are usually easier to write if Microsoft Transaction Server controls transactions automatically. Each component is marked to indicate whether it requires a transaction. When a component completes its work, it signifies whether the transaction should commit or abort. Microsoft Transaction Server determines the transaction outcome based upon the responses it receives from all of the components participating in the transaction. The application developer writes very little code for managing transactions.

Another benefit of automatic transactions is that the transactions are controlled at the server rather than at the client. Clients are often running on unreliable systems. Servers are normally running on more reliable and carefully managed systems. It is better to initiate and control the transaction from these more reliable server systems. Controlling the transaction from the server may also be more efficient because it reduces the number of network messages.

Can Microsoft Distributed Transaction Coordinator manage transactions involving UNIX, IBM MVS, IBM AS/400, and other systems?

Microsoft DTC can coordinate transactions distributed across a wide variety of heterogeneous systems. These systems may be running databases, message queuing systems, and transaction processing monitors.

In the case of databases, an application will be able to access databases distributed across a mixture of Windows NT, UNIX, IBM AS/400, IBM MVS, Tandem, and other systems. This is possible because an ODBC driver on one system can communicate with a database server running on another system. This is a standard feature of most ODBC drivers.

If the ODBC driver and database support transactions, all of the work done on behalf of the application will be performed under the control of a single atomic distributed transaction. The Microsoft Distributed Transaction Coordinator running on the Windows NT system will coordinate the transaction.

Microsoft Message Queue (MSMQ) supports transactional message queuing.

Microsoft's COM Transaction Integrator (COMTI) for CICS & IMS provides transactional access to IBM's CICS over the SNA LU 6.2 sync level 2 protocol. CICS can fully participate in a distributed transaction controlled by Microsoft Transaction Server.

If a transaction aborts, what happens to the updates an application makes to databases on NT, UNIX, AS/400, MVS, or other systems?

If the transaction aborts, its effects will be backed out on every system participating in the transaction.

Can Microsoft SQL Server transactions be used with Microsoft Transaction Server transactions?

Components can call BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION inside a transactional MTS component, although this is not recommended.

Calls to BEGIN TRANSACTION are ignored, as are calls to COMMIT TRANSACTION.

Calls to ROLLBACK TRANSACTION will abort the component's transaction even if the component calls SetComplete.

Components must not call BEGIN DISTRIBUTED TRANSACTION, PREPARE TRANSACTION, or SAVE TRANSACTION inside a transactional MTS component.

Can I use explicit ODBC transactions with Microsoft Transaction Server transactions?

Calls to SQLTransact are disabled while in a Microsoft Transaction Server transaction.  Microsoft Transaction Server will report the error "Cannot use manual transactions while in a component transaction." if a transactional component attempts to call SQLTransact.

Similarly, calls to SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) are disallowed.

Can I obtain MTS Transaction statistics programmatically?

At present there is no easy way to obtain transactions statistics programmatically.

You can monitor the state of individual transactions using the Microsoft Transaction Server Event Sinks facility described in the Microsoft Transaction Server SDK. The IMTSTxEvents interface's OnTransactionStart, OnTransactionPrepare, and OnTransactionAbort methods are called when the state of a transaction changes. The Microsoft Transaction Server Spy sample application illustrates how Microsoft Transaction Server Event Sinks can be used.

Resource Dispensers and Resource Managers

What is a resource dispenser?

A Microsoft Transaction Server resource dispenser does two things. It manages a pool of connections to the resource manager and it automatically enlists the resource manager in the component's current transaction.

The ODBC Driver Manager is a typical resource dispenser. It manages a pool of ODBC database connections and enlists the database connections in the component's current transaction. Application components use standard ODBC API calls to use the ODBC resource dispenser.

• Connection Pool Management

  Connection pooling makes it cheaper for short-lived components to connect and disconnect to resource managers.

  Application components call the resource dispenser when they need a resource manager connection. The resource dispenser searches its pool of already open resource manager connections looking for a connection that meets the component's needs. If it finds a satisfactory connection, the resource dispenser assigns the connection to the component. If the resource dispenser cannot find a satisfactory existing connection, it opens a new one. When the component releases the connection, the resource dispenser returns the connection to the pool for use by another component. If a connection goes unused for a period of time, the resource dispenser closes the connection and removes it from the pool. The system administrator can configure this connection timeout period.

  Resource dispensers often provide administrative interfaces for controlling the connection pool. For example, the resource dispenser may allow the administrator to control the minimum and maximum connection pool size and the timeout for closing unused connections.

• Automatic Transaction Enlistment

  The resource dispenser automatically enlists the resource manager connection in the component's current transaction. The resource dispenser does this by obtaining the current transaction from the component's object context and sending it to the resource manager. By doing this, the resource dispenser makes transaction enlistment automatic for the application.

What is a resource manager?

A Resource Manager is a database, file system, message queuing system, or the like that provides access to shared resources. The prototypical resource manager is a relational database.

A Transactional Resource Manager allows the data it manages to be updated under transaction control.

How does a resource dispenser relate to a resource manager?

A resource dispenser manages the connections to a resource manager.

What resource dispensers does Microsoft provide?

Microsoft currently provides three resource dispensers.

1. The ODBC Driver Manager is a resource dispenser for managing ODBC connections.
   
   
2. MSMQ includes a resource dispenser for enlisting MSMQ queues in Microsoft Transaction Server transactions.
   
   
3. COM Transaction Integrator for CICS & IMS includes a resource dispenser for managing SNA connections to IBM CICS and IMS.

What is the Microsoft ODBC resource dispenser?

The ODBC resource dispenser is a very thin layer implemented in the ODBC Driver Manager. When your application component makes what looks like a standard ODBC call to connect to a database, the ODBC resource dispenser intercepts the connect call and does two things.

First, it looks in the pool of existing ODBC connections and hands out an existing ODBC connection, if one is present that meets the application component's requirements. This makes it very cheap for components that come and go relatively rapidly to connect to the database. It also reduces the number of required ODBC connections because the connections are shared and reused.

The second thing that the resource dispenser does is to automatically enlist the ODBC connection in the component's current transaction. The ODBC resource dispenser obtains the current transaction from the component's object context. MTS automatically maintains this object context on behalf of the component. The resource dispenser then enlists the ODBC connection in the transaction. By doing this, the resource dispenser makes transactions automatic for the application component.

Can I build a resource dispensers?

Yes, you can build a resource dispenser. You may wish to do so if you manage a resource that requires either connection pooling or automatic transaction enlistment.

Note that building a resource dispenser is more difficult than building a Microsoft Transaction Server component that uses a resource dispenser because a resource dispenser must be multi-threaded and thread safe.

The interfaces for building a resource dispenser are not yet fully documented and some of the necessary header files are not provided in the SDK. If you wish to build a resources dispenser you must contact Microsoft for assistance.

How can I learn more about resource dispensers?

The Microsoft Transaction Server SDK includes a "Resource Dispenser Guide" that contains more information about resource dispensers. To obtain the "Resource Dispenser Guide," install the Microsoft Transaction Server SDK and open the file C:\WINNT40\system32\SDK\Docs\mtxsdk.hlp

How can I obtain the Microsoft Transaction Server SDK?

See the Microsoft Transaction Server pages of the Microsoft COM Web site (http://www.microsoft.com/com/mts.htm) for more information about how to obtain the MTS SDK.

Data Access Methods

What data access methods fully support Microsoft Transaction Server?

The following data access methods fully support Microsoft Transaction Server. They support both connection pooling and automatic transaction enlistment.

What data access methods do not fully support Microsoft Transaction Server?

The following data access methods either do not support Microsoft Transaction Server or support it with limitations.

Can I invoke stored procedures from Microsoft Transaction Server?

Yes, you can invoke stored procedures from Microsoft Transaction Server. The work done by the stored procedure will be full transaction protected provided the calling component is transactional and the data access method and database support Microsoft Transaction Server transactions.

ODBC Drivers

What must an ODBC driver do to support Microsoft Transaction Server?

ODBC drivers do not automatically work with Microsoft Transaction Server, but they can be enhanced to work with Microsoft Transaction Server. To fully support Microsoft Transaction Server:

1. The ODBC driver must run on Windows NT or Windows 95.
   
   
2. The ODBC driver must be as 32-bit driver. It must be fully thread-safe and it must not require thread affinity.  That is, the driver must be able to handle an ODBC call from any thread at any time. For example, it must be possible to connect to the database from a call on one thread, to use the database connection from another thread, and to disconnect from the database from another thread. This is essential because the ODBC driver manager opens and closes database connections from a system thread, while application components may call the database from any of a number of application threads.

   If you attempt to use a non-thread-safe ODBC driver with Microsoft Transaction Server 1.0 or 1.1, a memory access violation may occur. This is most likely to occur after a database connection has been inactive for 60 seconds and the driver manager attempts to close the database connection.

   In the Microsoft Transaction Server 2.0 release, the Resources Dispenser Manager catches this exception and logs the message:

   An exception occurred within a Resource Dispenser
   :Dispenser=[ODBC env...]  Method=IDispenserDriver::<method name>
   

   Where <method name> is CreateResource, RateResource, EnlistResource, or DestroyResource.

3. The ODBC driver must support the new ODBC 3.0 SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) call. The ODBC driver manager uses this call to pass the component's current transaction to the ODBC driver. The ODBC driver then passes the transaction to the database server.

   Microsoft Transaction Server, the ODBC Driver Manager, and the ODBC driver work together to make transactions automatic and transparent to the application. This automatic transaction capability is an important feature of Microsoft Transaction Server. It makes applications more robust while making application development easier.

4. The database server must support either X/Open XA or the OLE Transactions two-phase commit protocol.

   If the database server does not support the OLE Transaction two-phase commit protocol, the ODBC driver must translate OLE Transaction calls into the corresponding XA two-phase commit messages. The ODBC driver sends these XA two-phase commit messages to the database server. This permits the Microsoft Distributed Transaction Coordinator to perform the two-phase commit protocol with the database server.

What versions of the ODBC Driver Manager support Microsoft Transaction Server?

ODBC 3.0 and ODBC 3.5 Driver Managers support Microsoft Transaction Server.

Which ODBC drivers currently support Microsoft Transaction Server?

The following ODBC drivers currently support Microsoft Transaction Server.

Which ODBC drivers will support Microsoft Transaction Server?

The following ODBC driver providers have said that they will support Microsoft Transaction Server in the future.

What independent ODBC driver vendors are you working with to support Microsoft Transaction Server?

We have talked with the following independent ODBC driver vendors:

ODBC Connection Pooling

What is ODBC connection pooling?

ODBC connection pooling is a feature that is implemented by the ODBC Driver Manager. Connection pooling makes it much cheaper for an application to open and close a database connection. Once a connection has been created and placed in a connection pool, an application can reuse that connection without paying the cost of opening a new database connection.

When connection pooling is enabled, the ODBC driver manager maintains a "pool" of available ODBC connections. When an application opens a database connection, the driver manager searches the pool to locate an available connection that meets the application's requirements. If it finds a suitable connection, the driver manager assigns it to the application. By reusing an existing database connection, the driver manager avoids the cost of establishing a new connection. If no suitable database connection is found, the driver manager opens a new database connection. When the application releases the database connection, the driver manager returns the connection to the pool.

Using a pooled connection can result in significant performance gains, because applications can save the overhead involved in making a connection. This can be particularly significant for middle-tier applications that connect over a network, or for applications that repeatedly connect and disconnect, such as Internet applications or Microsoft Transaction Server application components.

The connection pooling architecture enables an environment and its associated connections to be used by multiple components in a single process. This means that standalone components in the same process can share database connections without being aware of each other. A connection in a connection pool can be used repeatedly by many components.

The Driver Manager maintains the connection pool. Connections are drawn from the pool when the application calls SQLConnect or SQLDriverConnect, and are returned to the pool when the application calls SQLDisconnect. The size of the pool grows dynamically based upon the requested resource allocations. It shrinks based on the inactivity timeout. If a connection is not reused before the timeout period expires, the connection is closed and removed from the pool. The size of the pool is limited only by the amount of memory available on the server.

Is connection pooling supported for all ODBC drivers?

Connection pooling is a standard feature of the ODBC 3.0 and 3.5 Driver Managers and it can be used with any 32-bit ODBC driver that is thread-safe.

To support connection pooling, an ODBC driver must be thread-safe and must not require thread affinity. That is, the driver must be able to handle an ODBC call from any thread at any time. For example, it must be possible to connect to the database from a call on one thread, to use the database connection from another thread, and to disconnect from the database from another thread. This is essential because the ODBC driver manager opens and closes database connections from one thread, while application components may call the database from any of a number of other threads.

If you attempt to use a non-thread-safe ODBC driver with the ODBC 3.0 driver manager, a memory access violation may occur. This is most likely to occur after a database connection has been inactive for 60 seconds and the driver manager attempts to close the database connection.

The ODBC 3.5 driver manager catches this exception and logs the message:

"Exception occurred within ODBC driver <driver name> during disconnect operation, likely not thread-safe ..."

Can applications use connection pooling if they do not use Microsoft Transaction Server?

Yes, connection pooling is a standard feature of the ODBC 3.0 and 3.5 Driver Managers. It can be used whether or not your application uses Microsoft Transaction Server. You must do two things to use connection pooling without using Microsoft Transaction Server. You must enable connection pooling when configuring the driver and your application program must make the appropriate ODBC calls.

Configuring the ODBC Driver to Support Connection Pooling

For the ODBC 3.5 Driver Manager, connection pooling is controlled on a driver by driver basis through the CPTimeout registry setting. If this registry entry is not present, connection pooling is disabled. To enable connection pooling for a specific driver, create the following registry entry:

HKLM\Software\ODBC\ODBCINST.INI\<driver name>\[CPTimeout]

Where CPTimeout is a string-value that specifies the timeout in seconds.

For the ODBC 3.0 Driver Manager, connection pooling is enabled by default. It cannot be disabled through the registry.

Enabling Connection Pooling in the Application

Non-Microsoft Transaction Server applications must perform the following steps to use connection pooling:

1. The application enables connection pooling by calling SQLSetEnvAttr to set the SQL_ATTR_CONNECTION_POOLING environment attribute to SQL_CP_ONE_PER_DRIVER or SQL_CP_ONE_PER_HENV. This call must be made before the application allocates the shared environment for which connection pooling is to be enabled. The environment handle in the call to SQLSetEnvAttr should be set to null, which makes SQL_ATTR_CONNECTION_POOLING a process-level attribute.

   If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_ONE_PER_DRIVER, a single connection pool is supported for each driver, and it is not possible to share connections between environments. If an application works with many drivers and few environments, this may be more efficient because fewer comparisons may be required.

   If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_ONE_PER_HENV, a single connection pool is supported for each environment, and it is possible to share connections between environments. If an application works with many environments and few drivers, this may be more efficient because fewer comparisons may be required.

   If the SQL_ATTR_CONNECTION_POOLING attribute is set to SQL_CP_OFF connection pooling is disabled.

2. The application allocates an environment by calling SQLAllocHandle with the HandleType argument set to SQL_HANDLE_ENV. The environment allocated by this call will be an implicit shared environment because connection pooling has been enabled. The environment to be used is not determined, however, until SQLAllocHandle with a HandleType of SQL_HANDLE_DBC is called on this environment.
   
   
3. The application allocates a connection by calling SQLAllocHandle with InputHandle set to SQL_HANDLE_DBC, and the InputHandle set to the environment handle allocated for connection pooling. The Driver Manager attempts to find an existing environment that matches the environment attributes set by the application. If no such environment exists, one is created, with a reference count (maintained by the Driver Manager) of 1. If a matching shared environment is found, the environment is returned to the application, and its reference count is incremented.

   The actual connection to be used is not determined by the Driver Manager until SQLConnect or SQLDriverConnect is called.

4. The application calls SQLConnect or SQLDriverConnect to make the connection. The Driver Manager uses the connection options in the call to SQLConnect (or the connection keywords in the call to SQLDriverConnect) and the connection attributes set after connection allocation to determine which connection in the pool should be used.

   How a requested connection is matched to a pooled connection is determined by the SQL_ATTR_CP_MATCH environment attribute.

5. The application calls SQLDisconnect when it is done with the connection. The connection is returned to the connection pool, and it becomes available for reuse.

Can connection pooling be disabled when using Microsoft Transaction Server?

Microsoft Transaction Server always makes the necessary ODBC calls to enable connection pooling. It does this by calling SQLSetEnvAttr and setting the SQL_ATTR_CONNECTION_POOLING environment attribute.

For the ODBC 3.5 Driver Manager, you control whether connection pooling is used on a driver by driver basis through the CPTimeout registry setting. If this registry entry is not present, connection pooling is disabled. To enable connection pooling for a specific driver, create the following registry entry:

HKLM\Software\ODBC\ODBCINST.INI\<driver name>\[CPTimeout]

Where CPTimeout is a string-value that specifies the timeout in seconds. ODBC connections are handed out in a last-in, first-out (LIFO) order. If a connection goes unused for the CPTimeout period, the database connection is closed and dropped from the pool.

For the ODBC 3.0 Driver Manager, connection pooling is always enabled and cannot be disabled. If the CPTimeout registry entry is present, its value controls the connection timeout value. If no registry entry is present for a driver, a CPTimeout value of 60 seconds is assumed.

You may need to disable connection pooling if your ODBC driver experiences access violations when used with Microsoft Transaction Server. This can occur if your ODBC driver is not fully thread-safe. The access violation occurs when the ODBC driver manager attempts to release a database connection from the pool. The ODBC driver manager may not call on the same thread that originally opened the database connection; this can cause an access violation in the ODBC driver.

What are the consequences of disabling connection pooling?

Microsoft Transaction Server performance suffers when connection pooling is disabled. Whenever a Microsoft Transaction Server component opens a database connection, the system must establish an entirely new database connection. When the component finishes its work and is destroyed, the database connection must be closed. This can be quite expensive because Microsoft Transaction Server components are typically short lived. They only persist for the life of the transaction. Connection pooling is usually essential for good performance.

Can I control the number of ODBC database connections in the connection pool?

There is currently no way to control the number of ODBC database connections in the connection pool. You cannot limit the number of connections in the pool and you cannot preallocate connections.

The connection pool is initially empty. If your application component asks for a database connection and there is no suitable free connection in the pool, the ODBC driver manager will open a new ODBC connection. When your application component closes a database connection, the connection is returned to the pool.

At present, the only mechanism for influencing the number of ODBC connections is the CPTimeout value. It specifies how long an idle database connection remains in the pool before it is closed and removed from the pool.

Can two or more processes share a connection pool?

No, database connections are only pooled within a single process.

How are database connections selected from the connection pool?

When an application component opens a database connection, the ODBC Driver Manager attempts to find an existing connection in the connection pool that matches the criteria requested by the application. These criteria include the connection options requested in the call to SQLConnect (the values of the ServerName, UserName, and Authentication keywords) and any connection attributes set since SQLAllocHandle was called with a HandleType of SQL_HANDLE_DBC. The Driver Manager checks these criteria against the corresponding connection keywords and attributes of the connections in the pool. If a match is found, the connection in the pool is used; otherwise, a new connection is created.

If you wish to learn more about ODBC connection pool management, consult the ODBC 3.0 SDK documentation.

How does implementing security through client impersonation effect connection pooling?

Client impersonation works with connection pooling but it makes connection pooling much less effective. When you use client impersonation, the system must provide a unique connection for each Cser ID. As a result, the connections in the pool are far less interchangeable.

What does the ODBC error message, "Failed to enlist on calling object's transaction," mean?

The ODBC driver manager reports this error when it cannot enlist the database connection in the component's current transaction. This occurs when the ODBC Driver Manager's call to the ODBC driver's SQLSetConnectionAttr (SQL_ATTR_ENLIST_IN_DTC) procedure fails.

If this error occurs check the following:

1. If the database you are connecting to is located on a remote machine, ensure that the two machines can Ping one another.
   
   
2. If you are connecting to a Microsoft SQL Server database located on a remote machine, ensure that the Microsoft DTC service is "Started" on that machine. You can check the status of Microsoft DTC service using the Services Control Panel applet.
   
   
3. If you are connecting to an Oracle database, run the "TestOracleXAConfig" program to ensure that you can successfully connect to the Oracle database without using Microsoft Transaction Server.
   
   
4. Consider whether the component's transaction may have aborted before the database enlistment completed. This may occur if the transaction time out expires before the enlistment completes. You can increase the transaction timeout value through the Microsoft Transaction Server Explorer. To do this, run the Microsoft Transaction Server Explorer and select Computers. Right click on the computer where the transaction was initiated and select Properties. Select the Options tab and modify a longer Timeout value.

Transaction Processing Monitors and Microsoft Transaction Server

Which Transaction Processing Monitors interoperate with Microsoft Transaction Server?

The following transaction processing monitors interoperate with Microsoft Transaction Server.

Can Microsoft Transaction Server interoperate with UNIX TP Monitors such as Tuxedo, TopEnd, Encina, and OpenUTM?

No, Microsoft Transaction Server cannot interoperate with UNIX TP Monitors such as BEA Tuxedo, NCR TopEnd, IBM Encina, and SNI OpenUTM. We have talked with these companies about providing interoperability with Microsoft Transaction Server but none have announced plans to support such interoperability.

Can Microsoft Transaction Server use XA to interoperate with other TP Monitors such as Tuxedo?

No, XA is not a TP-Monitor-to-TP-Monitor interoperability standard. That is not the objective of the XA standard therefore it cannot be used to make Microsoft Transaction Server interoperate with Tuxedo or other transaction processing monitors.

XA is a standard that defines how transaction managers communicate with resource managers. For example, it defines how Tuxedo (a TM) talks with Oracle (a RM). More specifically, it describes how the TM and RM perform the two-phase transaction commit protocol and do transactional recovery.

XA does not define how transaction managers coordinate distributed transactions with other transaction managers. For example, XA does not define how Tuxedo would coordinate distributed transactions with Microsoft Transaction Server, or how TopEnd would coordinate distributed transactions with Encina.

XA does not define how application programs communicate with other application programs. For example, XA does not define how a Tuxedo application would communicate with a Microsoft Transaction Server application, or how a TopEnd application would communicate with an Encina application.

People who are not extremely familiar with the X/Open DTP standard are often confused on this point. They believe that XA defines a standard for interoperability between one transaction processing monitor and another. XA is simply a standard that allows a transaction manager to perform two-phase commit with a resource manager. It allows Tuxedo, TopEnd, and Encina to coordinate transactions with Oracle, Informix, DB2, and Sybase.

Message Queuing Systems and Microsoft Transaction Server

Which message queuing systems support Microsoft Transaction Server?

The following message queuing systems support Microsoft Transaction Server.

Which Message Queuing Systems do not support Microsoft Transaction Server?

The following message queuing systems do not support Microsoft Transaction Server.

Transaction Internet Protocol (TIP)

What is the Transaction Internet protocol (TIP)?

TIP is an Internet Engineering Task Force (IETF) Internet draft that describes a standard two-phase commit protocol. It enables heterogeneous transaction managers to coordinate distributed transactions.

TIP satisfies two key requirements:

• The TIP two-phase commit protocol is independent of the application-to-application communications protocol, such that it may be used with any application protocol but especially Hypertext Transfer Protocol (HTTP).
  
  
• The TIP two-phase commit protocol is simple to implement and it has a small working footprint. This will encourage ubiquitous implementation and offer wide applicability.

What is the status of the TIP standard?

TIP is an Internet Draft.

Who is participating in the TIP standardization effort?

Tandem Computers and Microsoft drafted the TIP proposal. Many vendors are refining and endorsing TIP.

How can I learn more about TIP?

You can obtain the TIP specification in the document "Transaction Internet Protocol Version 2.0," available at the InterNIC Web site (http://ds.internic.net/internet-drafts/draft-lyon-itp-nodes-04.txt).

X/Open DTP and XA

What is the X/Open Distributed Transaction Processing Model?

The X/Open Distributed Transaction Processing (DTP) model is a software architecture that allows application programs to update databases and other transaction protected resources under transaction control. X/Open DTP originated in the UNIX world. It was standardized through the X/Open group.

The X/Open DTP model describes how an application can use transaction processing monitors (TP Monitors) like Tuxedo to update databases like Oracle under transaction control. X/Open DTP is supported by all of the leading UNIX TP Monitors including BEA's Tuxedo, NCR's TopEnd, IBM's Encina, and SNI's OpenUTM. It is also supported by all of the leading UNIX databases including Oracle, Informix, IBM DB2, and Sybase. As these TP Monitors and databases have been ported to Windows NT, the X/Open DTP model has become more important on Windows NT.

The original X/Open DTP model was composed of three functional components and three functional interfaces.

Figure 1. The X/Open DTP Protocol

Application Program (AP)

The Application Program contains the business application logic created by the application developer. The application program defines transaction boundaries through calls it makes to the transaction manager. It controls the operations performed against the application data through the calls it makes to the resource managers.

Resource Managers (RMs)

Resource Managers are databases, files systems, message queuing systems, and the like that provide access to shared resources. The prototypical resource manager is a relational database.

Transaction Manager (TM)

The Transaction Manager creates transactions, assigns transaction identifiers to them, monitors their progress, and coordinates their outcome. The transaction manager is responsible for orchestrating the two-phase commit process.

RM Interface

The application program communicates with the resource manager using the RM interface. Typically the provider of the resource manager defines this interface. For example, an application would normally use SQL statements to communicate with a relational database.

TX Interface

The application program communicates with the transaction manager using the TX interface. The application program uses the TX interface to initiate and control transactions. For example, the application initiates a transaction by calling tx_begin(). It commits the transaction by calling tx_commit(), or aborts the transaction by calling tx_rollback().

XA Interface

The transaction manager and the resource managers communicate using the XA interface. XA calls are used to enlist the resource manager in a transaction, to implement the two-phase commit protocol, and to perform recovery. For example, xa_start enlists an RM in a transaction, xa_prepare initiates phase-one processing, xa_commit initiates phase-two processing, and xa_rollback initiates transaction rollback.

Note that the XA interface is never used by the application program; it is used solely for TM to RM communication.

What is XA and why is it important?

The XA interface is part of the X/Open DTP standard. It defines the interface that transaction managers and resource managers use to communicate. The XA calls are used to enlist the resource manager in a transaction, to implement the two-phase commit protocol, and to perform recovery.

XA is important because it has been broadly adopted. It is supported by the leading UNIX transaction processing monitors including BEA's Tuxedo, NCR's TopEnd, IBM's Encina, SNI's OpenUTM, and the like. It is supported by the leading UNIX databases including Oracle, Informix, IBM's DB/2, and Sybase.

Does Microsoft Transaction Server work with XA compliant resource managers?

Microsoft Transaction Server does not support the X/Open XA interface. However, an XA compliant resource manager can be enhanced to work with Microsoft Transaction Server.

The resource manager's client library must accept OLE Transactions calls in place of XA calls. The client library must translate these OLE Transactions calls into the message formats and protocols that would have resulted had the corresponding XA interfaces been called.

The client library must translate the OLE Transaction Identifier (TRID) into a XA Transaction Identifier (XID). The client library may call a Microsoft provided DTC helper procedure to convert a TRID into a XID.

The resource manager server need not be changed to work with Microsoft Transaction Server. The client library completely hides OLE Transactions from the resource manager. The resource manager only receives XA compliant message flows from its client library. It only sees the XID when communicating with its client library.

Microsoft DTC includes an XA Transaction Manager (XATM) recovery manager. This XATM recovery manager uses standard XA calls to perform recovery.

If you wish to learn more about XA support in Microsoft Transaction Server, consult the Microsoft Transaction Server Software Developers Kit. The section on Resource Dispensers in this document explains how you can obtain the Microsoft Transaction Server SDK.

Figure 2. MTS working with compliant resource managers

Terms

• FAP—The Format and Protocol describes the message formats and the communication protocols used for communication.
  
  
• OLE Tx—OLE Transactions
  
  
• ODBC—Open Database Connectivity
  
  
• DTC—Microsoft Distributed Transaction Coordinator
  
  
• RM—Resource Manager

Can SQL Server and MSMQ be used with XA Transaction Managers like Tuxedo?

Yes, Microsoft SQL Server and MSMQ can be used with XA compliant transaction managers including BEA's Tuxedo, NCR's TopEnd, IBM's Encina, SNI's OpenUTM, and the like.

This is possible because Microsoft Distributed Transaction Coordinator translates each standard XA call into the corresponding OLE Transactions call that SQL Server and MSMQ expect. As a result, SQL Server and MSMQ act as fully XA compliant resource managers.

Figure 3 illustrates how this is done. This capability was included in the Distributed Transaction Coordinator version 1.0 released first distributed with SQL Server 6.5.

Figure 3. Microsoft SQL Server and MSMQ working with an XA Transaction Manager

OLE Transactions

What is OLE Transactions?

OLE Transactions is Microsoft's object-oriented protocol for transaction management. OLE Transactions defines the interfaces that applications, resource managers, and transaction managers use to perform transactions. Applications use OLE Transaction interfaces to initiate, commit, abort, and inquire about transactions. Resource managers use OLE Transaction interfaces to enlist in transactions, to propagate transactions from process to process or from system to system, to participate in the two-phase commit protocol, and to perform recovery.

OLE Transactions takes the place of both the X/Open TX and XA interfaces.

Figure 4. OLE Transactions

Why did Microsoft define OLE Transactions?

We evaluated the X/Open DTP standard but decided that OLE Transactions was required for the following reasons:

• The Microsoft computing model is based upon distributed, transaction-protected, components that communicate using COM interfaces. Our transaction interfaces are object-based to better fit this model.
  
  
• We intend to extend the transaction model to support a wide variety of transaction-protected resources. This includes documents, images, voice, queued messages, workflow and other nondatabase resources. To do this, we must extend the transaction model to provide richer transaction capabilities. OLE Transactions permits us to make these extensions.

Does Microsoft have a Transaction Manager?

Microsoft Distributed Transaction Coordinator is the Microsoft transaction manager. It supports both the OLE Transactions standard and the XA standard for interoperability with resource managers.