MS SQL Setup As H-Sphere System DB

This guide explains how to configure MS SQL so it is used as H-Sphere system database instead of the default PostgreSQL database. If you choose to do so, please note that you will have to perform many configurations and updates manually. To configure MS SQL as a user database, follow the Microsoft SQL Server Installation guide.

1. Download TWFreeTDS JDBC driver
Fill out the form. Next you will receive a letter with a link to the page with products to download.
Go to this page and choose TWFreeTDS.
Click the "download" link.

2. Config environment
Put TwFreeTds_1.0.jar into the directory with jar files.
Change profile:
add a path to TwFreeTds_1.0.jar to CLASSPATH;

Change /shiva/apache/etc/jserv.properties:
Add the following line:
wrapper.classpath= path to TwFreeTds_1.0.jar

Please make sure that your Microsoft SQL Server is configured to use the TCP/IP networking protocol and that it is currently listening to a TCP/IP port.

On the control panel server, open file /hsphere/local/home/cpanel/shiva/psoft_config/hsphere.properties and replace PostreSQL settings for the following values with respective MS SQL settings:

DB_VENDOR = MSSQL
DB_USE= database_name
DB_DRIVER = com.thinweb.tds.Driver
DB_URL =jdbc:twtds:sqlserver://servername:port/database_name;TDS=7.0
DB_USER = login
DB_PASSWORD = password
DB_NEWID =

where:

DB_VENDOR is an 'absolute symbol' name of the data base server
DB_USE is the database name
DB_DRIVER is a JDBC driver to work with the data base server
DB_URL - JDBC URL which has 2 forms:
jdbc:twtds:sqlserver://servername:port/database_name;TDS=7.0
or
jdbc:twtds:sqlserver://servername/database_name;TDS=7.0

The server is set to use 1433 as a default port, but this can be changed by dba.
TDS: TDS protocol version to use. Can be set to "4.2", or "7.0". Defaults to "7.0".
- SQLServer 6.5 uses TDS protocol 4.2.
- SQLServer 7.0 use TDS protocol 7.0.

DB_USER is the user name that to be used to connect to the database;
DB_PASSWORD is the password to be used to connect to the database;
DB_NEWID variable is used for compatibility with other SQL servers and must not be changed.

Restart the webserver when you are through with configuration of the hspere.properties file.

3. Convertion of sql script from Postgres format into MSSQL format

You can use the post2mssql.pl script to convert sql script from Postgres format into MSSQL format. As MSSQL does not have sequences, the script also creates a stored procedure "nextval" and a table named "sequences".
Format of usage:
./post2mssql < hsphere.db > hsphere_mssql.db

4. Usage of BatchSQL for execution of sql scripts.
Create a database with name=database_name on MSSQL server.
For 'batch' execution of sql commands, use the class: psoft.hsphere.tools.BatchSQL :

1. Log into your control panel server as cpanel user running the following command:
   su -l cpanel
2. Execute the following command:
   java psoft.hsphere.tools.BatchSQL hsphere.db