Skip to content

MS SQL

This section describes how to use MS SQL (https://www.microsoft.com/en-us/sql-server/sql-server-2019) as the metadata database.

Supported versions: SQL Server 2012+

We use sqlalchemy's MSSQL dialect to connect to the MySQL server - this can be set up using multiple methods that are described in https://docs.sqlalchemy.org/en/13/dialects/mssql.html.

We recommend using PyODBC. To use this, an additional dependency (pyodbc) needs to be installed with the following components:

  • API
  • Celery - API worker

pyodbc is a python package and can be installed using pip in the virtualenv created for these components.

PyPI link: https://pypi.org/project/pyodbc/

Installation

$INSTALL_DIR/venv-api/bin/pip install pyodbc

Additionally, pyodbc also requires the "unixODBC devel libraries" and SQL Server ODBC driver to be installed.

This can be done with:

RedHat:

yum install unixODBC-devel

Ubuntu:

apt-get install unixodbc-dev

SQL Server ODBC driver: Follow steps in https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

Configurations

In the API configurations, the following configurations need to be set:

  • SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc://<username>:<password>@<hostname>/<dbname>?driver=ODBC+Driver+17+for+SQL+Server' Needs to be set to be able to connect to the Database.
  • Also, check the other configurations that may be required as described in API - Configurations > SQLAlchemy section