Storing data in Microsoft SQL Server 2016+

Data Storage in 6.0+ allows a Metric Insights server to connect to existing enterprise database infrastructure. 

PREREQUISITES:

This article describes how to initialize Data Storage with Microsoft SQL Server. The process of connecting MySQL is essentially the same.

Grant Database Permissions to a Microsoft SQL User

Data Storage initialization can be performed by any SQL Server User having the corresponding Permissions to manipulate a database (including DDL commands):

  • CREATE
  • ALTER
  • DROP
  • RENAME
  • INSERT
  • UPDATE
  • DELETE
  • SELECT

We recommend creating a separate SQL Server User (service account) to work with the assigned Data Storage. See details below.

USE master
CREATE LOGIN <username> WITH PASSWORD = '<password>';
CREATE DATABASE <storage name>;
USE <storage name>;
CREATE USER <username>;
CREATE ROLE <storage role>;
EXEC sp_addrolemember '<storage role>', '<username>'
GRANT CREATE,ALTER,DROP,RENAME,INSERT,UPDATE,DELETE,SELECT ON DATABASE::<storage name> TO <storage role>;

1. Access Admin > Collection & Storage > Data Storage

The list page containing all Data Storages available in the system opens.

Below the grid, select [+ New Data Storage].

2. Create a New Data Storage

  1. Enter a Name for your Data Storage
  2. Storage Type: specify your Dataset Storage type. In this case it is a MSSQL database
  3. Node Name: leave as "node1"
  4. Username/Password: provide the credentials for accessing MSSQL Server database
  5. Provide Hostname or IP of the database host
  6. Enter the MS SQL Server Database name where MI Datasets will be stored
  7. JDBC driver: choose the JDBC driver, that will be used to interact with the database
    • After the JDBC driver has been selected, the Port and the JDBC string will be provided automatically
  8. You can use a Proxy to connect to the Data Storage:
  9. [Save]

3. Provide Data Storage Parameters

  1. Data Upload Type:
    • JDBC: a regular SQL INSERT
    • Shared Folder: a CSV file is uploaded to a shared folder on the Windows server available to both SQL server and docker container
      • Microsoft SQL Server CSV folder path: a folder where CSV files are saved on the SQL Server host
      • Shared folder mount path:
        • /opt/mi/shared/ for Simple Installations
        • For Orchestrated Environments the shared folder is defined by the installer options:
          • --shared-drive-address - IP address of the NFS server
          • --shared-drive-folder - path to the shared folder on the NFS server
    • Local Folder: a CSV file is uploaded to the /temp  folder in the dataprocessor container
  2. Available to all content creators: enable this parameter to allow all Power Users to use this Data Storage
  3. Median Aggregation Available: enable this parameter to be able to use median aggregation for Datasets and Metrics
  4. [Initialize] the database to be able to save Datasets to it
  5. [Test Connection] to verify that the Data Storage profile can communicate with the database

Note:

  • After Data Storage Initialization, configuration process is complete and cannot be reverted
  • If you need to change the Database name, re-enter Password and save changes before re-initializing the Data Storage

4. Selecting the Data Storage (applicable to Datasets only)

Access Dataset Editor> Data tab

Having configured the Data Storage profile, users can select it for storing their MI data (Datasets).