Installation Guide › Installing an AIX Implementation › Install and Configure Workpoint Components on AIX/WebSphere › Install Microsoft SQL Server JDBC Drivers and Data Sources on the Workpoint Cluster

Install Microsoft SQL Server JDBC Drivers and Data Sources on the Workpoint Cluster

The Java Database Connectivity (JDBC) API supports server cluster connections to the SQL database.

Distributed transactions are handled using XA data sources with Microsoft Distributed Transaction Coordinator (MS DTC). To allow a specific user to participate in distributed transactions with the JDBC driver, assign the user the SqlJDBCXAUser role for the database.

You must install JDBC XA drivers on all SQL servers, and on the WebSphere application server.

To install Microsoft SQL Server JDBC drivers and data sources on the Workpoint cluster

1. Install the Microsoft SQL JDBC installer on the SQL server.

   You can download the installer, sqljdbc_1.2.2828.100_enu.exu, from the Microsoft Download Center.

2. Enable XA transactions on the SQL server.
   1. Browse to the Control Panel
   2. Click Administrative Tools, Component Services. Right-click My Computer and click Properties.
   3. Click the MSDTC tab and click Security Configuration.
   4. Select the Enable XA Transactions option.
   5. Save changes and restart the SQL server.
3. Copy and install drivers on any other SQL servers:
   1. Locate the JDBC distributed transaction components under the \xa folder of the JDBC driver installation directory.
   2. Copy the file sqljdbc_xa.dll
   3. Paste this file in the %SQL_SERVER_INSTALL%\Binn directory of every SQL Server computer that will participate in distributed transactions.
      • If you are using XA transactions with a 32-bit SQL Server, use the sqljdbc_xa.dll file in the x86 folder, even if the SQL Server is installed on a x64 processor.
      • If you are using XA transactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in the x64 folder.
      • If you are using XA transactions with a 64-bit SQL Server on an IA-64 processor, use the sqljdbc_xa.dll file in the IA64 folder.
   4. Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. This script installs sqljdbc_xa.dll as an extended stored procedure. You must be logged in as an administrator of the SQL Server instance when you run this script.
4. Install drivers on WebSphere.
   1. In the original JDBC installation folder on the SQL server, find the file sqldbc.jar in the directory

      Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.2\enu
      

   2. Copy this file to the WebSphere application server under the directory

      WAS_install_root/essentials/JDBC/
      

      Note: WAS_install_root is the WebSphere Application Server installation directory.

5. In the WebSphere Application Server administration console, select Resources, JDBC, JDBC Providers and create JDBC providers with the following settings:
   • For the server provider:

     Name: ServerProvider

     Implementation Class: com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource

   • For the server XA provider:

     Name: ServerXAProvider

     Implementation Class: com.microsoft.sqlserver.jdbc.SQLServerXADataSource

   Apply the following settings to both providers:

   Scope: Workpoint cluster

   Database Type: user-defined

   Class Path Information: WAS_install_root/essentials/JDBC/sqljdbc.jar

6. Restart the WebSphere Application Server or the Deployment Manager service as required in your WebSphere environment to implement the new data source definitions.