Tuesday, May 10, 2011

Weblogic and MS SQL Configurations to run projects using SQL Server Connections

Creating the SQL Server connection from Jdeveloper is easy. However, after the project is developed, it has to be deployed on the Weblogic Server, a JNDI needs to be created to connect to the MS SQL Server. Oracle has provided 2 default drivers for MS SQL Server.

*Oracle's MS SQL Server Driver (Type 4 XA) Versions:7.0, 2000, 2005, 2008
*Oracle's MS SQL Server Driver (Type 4) Versions:7.0, 2000, 2005, 2008

The difference between 2 drivers is that one is Transaction Aware and other one is not. There are other drivers also present but they would need specific jars to be placed in the server lib directory, but default drivers would work without any additional configuration on the Weblogic Server. Hence a JNDI to connect to MS SQL Server can be created using any of these drivers very much the same way as you create for Oracle Database.

However, following MS SQL Server configurations also need to be done to be able to connect to MS SQL Server from Weblogic Server.
Create the JTA Procedures
Follow the steps as given here.

Also if you are using the XA driver, you also need to enable XA Transactions on MS SQL Server. Follow the following steps for that. 

  1. Turn on support for XA transactions: 
    • Go to Control Panel > Administrative tools > Component Services.
    • Expand the tree view to locate the computer where you want to turn on support for XA transactions: for example, Component Services > Computers > My Computer
    • Right-click the computer name, then click Properties
    • Click the MSDTC tab, and then click Security Configuration. 
    • Under Security Settings, click the check box for XA Transactions to turn on this support. (Enable XA Transactions
    • Click OK, and then click OK again. 
  2. Create a registry named-value: 
    • Use Registry Editor and navigate to registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\XADLL
    • Create a new registry named-value:
    • Name is the file name of the XA DLL (in the format dllname.dll).
    • Type is String (REG_SZ).
    • Value is the full path name (including the file name) of the DLL file. for example, C:\Program Files\Microsoft SQLServer\ MSSQL.1\ MSSQL\ Binn\ sqljdbc.dll
  3. Go to Control Panel ->Administrative tools->Services
    •  Start "Distributed Transaction Coordinator."
  4. Please restart the server to make these changes take effect.

While making the JNDI for data sources on WLS Console, specify the value of property platformClassName as oracle.toplink.platform.database.SQLServerPlatform.

And that's all. Happy Development!