Setup MSSQL with Centos/Red Hat

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.

• sudo or root privileges to install MSSQL and issue system command

Install and Basic MSSQL Setup

The following is a brief overview of the steps required to install MSSQL. For full details on installing MSSQL on Linux please see SQL Server on Linux.

• Install SQL Server. If you are using RHEL 8 or CentOS 8 change "rhel/7" to "rhel/8".
• sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo
• sudo yum install -y mssql-server
• sudo /opt/mssql/bin/mssql-conf setup
  • Follow the prompts to setup the SA password and select your version. (If you did not buy a specific version of MSSQL then select 3)
• Verify the server is running, if it succeeded you will see output similar to the following
  • systemctl status mssql-server

   

• Install command line tools
  • sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
  • Remove any previous versions of mssql-tools 
    • sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
    • sudo yum install -y mssql-tools unixODBC-devel
    • echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

•  Connect to the server and create the Senzing database

  • sqlcmd -S 127.0.0.1 -U SA

    • This will prompt you for the password for the admin account you created in setup.

  • CREATE DATABASE G2
  • SELECT Name from sys.Databases
  • GO
  • quit

 
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

• 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 = database name 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.