As part of installing required components, you need to install a database server. You can use either PostgreSQL or Microsoft SQL Server as the primary database with a Premium Edition of LabKey Server. This topic covers the process of setting up a Windows machine to use Microsoft SQL Server as the primary database. Information about other environments is provided below.
For current supported versions of Microsoft SQL Server, see Supported Technologies.
Basic Installation Topics:
If you already have a licensed version of Microsoft SQL Server in a supported version, follow the installation instructions noting the requirements outlined for installing an express edition below.
If you don't have a licensed version of Microsoft SQL Server, you can download a free Express Edition. Note that the Express Edition has database size limitations that generally make it inappropriate for production deployments. You can also use the Developer Edition; details may differ slightly from instructions in this topic.
During installation, configure Microsoft SQL Server to accept both Windows Authentication and SQL Server Authentication, ("Mixed Mode"), and specify a user name and password for the administrative account.
Follow the general steps to set up a development machine, with a few exceptions noted below:
1. You can ignore the instructions here around the config file "labkey.xml", which do not apply to setting up a development server.2. Instead of configuring your pg.properties file, use the one for use with Microsoft SQL Server (mssql.properties), specifying JDBC settings, including URL, port, username, password, etc.
3. Instead of running "gradlew pickPg" to pick up the properties file, run:
gradlew pickMSSQL
After you've installed SQL Server, you'll need to configure it to use TCP/IP. Follow these steps:
Download the SQL Server Management Studio graphical database management tool.
You may want to set up a new login (in addition to the "sa" system administrator) for LabKey Server to use to connect to SQL Server:
Edit the LabKey Server configuration file (usually named labkey.xml or ROOT.xml) to configure the JDBC driver for Microsoft SQL Server, available with Premium Editions of LabKey Server.
Comment out the Resource tag that specifies the PostgreSQL configuration. This Resource tag can be identified by the driverClassName "org.postgresql.Driver". Use "<!--" and "-->" to comment it out, similar to the following:
<!--
<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="postgres"
...
validationQuery="SELECT 1"
/>
-->
Use the following template for configuring a MS SQL Server data source. Replace USERNAME, PASSWORD, SERVER_NAME, and DATABASE_NAME to fit the particulars of your target data source. If your SQL Server is not using port 1433 (the default), edit that part of the URL as well.
<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://SERVER_NAME:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;"
maxTotal="20"
maxIdle="10"
maxWaitMillis="120000"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"
/>
Note: In the url parameter, "trustServerCertificate=true" is needed if SQL Server is using a self-signed cert that Java hasn't been configured to trust. The applicationName isn't required but identifies the client to SQL Server to show in connection usage and other reports. For production environments, adding the certificate to a trust store enable it validation. See the driver documentation for details on these configuration options.
Note: The maxWaitMillis parameter is provided to prevent server deadlocks. Waiting threads will time out when no connections are available rather than hang the server indefinitely.
When setting up SQL Server, you may need to separately install the GROUP_CONCAT Common Language Runtime (CLR) function. For details see GROUP_CONCAT Install.
You may also need to install the PremiumStats CLR functions separately. For details see PremiumStats Install.
EHR users may also need to install LDKNaturalize, following similar methods.
LabKey Server supports the use of SQL Server Synonyms. These alternative names function like shortcuts or symlinks, allowing you to "mount" tables and views which actually exist in another schema or database. For more information, see SQL Synonyms.
Microsoft distributes a native Linux version. Obtain it and follow the documentation available here:
1. Install Docker.
2. Load the SQL Server docker image. Learn more about this here.
docker pull mcr.microsoft.com/mssql/server:2019-latest
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Strong!Passw0rd' -p 1401:1433 --name mssql -d mcr.microsoft.com/mssql/server:2019-latest
gradlew pickMSSQL
5. Start tomcat.
The Linux edition of SQL Server is also available within a Docker container:
Steps to deploy SQL Server in Docker on a Windows machine:
1. Install a stable version of Docker for Windows, accepting the default settings in the installer dialog.
2. Restart your PC, if it does not automatically restart.
DOCKER_CERT_PATH=C: DOCKER_HOST=tcp://192..xx.xx.xxx:2376
DOCKER_MACHINE_NAME=default
DOCKER_TLS_VERIFY=1
4. Enable Hyper-V by going to Turn Windows features on or off and checking all Hyper-V boxes. Click OK.
5. Make sure Virtualization is enabled by checking your Windows Task Manager. Find it on the Performance tab under the grid for CPU usage. If it is not enabled, check the troubleshooting documentation available from Docker.
6. Restart your PC. Docker should start during this reboot. Follow status from the icon on the taskbar.
7. Use Windows Powershell to test the installation:
docker run hello-world
8. If the test is unsuccessful, install Docker Toolbox for Windows and restart, then try again.
9. Once the test is successful, you can pull the SQL Server image for linux from the Docker registry. Find the specific featured tag to pull on this page. For example:
docker pull mcr.microsoft.com/mssql/server:2019-latest
10. Once completed, run:
docker image ls
11. You should now see microsoft/mssql-server-linux listed in the Docker image cache.
12. Run SQL Server:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<your strong password>' -p 1401:1433 --name mssql -d microsoft/mssql-server-linux:2019-latest
13. View your SQL Server container up and running:
docker ps -a
14. Connect to SQL Server via SQL Server Management Studio using:
16. If desired, you can use disable the automatic start of Docker by editing the settings.
For troubleshooting assistance with this process, review the Docker documentation.
In some organizations, you may want or need to use Windows/Domain Authentication instead of SQL Server Authentication. This option should be considered carefully, as access to LabKey Server will depend on a successful login in the domain/Active Directory. If a problem occurs, like an expired profile or if Active Directory is either down or having issues, this will prevent the Windows-based authentication to SQL Server.
In order to use Windows/Domain Authentication you need to add the integratedSecurity parameter to the URL string in your data source tag:
url="jdbc:sqlserver://localhost:1433;databaseName=DATABASE_NAME;trustServerCertificate=true;applicationName=LabKey Server;integratedSecurity=true"In addition, you need to install the MSSQL JDBC Auth DLL file within the driver package; it is not included by default. You can download the matching version of the full driver from Microsoft directly. The error message you see will guide you to the expected version and specific file name. Find the DLL in the auth > x64 subfolder, download it, and put it into the Windows/System32 directory.
If this DLL is not present, you'll see an error message similar to:
Message: This driver is not configured for integrated authentication. ClientConnectionId:##### SQLState: 08S01 ErrorCode: 0
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:#####
...
java.lang.UnsatisfiedLinkError: Unable to load authentication DLL mssql-jdbc_auth-10.2.1.x64
Support for the jTDS driver was removed in 23.3 (March 2023).
If you are currently using the jTDS driver with a development machine, the next time you pull from develop and run 'gradlew pickMSSQL' you'll start using the new driver. If you need to switch back for some reason, you can use the target 'gradlew pickJtds' to return to using the jTDS driver.
For other instances, you will need to update the data source driver's class and URL in the labkey.xml/ROOT.xml file. See the configuration template above, particularly the driverClassName and url lines. We recommend that you first upgrade your staging (and/or test) instance(s), and upgrade production after testing is completed.
Note that if you are using Windows domain authentication, you will need to obtain and install a different DLL than you needed for the jTDS driver.
| previousnext |
| expand allcollapse all |
