Install and Configure Microsoft SQL Server 2012 or 2014

As part of installing required components, you need to install a database server. You can use either PostgreSQL or Microsoft SQL Server. Follow these steps if you wish to use Microsoft SQL Server.

For current supported versions of Microsoft SQL Server, see Supported Technologies.

To install Microsoft SQL Server 2012 or 2014:

1. If you don't have a licensed version of Microsoft SQL Server, you can download the free Express Edition of either from http://www.microsoft.com/express/sql/download/. Note that the Express Edition has database size limitations that generally make it inappropriate for production deployments. Download SQL Server Management Studio graphical database management tool from http://www.microsoft.com/en-us/download/details.aspx?id=42299 --> Click Download --> Select appropriate SQLManagementStudio .exe to download. Use Windows Update to install the latest service packs.

2. During installation, configure Microsoft SQL Server to accept both Windows Authentication and SQL Server Authentication, and specify a user name and password for the administrative account. Keep track of this user name and password; LabKey Server uses it to authenticate to SQL Server. It must be provided in plaintext in labkey.xml. 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.

3. 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 in the Microsoft SQL Server program group on the Windows start menu. (on win8+, there won't be a program group- it's an MMC snap-in. To launch, type SqlServerManager12.msc (for server2014) and press Enter. For details: https://technet.microsoft.com/en-us/library/ms174212.aspx)
• 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.
• Under 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.
• 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).

4. You may want to set up a new login 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, and type the user name and password.
• Use this password to configure the data source below.

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"
        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. The following template applies to Tomcat 8. See an earlier version of this topic for a Tomcat 7 template.

<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" 
        accessToUnderlyingConnectionAllowed="true"
        validationQuery="SELECT 1"/>

Documentation for this driver is available on SourceForge. Other JDBC drivers for Microsoft SQL Server have not been tested.

6. You may also need to install the GROUP_CONCAT function separately. For details see GROUP_CONCAT Install.

7. 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.

8. For setting up SQL server in a Virtual Box Windows VM on Mac, follow additional instructions here under 'Virtual Box'.