Using SQL Server as the Database for IBM Director V5.10

In this update: The recommendation is now to specify a shorter directory name when installing the JDBC driver, such as MSSQLJDBCSP3.

Since IBM Director V5.10 now uses JDBC for all database queries, a bit of tweaking is necessary before a SQL Server database can be used . This involves downloading and installing the JDBC connector kit, freely available from Microsoft.
In this section, we provide an easy installation guide for the JDBC connector for use on SQL Server with IBM Director Server on Windows.

Prerequisites and download

IBM Director currently supports JDBC Service Pack 3. To download, go to Microsoft's Download center at http://download.microsoft.com/ and search for JDBC. Select the JDBC Service Pack 3, as shown in Figure 1. The Web page also indicates the prerequisites for the IBM Director Server system. You will also need a functioning Java Virtual Machine (JVM) installed.


Figure 1: Getting the JDBC package from Microsoft downloads

Download the appropriate file at the bottom of the Web page. For a Windows-based IBM Director Server installation, download setup.exe. We will install the JDBC later.

Setup in SQL Server

This topic describes how to prepare the SQL Server database that IBM Director uses to store inventory data. You must prepare the SQL Server database before BM Director Server can use it.
On SQL Server, do the following:

1. Create a database.
Following your organization's rules for database creation, you should create the initial database. We decided on a database named Director with an initial size of 400 MB. as shown in Figure 2.


Figure 2: Initial SQL database creation
2. Set authentication mode to mixed mode.
In SQL Server Properties, set authentication to mixed mode, SQL Server and Windows, as shown in Figure 3.

Tip: Only mixed mode authentication is supported by the Microsoft JDBC. Windows-only authentication is not supported.


Figure 3: Changing the authentication
3. Create a service account.
This user ID is the one IBM Director Server uses to access the database. As long as this user only has access to the previously created database, there are no security issues. We created and configured user Director, as shown in Figure 4. In particular, the user ID needs to have db_owner permission.


Figure 4: Creating the user and setting the access
Install the JDBC on Windows

The JDBC software is installed on the system running IBM Director Server. Run setup.exe you downloaded earlier. The install procedure is similar to most InstallShield-based installations.

Tip: When installing the JDBC, change the default install folder to something shorter, such as MSSQLJDBCSP3, instead of the default "Microsoft SQL Server 2000 Driver for JDBC". This eliminates the problems associated with a long file name that also includes spaces.

Update the Classpath variable

Before IBM Director can use the installed JDBC drivers, the CLASSPATH variable needs to be updated with the correct path to where you have installed the JDBC driver. The CLASSPATH is the search string that the JVM uses to locate the JDBC drivers on your computer. If the drivers are not on your CLASSPATH, you receive the error "class not found" when trying to connect.

To update the classpath variable, do the following:

1. Right-click My Computer and click Properties.
2. Click the Advanced tab and click the Environment Variables button.
3. In the lower half of the window in System Variables, select CLASSPATH and click Edit. If CLASSPATH does not exist in the list, click New otherwise and type in Variable name as CLASSPATH.
4. Add the entries to the CLASSPATH as shown below: (please note, there are no line breaks)..;C:\Program Files\MSSQLJDBCSP3\lib\msbase.jar;C:\Program Files\MSSQLJDBCSP3\lib\mssqlserver.jar;C:\Program Files\MSSQLJDBCSP3\lib\msutil.jar; Figure 5: Configuring the CLASSPATH variable in Windows
5. Click OK to save the change to CLASSPATH

This can also be verified from the command line; type SET from a command prompt, and the result should be the same as shown in Figure 6.

c:\>SET ALLUSERSPROFILE=C:\Documents and Settings\All Users APPDATA=C:\Documents and Settings\Administrator.DIRECTOR5\Application Data CLASSPATH=.;C:\Program Files\MSSQLJDBCSP3\lib\msbase.jar;C:\Program Files\MSSQLJDBCSP3\lib\mssqlserver.jar;C:\Program Files\MSSQLJDBCSP3\lib\msutil.jar;

Figure 6: Verifying the classpath variable in Windows

Now you can install IBM Director Server and choose the SQL Server database option.

Tip: When connecting to a named SQL instance, the instance name (and the backslash) should be omitted. For example, SERVER1\SQLINSTANCE2 should be entered as just SERVER1.