SQL Server isn't usually at the top of the list when it comes to selecting a database for J2EE development.

Organizations that have made a commitment to Java and J2EE are likely to be Unix shops, making them highly unlikely to choose SQL Server, which can only be hosted on the Windows platform. Furthermore, the front-end client tools that come packaged with SQL Server can only be used on Windows.

Even so, SQL Server can be a good fit for WebLogic Server projects. It implements all four ANSI standard transaction isolation levels ­ Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Industry support is strong, with many vendors making JDBC drivers for SQL Server, including BEA and Microsoft. BEA includes special features in WLS just for SQL Server, which will be discussed later in the article. Microsoft's driver is still in beta, and can be downloaded at www.microsoft.com/sql/downloads/2000/jdbc.asp. A complete list of SQL Server JDBC drivers can found at http://industry.java.sun.com/products/jdbc/drivers.

WebLogic jDriver for MS SQL Server
BEA has a Type 4 SQL Server JDBC driver, which it licenses with WLS. A Type 4 driver should work on any platform because it doesn't require native libraries to be installed on the database client (in this case the WLS Application Server). Type 4 drivers are written entirely in Java, and communicate directly with the database server. Setting up the JDBC driver in WLS is quite easy, as the SQL Server driver is included in the weblogic.jar file. The only requirement is to set up the connection pool. You can even use BEA's SQL Server JDBC driver with standalone Java applications, as long as you include the weblogic.jar file and the license.bea file in the CLASSPATH. This is extremely useful when unit-testing code that performs database interaction. BEA's SQL Server JDBC driver is not XA-compliant, but can participate in distributed transactions. A distributed transaction, also referred to as a two-phase commit, is a transaction that spans two separate resources. All resources involved either commit or roll back the transaction together. WLS allows one non-XA­compliant resource manager to participate in distributed transactions.

To set up the WebLogic jDriver for MS SQL Server within WLS, from the console select JDBC, then Connection Pools. Select "Configure a new JDBC Connection Pool". First fill in the URL. The first part of the URL will be "jdbc:weblogic:mssq lserver4:". Next add the name of the database, followed by an "@". Now set the server name, followed by a colon, and the port number:

jdbc:weblogic:mssqlserver4:DatabaseName@ServerName:1433

Next, set the "Driver Classname" to "weblogic.jdbc.mssqlserver4.Driver". Now set up the user name inside the properties. Enter "user=MyUserName". The WLS 6.1 console provides a special area to enter the password. If you're using an older version, just add "password=MyPassword" to the properties. You still need to configure the options on the "Connections" tab, and set up a TX DataSource, but that will be the same for all connection pools (see Figure 1).

Key Generation
SQL Server supports auto-increment fields, which makes key generation much easier and less complicated. Set up the primary key column as an auto-increment column. A counter will be incremented, and the new value will be placed in the primary key column of the newly inserted record. Only SQL Server can set the column's value. This provides the same key-generation mechanism to all applications that use the table, not just WLS applications. You can retrieve the value of the new column by executing SELECT @@IDENTITY within the same transaction as the insert, as shown in Listing 1. If multiple inserts are executed within a single transaction, a very likely scenario, the identity value of the last record inserted will be retrieved by the SELECT @@IDENTITY.

WebLogic Server has automated this process when using CMP (Container Managed Persistence) with SQL Server. Add the XML code in Listing 2 to your weblogic-cmp-rdbms-jar.xml file. The class mapped to the primary key field of the table must be of type Integer. And of course, the table's primary key must be set up as an auto-increment field. This feature is very nice, as it removes database-specific code. WLS has similar support for Oracle.

If you're using container-managed transactions, make sure to use Tx DataSource, not DataSource, when setting up your SQL Server Connection Pool. This is a good idea for many reasons, but especially important when using automatic key generation. The SQL statement that returns the key will fall outside the transaction and won't be able to return the key value if you use DataSource.

Conclusion
Any database with strong transactional support and a good JDBC driver can, at some level, support WLS. SQL Server meets both requirements, and in addition, has strong support from WLS. Organizations that have made a large commitment to SQL Server in the past will likely do well to stay with SQL Server. There may be a large number of production applications utilizing SQL Server, plus staff will be experienced at managing SQL Server in an enterprise environment.

The next question is what level of performance do you need from your database, and what is the most economical way to achieve that performance, factoring in all the variables, including licensing, development time, production maintenance, and staff training.

I've covered some of the issues surrounding SQL Server and WLS integration. Before making a choice you must still perform some due diligence. The choice comes down to whether SQL Server is capable of providing the level of service you need.