OpenSubsystems

Business Components for Java Applications

Database Setup

MS SQL Server Setup

Last modified

$Author: bastafidli $
$Date: 2006/08/27 07:52:12 $
$Revision: 1.5 $
$RCSfile: dbsetup_sqlserver.html,v $

This document will help you to get started with previous versions of MS SQL Server supported by OpenSubsystems. It will walk you through obtaining, installing and configuring the database so that OpenSubsystems can access it and use it as its persistence store.

MS SQL Server 2005 Express

Official page | Download | Documentation
Tested version: MS SQL Server 2005 Express Edition Service Pack 1 with Advanced Services with jTDS 1.2

MS SQL Server 2005 Express Edition is the free, easy-to-use, lightweight version of SQL Server 2005. SQL Server Express is free to use and redistribute and therefore may represent an ideal database management system for Microsoft platform.

Download and install MS SQL Server 2005 Express Edition database. We recommend to download and install the Express Edition with Advanced Services since it includes additional features such as SQL Server Management Studio Express allowing you to manage your server and it also includes Service Pack 1. Extract the content of downloaded SQLEXPR_ADV.EXE file and start the installation by launching autorun.exe. We recommend to perform custom installation and select all the unchecked components to make sure that all the necessary functionality is available. When asked to configure services accounts choose to use the Local System account. Choose mixed mode (Windows Authentification and SQL Server Authentication) as an authentication mode. You can find the default administration password expected by OpenSubsystems here.

After the software is installed, it is necessary to create a database. Start the SQL Server Management Studio Express, which can be found in the Microsoft SQL Server 2005 group in the Start menu. In the tree on the left side expand nodes localhost (if your server runs on your local computer, otherwise expand the node corresponging to your server name) until you see node Databases. Right click on the Databases node and from the context menu select New Database... Enter database name "OSS" and confirm the dialog. Database OSS will be added to the tree.

To use MS SQL Server with OpenSubsystems you have to include a JDBC driver supporting this database on the classpath so that OpenSubsystems can access it. We recommend to use open source jTDS JDBC driver, which is included with OpenSubsystems and can be found in the external\jtds directory. This driver provides excellent performance and stability. Microsoft also provides it own SQL Server 2005 Driver for JDBC, which is type 4 JDBC driver available from the download section of the MS SQL Server website.

The JDBC driver uses TCP/IP protocol to connect to the the database. This protocol is by default disabled after the installation. You have to enable this protocol so that OpenSubsystems can access your database. Start the SQL Server Configuration Manager, which can be found in the Configuration Tools subfolder of Microsoft SQL Server 2005 group in the Start menu. Expand nodes SQL Server Configuration Manager, SQL Server 2005 Network Configuration until you see node Protocols for SQLEXPRESS. Click on this node to display list of protocols on the right. Right click on the TCP/IP protocol and from the context menu select Enable. To make the connection unambiguous you may also need to right click again on the TCP/IP protocol and from the context menu select item Properties. There on the IP Addresses tab under section IPAll clear out the value for TCP Dynamic Ports and enter value for port in the TCP Port field. The default value for port should be 1433 and you can confirm it here. Once you close the dialog you will have to restart the server for this setting to take affect. Click on the SQL Server 2005 Services to display the list of the services. Right click on the SQL Server (SQLEXPRESS) item and from the context menu select Restart.

Important sideline, if you will want to connect from the SQL Server Management Studio Express using TCP/IP and you have explictly specified the port, you will have to specify the server name in form ip_address,port or hostname,port.

Once you run OpenSubsystems with MS SQL Server and decide to remove all the created content from the database, the easiest way to do it is from the SQL Server Management Studio Express application. Under the node for your database click on the Tables node and delete all tables owned by user OpenSubsystems is using to connect to the database. You can find the default user name here. The dialog allows you to continue in deletion even if you receive an error so continue clicking OK until all tables in the tree are removed. Repeat the same for Views and Stored Procedures nodes. Next delete entries with specified user name in Users and Schemas under Security node of your database. For each deleted user you also have to delete it's login under the global Security, Logins node.

MS SQL Server 2005

Official page | Download | Documentation
Tested version: MS SQL Server 2005 with jTDS 1.2

Download and install the trial version of MS SQL Server 2005 database and its associated service packs. The downloaded database comes with 180-Day Evaluation License for purposes of 'demonstration, testing, examination and evaluation for the limited period'. Extract the content of downloaded SQLEVAL.EXE file and start the installation by launching autorun.exe. We recommend to perform custom installation and select all the unchecked components to make sure that all the necessary functionality is available. When asked to configure services accounts choose to use the Local System account. Choose mixed mode (Windows Authentification and SQL Server Authentication) as an authentication mode. You can find the default administration password expected by OpenSubsystems here.

