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 LabKey database. 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.
Install and Configure Microsoft SQL Server
For current supported versions of Microsoft SQL Server, see
Supported Technologies.
Basic Installation Topics:
Additional Topics:
Install Microsoft SQL Server
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.
Configure During Installation
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.
To enable this feature on SQL Server 2017 Express, select the
Custom installation option and proceed through the wizard accepting defaults until the
Database Engine Configuration step.
Keep track of the user name and password; LabKey Server uses it to authenticate to SQL Server. It must be provided in plaintext in labkey.xml or in your mssql.properties file later.
If you've already installed SQL Server without enabling SQL Server Authentication then see
How to: Change Server Authentication Mode in the Microsoft SQL Server documentation.
Configure TCP/IP
After you've installed SQL Server, you'll need to configure it to use TCP/IP. Follow these steps:
- Launch the SQL Server Configuration Manager.
- Under the SQL Server Network Configuration node, select Protocols for <servername>.
- In the right pane, right-click on TCP/IP and choose Enable.
- Right-click on TCP/IP and choose Properties.
- Switch to the IP Addresses tab.
- Scroll down to the IPAll section, clear the value next to TCP Dynamic Ports and set the value for TCP Port to 1433 and click OK. By default, SQL Server will choose a random port number each time it starts, but the JDBC driver expects SQL Server to be listening on port 1433.
- Click OK
- Restart the service by selecting the SQL Server Services node in the left pane, selecting SQL Server <edition name> in the right pane, and choosing Restart from the Action menu (or use the Restart button on the toolbar).
SQL Server Management Studio
Download the SQL Server Management Studio graphical database management tool.
Set Up a Login
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:
- Run SQL Server Management Studio.
- Connect to the database.
- Under Security > Logins, add a new login, using SQL Server authentication.
- Enter the user name and password.
- Use this password to configure the data source below.
LabKey Configuration File
Next edit the
LabKey Server configuration file, by default named labkey.xml, to configure the jTDS JDBC driver for Microsoft SQL Server, which is included in the LabKey Server installation. You should comment out the Resource tag that specifies the PostgreSQL configuration. This Resource tag can be identified by the driverClassName "org.postgresql.Driver". After commenting out this resource tag, it should look similar to the following:
Comment out PostgreSQL Resource Tag
<!--
<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="postgres"
password="sa"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5433/labkey162"
maxTotal="20"
maxIdle="10"
maxWaitMillis="120000"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"
/>
-->
Add MS SQL Server Resource Tag
Use the following template for configuring a MS SQL Server data source. Replace USERNAME, PASSWORD, and DATABASE_NAME to fit the particulars of your target data source.
<Resource name="jdbc/labkeyDataSource" auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
url="jdbc:jtds:sqlserver://localhost:1433/DATABASE_NAME"
maxTotal="20"
maxIdle="10"
maxWaitMillis="120000"
accessToUnderlyingConnectionAllowed="true"
validationQuery="SELECT 1"/>
Documentation for this driver is available on
SourceForge. Other JDBC drivers for Microsoft SQL Server have not been tested.
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.
Install CLR Functions (GROUP_CONCAT)
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.
For Premium Editions, you may also need to install the PremiumStats CLR functions separately. For details see
PremiumStats Install.
Premium EHR users may also need to install LDKNaturalize, following similar methods.
SQL Server Synonyms
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.
Installation on Other Platforms
VirtualBox Deployment
On all operating systems, you can set up a
VirtualBox VM running Windows and install SQL Server there. These
Mac-specific instructions, combined with the documentation above should get you running.
Linux Deployment
Starting with SQL Server 2017, Microsoft has started distributing a native Linux version:
Docker Deployment
The Linux edition of SQL Server is also available within a
Docker container. This will work on Mac, Linux, or Windows.
You will need to install a separate visual database management tool:
Related Topics
