Microsoft SQL Server

Important

Microsoft SQL Server support is only available to large enterprise deployments of Canopy. A Premium/Enterprise license for Canopy is required, and an additional support fee is also applicable. This allows us to ensure we can maintain and support this feature for the small number of clients that require it.

Attention

The default collation used by SQL Server is likely case insensitive. Canopy was designed to operate on a case sensitive database and filesystem. It is recommended to use the Latin1_General_CS_AS collation for the database so that it is case sensitive.

Note

Canopy requires MS SQL Server to operate in READ_COMMITTED_SNAPSHOT mode. This will automatically be configured by Canopy.

Additionally, Canopy assumes the isolation level will be READ_COMMITTED, which is the default on SQL Server.

Steps

This guide replaces the canopy-setup postgresql step from the main installation guide, all other steps are the same.

Attention

Unlike with PostgreSQL, the database must be provisioned before the installation of Canopy.

  1. Ensure a SQL Server Database and credentials have been provisioned. database name, user name, password, hostname, port, etc.

  2. Install msodbcsql17 package from Microsoft. See Dependencies

  3. Follow the Platform installation guides until the Configuration step.

  4. Configure Canopy’s DATABASE_URL setting for the new database. See Canopy DATABASE_URL Configuration as the bottom of this page.

  5. Continue the Configuration section, skipping the PostgreSQL step.

After all these steps you should have:

  • Installed Canopy, its dependencies (libtidy, rabbitmq, docserver, etc) and license file.

  • Edited canopy.ini to update DATABASE_URL

  • Installed nginx

  • Executed the setupdb step.

  • Created the first admin user.

  • Restarted all the services.

Supported SQL Server versions

  • SQL Server 2019 (15.x)+

  • Azure SQL Database (DB Engine v12)+

Dependencies

Microsoft’s ODBC driver 17 is required for Canopy and can be sourced from https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

The msodbcsql17 package is required.

Example steps (executed as the root user), for RHEL/CentOS 7:

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
ACCEPT_EULA=Y yum install -y msodbcsql17

Example steps (executed as the root user), for Ubuntu 20.04:

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17

Canopy DATABASE_URL Configuration

  1. Open /etc/canopy/canopy.ini in a text editor

  2. Replace the DATABASE_URL line with DATABASE_URL=mssql://username:password@hostname:port/dbname where each part of the URI is replaced with the correct values.

  3. Save and exit the text editor.