After the software is installed, it is necessary to create a database. Start the SQL Server Management Studio, which can be found in the Microsoft SQL Server 2005 group in the Start menu. In the tree on the left side expand nodes localhost (if your server runs on your local computer, otherwise expand the node corresponging to your server name) until you see node Databases. Right click on the Databases node and from the context menu select New Database... Enter database name "OSS" and confirm the dialog. Database OSS will be added to the tree.

To use MS SQL Server with OpenSubsystems you have to include a JDBC driver supporting this database on the classpath so that OpenSubsystems can access it. We recommend to use open source jTDS JDBC driver, which is included with OpenSubsystems and can be found in the external\jtds directory. This driver provides excellent performance and stability. Microsoft also provides it own SQL Server 2005 Driver for JDBC, which is type 4 JDBC driver available from the download section of the MS SQL Server website.

The JDBC driver uses TCP/IP protocol to connect to the the database. This protocol is by default disabled after the installation. You have to enable this protocol so that OpenSubsystems can access your database. Start the SQL Server Configuration Manager, which can be found in the Configuration Tools subfolder of Microsoft SQL Server 2005 group in the Start menu. Expand nodes SQL Server Configuration Manager, SQL Server 2005 Network Configuration until you see node Protocols for MSSQLServer. Click on this node to display list of protocols on the right. Right click on the TCP/IP protocol and from the context menu select Enable. To make the connection unambiguous you may also need to right click again on the TCP/IP protocol and from the context menu select item Properties. There on the IP Addresses tab under section IPAll clear out the value for TCP Dynamic Ports and enter value for port in the TCP Port field. The default value for port should be 1433 and you can confirm it here. Once you close the dialog you will have to restart the server for this setting to take affect. Click on the SQL Server 2005 Services to display the list of the services. Right click on the SQL Server (MSSQLSERVER) item and from the context menu select Restart.

Important sideline, if you will want to connect from the SQL Server Management Studio Express using TCP/IP and you have explictly specified the port, you will have to specify the server name in form ip_address,port or hostname,port.

Once you run OpenSubsystems with MS SQL Server and decide to remove all the created content from the database, the easiest way to do it is from the SQL Server Management Studio application. Under the node for your database click on the Tables node and delete all tables owned by user OpenSubsystems is using to connect to the database. You can find the default user name here. The dialog allows you to continue in deletion even if you receive an error so continue clicking OK until all tables in the tree are removed. Repeat the same for Views and Stored Procedures nodes. Next delete entries with specified user name in Users and Schemas under Security node of your database. For each deleted user you also have to delete it's login under the global Security, Logins node.

MS SQL Server 2000

Official page | Download | Documentation
Tested version: MS SQL Server 2000 Service Pack 4 with jTDS 1.1

Download and install the trial version of MS SQL Server 2000 database and its associated service packs. The downloaded database comes with 120-Day Evaluation License for purposes of 'demonstration, testing, examination and evaluation for the limited period'. Extract the content of downloaded SQLEVAL.EXE file and start the installation by launching autorun.exe. We recommend to perform custom installation and select all the unchecked components to make sure that all the necessary functionality is available. When asked to configure services accounts choose to use the Local System account. Choose mixed mode (Windows Authentification and SQL Server Authentication) as an authentication mode. You can find the default administration password expected by OpenSubsystems here.

After the installation of MS SQL Server 2000 is finished, it is recommended to install the latest service pack to address any functional and security issues, which were already corrected. Download the latest service pack, for example file SQL2000-KB884525-SP4-x86-ENU.EXE, extract it's content and start the installation by launching setup.bat. If you have also installed analysis services, you may want to also apply service pack for these by downloading and extracting SQL2000.AS-KB884525-SP4-x86-ENU.EXE and starting setup by executing setup.exe.

After the software is installed, it is necessary to create a database. Start the Enterprise Manager, which can be found in the Microsoft SQL Server group in the Start menu. In the tree on the left side expand nodes Console Root, Microsoft SQL Servers, SQL Server Group and local until you see node Databases. Right click on the Databases node and from the context menu select New Database... Enter name "OSS" and confirm the dialog. Database OSS will be added to the tree.

To use MS SQL Server with OpenSubsystems you have to include a JDBC driver supporting this database on the classpath so that OpenSubsystems can access it. We recommend to use open source jTDS JDBC driver, which is included with OpenSubsystems and can be found in the external\jtds directory. This driver provides excellent performance and stability. Microsoft also provides it own SQL Server 2000 Driver for JDBC, which is type 4 JDBC driver available from the download section of the MS SQL Server website.

Once you run OpenSubsystems with MS SQL Server and decide to remove all the created content from the database, the easiest way to do it is from the Enterprise Manager application. Under the node for your database click on the Tables node and delete all tables owned by user OpenSubsystems is using to connect to the database. You can find the default user name here. If you receive an error message, accept it and delete the remaining tables, which you were trying to delete initially. Repeat the same for Views and Stored Procedures nodes. Next delete the specified user under Users node. You will also have to delete user's login under the global Security, Logins node.