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.

Note:
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!

7 comments:

  1. Hi Neerag! I saw your post. I would offer only one small difference: "The difference between 2 drivers is that one is Transaction Aware and other one is not." The same jar and most of the same classes are used, whichever 'driver' class is chosen, but the XA one has the XA calls and capability to join a global/XA transaction. However, it is key that the WebLogic DataSource is configured to support global transactions, so the WebLogic Transaction Manager does in fact call the XA methods and includes the JDBC in the global transaction. For global transactions with more than one resource involved, XA is generally required, but the non-XA driver could also support a global transaction. We offer an LLR (logging last resource) option to safely and performantly include one non-XA resource in a general global transaction.
    HTH,
    Joe Weinstein at Oracle

    ReplyDelete
  2. Hello Guys,

    I am getting the following error when invoking the SQL Server...database, I am using 11.1.1.4.0 Jdeveloper version...

    Message handle error.
    error while attempting to process the message "com.collaxa.cube.engine.dispatch.message.invoke.InvokeInstanceMessage"; the reported exception is: Error committing transaction:; nested exception is: javax.transaction.xa.XAException: JDBC driver does not support XA, hence cannot be a participant in two-phase commit. To force this participation, set the GlobalTransactionsProtocol attribute to LoggingLastResource (recommended) or EmulateTwoPhaseCommit for the Data Source = SQLgsfsDataSource2

    While configuring the datasource of SQL server in admin console, i have specified it as a oracle Type 4 version for MS Sql Server (non-XA)...In the transactions tab i said to support global transactions...and in the protocol..i gave the one phase commit protocol...

    And while configuring the JNDI name , i gave the JNDI name of the datasource in the datasource tab (not in the XA datasource tab, because i have selected my datasource as non-XA). So, what changes do i need to make my invoke to SQL database working...do i just need to change the protocol to Last logging resource ? please help me guys...


    Thanks a lot...hoping for the reply ...

    ReplyDelete
  3. Hi Narsing,

    I was also getting the same error. Follow the blog above with all steps for xA data source and the error will go off.

    Regards,
    Neeraj

    ReplyDelete
  4. Hi Neeraj any info on administration of AIA?

    ReplyDelete
  5. Hi neeraj,

    I have used com.microsoft.sqlserver.jdbc.SQLServerDriver to create a non XA datasource and further a jndi and have used the same in my DB Adapter I am getting the below error on invocation


    oracle.fabric.common.FabricInvocationException: java.lang.NoClassDefFoundError: com/microsoft/sqlserver/jdbc/SQLServerException

    part name="detail">
    com/microsoft/sqlserver/jdbc/SQLServerException


    Thanks in advance
    Regards
    Lakshmi

    ReplyDelete