Setup MSSQL on Linux

Introduction

By default Senzing is set to use an embedded SQLite database. This article describes the steps to configure Senzing to use MSSQL as the entity repository and how to create the Senzing schema within a MSSQL database.

This article assumes you are already a MSSQL user or familiar with MSSQL, it only briefly covers the installation steps of MSSQL. If you require additional MSSQL information the following links will be useful:-

• MSSQL Homepage
• MSSQL Documentation

Prerequisites

In addition to the dependencies outlined in the Introduction: Quickstart article the following operating system prerequisites and packages are required. You must ensure the dependencies in the quickstart article are met before proceeding.

• A user with DDL and DML permissions to an MS SQL database you have created
• This DB tuning article followed for MS SQL
• Install the MS SQL Linux client/driver

**Note: If you use Senzing example docker containers, the references to G2Module.ini will refer to the SENZING_ENGINE_CONFIGURATION_JSON environment variable.  Also, the schema file would be obtained from the senzingapi-setup package.

 
Add Senzing Schema to MSSQL

• sqlcmd -U g2user -S 127.0.0.1 -d G2 -i senzing/resources/schema/g2core-schema-mssql-create.sql

Configure odbc.ini and G2Module.ini (3.7 and newer)

• No  /etc/odbc.ini modification is needed
• Edit /etc/G2Module.ini  and comment out the mssql CONNECTION line by removing the # and add a # before the sqlite3 CONNECTION line

# CONNECTION=sqlite3://na:na@/home/ant/senzprojs/senzing-1_14_7/var/sqlite/G2C.db
CONNECTION=mssql://g2user:password4g2user@127.0.0.1:1433:G2

Where:-

• g2user = Senzing userid
• password4g2user = Password for the Senzing userid
• 127.0.0.1 = IP address of the MS SQL server
• 1433 = port number for the MS SQL server
• G2 = actual database name (not the DSN)

Info

The Server value in the odbc.ini specifies both the server address and port number separated by a comma. Earlier versions of the odbc configuration may require the use of both Server and Port individually. 

[MSSQL]
Driver = ODBC Driver 17 for SQL Server
Database = G2
Server = 127.0.0.1
Port = 1433

Configure odbc.ini and G2Module.ini (pre-3.7)

• Edit your /etc/odbc.ini using sudo and add this to it

[MSSQL]
Driver = ODBC Driver 17 for SQL Server
Database = G2
Server = 127.0.0.1, 1433

• Edit /etc/G2Module.ini  and comment out the mssql CONNECTION line by removing the # and add a # before the sqlite3 CONNECTION line

# CONNECTION=sqlite3://na:na@/home/ant/senzprojs/senzing-1_14_7/var/sqlite/G2C.db
CONNECTION=mssql://g2user:password4g2user@MSSQL

Where:-

• g2user = Senzing userid
• password4g2user = Password for the Senzing userid
• MSSQL = DSN in /etc/odbc.ini

Info

The Server value in the odbc.ini specifies both the server address and port number separated by a comma. Earlier versions of the odbc configuration may require the use of both Server and Port individually. 

[MSSQL]
Driver = ODBC Driver 17 for SQL Server
Database = G2
Server = 127.0.0.1
Port = 1433

 

Run a Test Load

Go back to the Quickstart Guide to load and explore the provided sample truthset files.