Provides support for using MSSQL as the main GRID DB.
MSSQL support is disabled by default in order to ensure that the ACL can run in any environment even without an existing DB server.
When disabled, ACL uses an embedded HSQL database engine for all storage operations instead of MSSQL. What database is used can be seen either via JMX monitoring or with log level set to debug. In the logs, connections to HSQL are named "java:hsql" whereas MSSQL connections are named "java:gacl-mssql".
In order to enabled the MSSQL connection the following steps need to be performed:
The connection to the SQLServer is established using the default port number 1433 and username "CoreDB". User, port and password can be adjusted inside "[app]/config/tinyjee-configuration.xml" after the ACL was started once.
The following table lists (Java) system properties that are defined by this extension and can be used to enable MSSQL support or adjust the behaviour of this datasource:
| System Property | Since | Description |
|---|---|---|
| "mssqlserver" | 1.0 |
Is a property defining the hostname or IP-address of the MSSQL server that hosts the GRID CoreDB. The default configuration
that is created by the MSSQL extension will only enable MSSQL support if this system property was specified.
Note: Setting the port number with the hostname or IP-address is not allowed. |
| "mssqlserverFailover" | 1.2.2 |
Is a property defining the hostname or IP-address of the MSSQL server that hosts a hot standby mirror of the
GRID CoreDB. Use this parameter in high availability setups.
Note: Setting the port number with the hostname or IP-address is not allowed. |
| "mssql.no.xa" | 1.2.2 | Is a boolean property that toggles whether the SQLServer driver is used with the classic Driver interface wrapped in an XA.emulating datasource instead of using the driver's real XA interface which provides true distributed transactions at the cost of additional database configuration inside the SQLServer. |
| "gacl.mssql.use.parametrized.tag.queries" | 1.0 | When set to true, uses prepared parametrized queries with FT searches instead of building adhoc search queries with values being included as text. Note: In general this should offer better performance, however SQLServer 2008 crashes when this is enabled. |
| "mssql.use.ftsearch.tag.queries" | 1.2.3 |
Tuning property to enable fulltext search in MSSQL instead of using full table scan for tag queries.
Fulltext search has the disadvantage that date limitation is not honored in the index, thus a table scan with a data range limitation can be faster. Starting from version 1.2.3, FT searches are no longer enabled by default. Use this option to bring back the behaviour of previous versions. |
| "mssql.use.tag.query.provider.always" | 1.2.3 | Tuning property to force enabling this tag query provider even when fulltext search is not enabled. |
| "mssql.use.ao" | 1.2.2 | Is a boolean property that toggles whether the SQLServer driver is used with Always On feature |
| "readonly" | 1.2.2 | Is a boolean property that toggles whether the SQLServer driver is used with readonly Always On feature |
System properties can be specified at the commandline via "-DpropertyName=value". Use "startup.params.*" to permanently enable these properties.
This module implements the tag query handlers using MSSQL fulltext queries with CONTAINSTABLE and CONTAINS (depending on the query type whether filtering or searching is required).
| MSSQL full text search is no longer enabled by default starting from version 1.2.3 as there are scalability issues related to this query option |
In place of the MSSQL tag query provider that was used in previous versions, there is a new, generic, DB independent provider that is enabled by default, offering indexed queries when used in combination with small date ranges (implemented by Module - Jpa Datasource).
There are 2 options to use fully indexed tag queries (which are required to support tag queries without any or with large date ranges):
In contrast to the HSQL schema used for tests, the production schema for MSSQL is hand crafted and optimized to correspond with the source code created under the module Jpa Datasource.
1 2 3 | -- DUMP NOT FOUND IN 'src/CoreDB/schemas/MSSQL-GRIDCoreDB-Schema.sql' -- The latest dump can be obtained from P4: -- '//Core/GRID/DEV/GRID/src/CoreDB/schemas/MSSQL-GRIDCoreDB-Schema.sql' |
The latest version of this schema (including conceptial documentation) can be found inside P4 at //Core/GRID/DEV/GRID/src/CoreDB/schemas/MSSQL-GRIDCoreDB-Schema.sql
