Configuring Microsoft SQL Server
To use HCL Compass with SQL Server databases, you must install a supported version of SQL Server on your database server.
For detailed instructions on completing the tasks in the following workflow, consult your Microsoft™ SQL Server software documentation. To use SQL Server from 64 bit Linux, the Microsoft ODBC Driver 17 for SQL Server must be installed prior to usage. For more details, see the Microsoft's instructions for Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS.
On Linux, SQL Server database connections require that the SQL Server contains TLS 1.2 support. This is the default on the most recent versions of the database (Sql Server 2016+). For prior SQL Server versions consult Microsoft documentation to obtain the latest updates. See https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server
On Linux, Compass defaults
and expects to use the Microsoft SQL Server driver version 17.4. If the Microsoft SQL Server driver
version that is installed is not v17.4, the file
/opt/hcl/ccm/compass/linux_x86_64/resources/cqodbcinst.ini must be changed to point
to the correct version. The version can be checked by looking at
/opt/microsoft/msodbcsql17/lib64/ via ls /opt/microsoft/msodbcsql17/lib64/ which will
list the available driver such as libmsodbcsql-17.5.so.1.1.
For example, in cqodbcinst.ini: [CCM Microsoft ODBC Driver for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1 , the driver value must be
changed to Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.1.1.
| Task | Comments |
|---|---|
| Install a supported version of SQL Server | Your SQL Server databases must be in the same network domain as both your HCL Compasst Web server and any HCL Compass clients or tools that must connect to the database. If they are not in the same domain, errors are returned. For more information, see technote 1133904 |
| During the installation process, select either the Dictionary order, case-sensitive or Dictionary order, case-insensitive sort-order option |
If you change the sort order after installing SQL Server, you must rebuild the SQL databases and reload the data. Changing the sort order affects the case-sensitivity of HCL Compass queries. If you want queries to not be case sensitive, set the order here. |
| Select mixed-mode authentication for HCL Compass to function properly. |
HCL Compass requires SQL Server Authentication, which may require configuration to permit "mixed mode" authentication, which includes SQL Server Authentication for HCL Compass Databases. |
| Verify that the computer you have installed SQL server on is registered with SQL Server. | If the computer is not registered, it must be before you can begin creating the databases |
| Create the database containers |
Create a database for the schema repository and a database for each user database. Allocate at least 50 MB for the schema repository and at least 15 MB for every 1,000 records that you expect each user database to store. Note: The default code page for your locale may not be compatible with your HCL Compass environment. For more information, see
Code pages and national
language support
|
| Create a db_owner login for each HCL Compass database (schema repository and each user database). To do so, select the db_owner and public server roles for each login that you create. | Do not use the SA (system administrator) login. The database must be empty when you upgrade or move it. If you use the SA login, HCL Compass can read the system tables and assumes that the database is not empty and does not let the process continue. |
SQL Server 2012, and later versions, may require the ODBC Driver 11 or 13 for certain advanced features such as TLS 1.2. HCL Compass uses the older "SQL Server" driver by default. You may need to install one of the Open Database Connectivity (ODBC) drivers for SQL Server. Consult Microsoft to determine the appropriate version of the driver to use.
To use one of these drivers, you set the MS_DRIVER connect option using the HCL Compass maintenance tool and designer. For information about how to configure the MS_DRIVER connect option, see the Database properties and connection information for SQL Server topic.