Totara support for a Microsoft technology stack

Summary

Totara Learn (v12 and earlier) and Totara TXP (v13 and later) fully support hosting on a Windows OS, IIS as a web server and using SQL server as the database engine. However there are a number of caveats and limitations which impact Totara sites hosted in a Microsoft environment. These are explained in more detail below.

General issues due to Totara staff and client preference

All supported platforms are thoroughly tested both automatically (unit tests and acceptance tests) and manually during the release process.

However, in addition to our formal testing it is not unusual for Totara staff to report issues that they discover in the course of their daily work. Since the majority of our developers and support staff use PostgreSQL or MySQL for their local installations, those databases are indirectly tested the most.

In addition, the vast majority of our clients deploy to Linux servers running PostgreSQL or MySQL. The increased usage of those platforms means that Totara is more 'battle-tested' on those platforms. Obscure bugs in rarely used areas are more likely to have been uncovered, reported and fixed on the more commonly used platforms.

That means that a site running on Microsoft technology may be more prone to discovering platform-specific issues than the same site running a more common configuration.

Totara Learn and Totara TXP on Windows

PHP support for Windows has traditionally had some issues, and while the situation is improving there are a number of known issues which may lead to platform-specific problems. Many of these cannot be worked around by Totara because they are intrinsic to the platform, not caused by the application logic.

Known PHP issues include:

  • Execution timeout uses different logic

  • File locking is incomplete and different - can lead to file-related problems

  • Open files cannot be deleted

  • Integers are 32 bits long - no support for large files > 2GB, problem with ids in large tables, random integer overflows

  • Very limited date support (1970-2038) - caused by 32-bit integers and other restrictions

  • Process-related APIs are not implemented

  • No support for sockets

  • Unicode problems with file names in Windows file systems

In addition Totara has a number of Windows-specific limitations:

  • No support for shares with \\ at the beginning of file paths in settings or dataroot

  • Different path separator - this can lead to Windows-specific bugs if developers aren't careful

  • Most large installs use memcache server for caching - it is not production ready on Windows

  • Limited support for CLI - compatibility problems with some shell emulations

  • File name case insensitivity problems - this can lead to Windows-specific bugs if developers aren't careful

Totara Learn served via IIS

Like PHP on Windows, Microsoft's Internet Information Server (IIS) has historically had a number of compatibility problems with PHP which cannot be worked around at the application level.

Known IIS problems include:

  • Simple error pages need to be disabled

  • Default script timeout is too short - it needs to be hours, not minutes

  • ignore_user_abort() not supported

  • Shutdown handlers do not seem to work in some situations - possible data loss and corruption

  • slasharguments do not work well - problems with unicode chars, tricky rewriting configuration

  • Some PHP extensions may not compatible with Non-Thread-Safe PHP that is recommended for IIS - can lead to bugs and crashes, but nobody knows exactly what the problems are

  • No X-Sendfile mechanism - low performance when serving large files

Totara Learn and TXP using SQL Server (MSSQL) as the database engine

Totara Learn and TXP fully support PostgreSQL, MySQL and MSSQL, with our recommended database of the three being PostgreSQL. MSSQL is fully supported, however performance in advanced areas does not scale as well on MSSQL as it does on the other two databases and due to this when using some of the more advanced features the experience may be less than ideal on a large site.

PHP database driver extension

The PostgreSQL extension is part of the core PHP distribution, is tested thoroughly on all platforms and is used much more widely than either the native or FreeTDS MSSQL drivers. Both MSSQL drivers have in the past presented compatibility and stability concerns with PHP applications that wish to make use of extended MSSQL functionality.

Recordset and transaction handling

Both recordsets and transactions are used extensively by Totara, and the two handle the combination very differently. For a long time MSSQL did not support concurrent recordsets, so to solve this Microsoft introduced Multiple Active Result Sets (codenamed MARS), which facilitated handling multiple recordsets with some pretty extreme limitations. One of these limitations in the PHP MSSQL driver is that recordsets cannot be used within transactions. The official MSSQL (native) PHP driver does not support MARS, in order to provide the required concurrent recordsets required by Totara and to facilitate their use within transactions we must emulate this layer in memory.

The unofficial FreeTDS driver available for MSSQL does a better job with MARS but is doing what we do in Totara for the native driver behind the scenes, essentially reading the recordset into memory. In both cases the cost of this is a much higher memory use when using MSSQL than when using PostgreSQL or MySQL. On small sites (<1000 users) this will not be an issue, however on larger installations or installations with minimally specced hardware this can lead to significant memory and performance issues.

Recordset and temporary tables

Like the above problem there is an issue when using both recordset and temporary tables. The two cannot be used at the same time, so in order to facilitate the writing to a temporary table whilst processing a recordset the entire recordset for MSSQL must be read into memory and then fed into the temporary table, or done in chunks. The cost of this is a slow experience in MSSQL when using some of the more technical areas of Totara such as backup and restore, HR import, statistics, and LDAP.

Totara Learn performance comparison

Performance testing conducted on a Microsoft Technology stack including IIS and MSSQL found 2.6 times more memory was required, and page requests took on average 2.6 times longer when compared to an out-of-the-box installation on Ubuntu using Apache and PostgreSQL.