Windows with MS SQL Server

This section describes the installation process of Allegra for installation on a MS Windows based system such as Windows 10, Windows Server 2012 or Windows Server 2016 and an MS SQL Server database. using the Allegra Windows Installer.

Preparation

Permissions

You must have the following permissions on your server:

  • You must be allowed to create software.

  • You must have permission to create services.

  • You must have permission to create a database.

Hardware and Software

First, you need to make sure that the installation environment meets all necessary requirements:

  1. You must be logged in with administrator privileges.

  2. Check that the hard disk on which programs are normally installed (usually C:\Programs) has at least 800 MByte of free space.

  3. Install Java on the computer if you don’t already have it there. You can use Java 8 or Java 11. You can download it from the AdoptOpenJDK server.

  4. There should not run any other software that provides services on port 80, like IIS, Skype or any other web server on the same machine. There should be no other Tomcat running on the server either. If there is, you will need to install Allegra manually.

    If the default port 80 is used by another application, the installer will try to use the following ports in the following order before giving up:

    • Port 80

    • Port 8080

    • Port 8081

    • Port 7080

    • Port 9080

    5. Create an empty MS SQL Server database with the correct character encoding. The database server can be identical to the Allegra server, or you can run it on a different computer.

    It is beyond the scope of this guide to explain this step in more detail, as there are many options. If you choose this route, you will usually know, how to proceed.

    You need to grant a database user the privileges to create tables and indexes. This user will be used by Allegra to connect to the database.

Hint

Allegra comes with JDBC drivers for MySQL 5, MySQL 8, MariaDB, MS SQLServer and Postgres. If you want to work with another database system such as Oracle or DB2 you will need to add the appropriate drivers to the Apache Tomcat directory under C:\Program Files\Apache Software Foundation\Tomcat9\lib.

Downloading the Allegra Installer

You can download the Allegra installer for free from the Allegra download page.

Executing the Installer

Right-click on the installer and select “Run as administrator” from the context menu.

../_images/installer-start.png

Attachments and Configuration Directory

Select the directory where you want to keep the Allegra configuration files all attachments and index files for full-text search. You should provide enough depending on the number of users and the number of attachments. about 1 to 10 GByte. This directory is referred to as TRACKPLUS_HOME.

Deactivating the Firebird Installation

Disable the installation of the Firebird database system, which is the default for smaller installations.

../_images/installer2.png

Selecting the MS SQL Server

Select MS SQL Server as the database management system.

../_images/installer-mssqlserver1.png

Configuring the database connection

Configure the database connection.

../_images/installer-mssqlserver2.png

The URL format for jTDS is:

jdbc:jtds:sqlserver://<server>[:<port>][/<database>][;<property>=<value>[;...]]

with <port> as the port on which the database server listens (default 1433) and <database> as the database name (e.g. “allegra”).

A description of all possible parameters can be found at http://jtds.sourceforge.net/faq.html.

Some important features are listed below.

charset

default - the character set with which the server was installed.

Very important setting, determines the byte value for character mapping for CHAR/VARCHAR/TEXT values. Applies to characters from the extended set (codes 128-255). For NCHAR/NVARCHAR/NTEXT values have no effect, since these are stored with Unicode.

domain

Specifies the Windows domain in which authentication should take place. If this parameter is present and the username and password are specified, jTDS will use Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the specified user and password are the domain user and password). This allows non-Windows clients to log on to servers that are configured for Windows authentication only.

If the domain parameter is present, but no username and password are specified jTDS uses its native single sign-on library and logs on with the credentials of the logged-in Windows user (for this to work, you would have to be running Windows, logged into a domain and have the SSO library installed).

instance

Named instance to connect to. SQL Server can have multiple so-called “named instances” (i.e. different server instances running on different TCP ports) on the same computer. When using Microsoft tools the selection of one of these instances is done by using “$<host_name><instance_name>$” instead of the usual “$<host_name>$”. With jTDS, you must separate the two instances and use the instance name as a property.

sendStringParametersAsUnicode

default - true

Determines whether string parameters are sent to the database in Unicode or in the encoding of the database system settings. This seriously affects the performance of SQL Server 2000, because the types are not automatically cast (as in 7.0), i.e. if an index column is Unicode and the string is transmitted with the default character encoding (or vice versa), SQL Server performs an index check instead of an index search. Determines for Sybase whether strings that cannot be encoded in the server’s character set of the server are sent as Unicode strings. There is a performance penalty for the encoding logic, so set this option to to false if unitext or univarchar data types are not used or if the character set is utf-8.

Optional Components

You can now add optional components, for example, if you want to print Gantt diagrams or if you want to export nice LaTeX documents from the wiki.

Printing Gantt Charts

To be able to create nice PDF Gantt charts, you need to use ImageMagick and Phantomjs <noinstaller>`.

You can download these packages from the Allegra download section or use the Allegra installer. Allegra detects the presence of these systems usually automatically. If not, you must provide the path to the commands to the servlet container as an environment variable:

-DIMAGEMAGICK_HOME=/usr/bin/convert -DPHANTOMJS_HOME=/usr/local/bin/phantom.js

If you have problems printing large Gantt charts (500+ elements over a larger period of time), you may need to adjust some timeouts in your web server or servlet container. For an Apache web server, you can add or change the following in your server configuration file:

...
<IfModule mod_reqtimeout.c>
    RequestReadTimeout head=20-30,body=30,MinRate=1000
</IfModule>
...

For a Tomcat server, you would need to adjust timeout and upload size parameters in the file ...tomcat9\conf\server.xml:

...
<Connector port="80" protocol="HTTP/1.1"
           connectionTimeout="30000" asyncTimeout="20000" maxPostSize="40000000"
...
<Connector port="8009" protocol="AJP/1.3" redirectPort="8443"
        asyncTimeout="20000" maxPostSize="40000000"/>
...

Creating LaTeX PDF Documents

To create nice PDF documents from the wiki, you need to install a LaTeX distribution on your your computer or server <noinstaller>`. Allegra usually detects the installation automatically; if not, you must specify the path to the xelatex command to the servlet container as a variable:

-DLATEX_HOME=/usr/bin/xelatex

For Windows systems we recommend the MikTeX-Distribution.

Basic configuration

You should now see login instructions in one browser window and the login screen in a second window.

Follow the instructions on the hints screen or go to the instructions for the basic configuration.

Security

If you want to run the Allegra server productively, you should put it behind an nginx, Apache or IIS proxy server. This way you can ensure that only SSL-secured connections are used and you do not expose the Tomcat to the Internet.

Instructions on how to do this can be found here, for example: