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.
add a path to TwFreeTds_1.0.jar to CLASSPATH;
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
and replace PostreSQL settings for the following values with respective MS SQL settings:
DB_VENDOR = MSSQL
DB_DRIVER = com.thinweb.tds.Driver
DB_USER = login
DB_PASSWORD = password
DB_VENDOR is an 'absolute symbol' name of the data base
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:
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
DB_PASSWORD is the password to be used to connect to the
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
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 :
Next, the database structure must be created on MSSQL server in
the 'database_name' database.
- Log into your control panel server as cpanel user running the following command:
su -l cpanel
- Execute the following command:
java psoft.hsphere.tools.BatchSQL hsphere.db
Similarly, use BatchSQL to enter data into the database. You can
also use BatchSQL to execute PostgreSQL or Oracle scripts.