Using SQL Server as the Database for IBM Director V5.10
Published 13 January 2006, updated 13 April 2006
Authors: David Watts
IBM Director Server supports a variety of different databases running locally on the management server, as well as external databases running on other systems. The management server uses these databases to store inventory information. This Technote describes how to set up Microsoft SQL Server for the IBM Director Server database on Windows. This procedure involves configuring SQL Server and installing the JDBC connector kit from Microsoft.
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:
- Right-click My Computer and click Properties.
- Click the Advanced tab and click the Environment Variables button.
- 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.
- 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
- 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.
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;
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.
This material has not been submitted to any formal IBM test and is published AS IS. It has not been the subject of rigorous review. IBM assumes no responsibility for its accuracy or completeness. The use of this information or the implementation of any of these techniques is a client responsibility and depends upon the client's ability to evaluate and integrate them into the client's operational environment.
Follow IBM Redbooks
Follow IBM Redbooks