HowTo Use Microsoft SQL Server Database System with Jena2

What is Microsoft SQL Server?

Microsoft SQL Server refers to a range of database servers available from Microsoft Corporation for use under the Windows operating system. Jena has been tested with Microsoft SQL Server 2005 Express, Microsoft SQL Server 2000 (Enterprise Edition), and with the Microsoft SQL Server 2000 Desktop Edition (MSDE) Release A.

Download and Installation of SQL Server Express

Download and install Microsoft SQL Server 2005 Express SP1. It is convenient to install the server manager as well.

Installation will need to include enabling TCP/IP access via the Microsoft Surface Area configuration tool.

Install a JDBC driver. The Microsoft JDBC driver has been tested with Jena. All that is required is the JAR file, so you may choose to install the .msi file or download download the Unix version (.tar.gz) and just pull the jar out of it and use that on WindowsXP.

In addition there is an open source driver compatible with SQL Server available from http://jtds.sourceforge.net/.

Ensure the JDBC driver is on your application classpath.

The URL is like (in Java):

String jdbcURL ="jdbc:sqlserver://localhost\\SQLExpress;database=jenatest" ;
  //** Note the \\ - that is Java escape becoming a single \
  //** Note: assumes it is not the default instance
  //  (example shown is the install default which is a named instance of SQLExpress)

See this Microsoft blog for details.

1. Enable TCP/IP. Forget to do it when you installed? Run the SQL Server Area Configuration tool / Services and Connections
2. Ensure your firewall isn't blocking port 1433
3. Make sure the SQL Server Browser is running or automatic : SQL Server Configuration Manager

Create a user.  Create a database.  Allow the user to access the database with at least these permissions:

• db_datareader
• db_datawriter
• db_ddlwriter (gives the ability to create SQL tables)

Persistent models are created in the usual way:

1. Load the JDBC driver. This enables the Jena program to communicate with the database instance.
2. Create a database connection. This creates a Java object for a database connection.
3. Create a ModelMaker for the database
4. Create a Model for existing or new data.

The JDBC driver class name is (Microsoft driver): "com.microsoft.sqlserver.jdbc.SQLServerDriver".

Installation of MS SQL Server

Microsoft SQL Server is commercial software and we cannot provide generic installation instructions. We assume that Jena users wishing to use SQL Server as a store have a correctly configured instance running. The Desktop Edition (MSDE 2000 Rel A) is free and redistributable software. See http://www.microsoft.com/sql/msde/default.mspx for download, installation and license information.

Notes

The single biggest limitation with the SQL Server 2000 driver implementation is that while a transaction is in progress inserting triples into a Model then other processes will be blocked from reading from that Model. In other databases, concurrent reads during a transaction are allowed. Whilst SQL Server does support ROW level locking, there seem to be situations in which it may still chose TABLE level locking at the expense of reduced concurrency.

This does not affect SQL Server 2005.