Storing Data in Microsoft SQL Server

Metric Insights Data Storage allows it server to connect to existing enterprise database infrastructure. This article describes how to initialize Data Storage with Microsoft SQL Server. The process of connecting MySQL is essentially the same.

PREREQUISITES:

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. Open Data Storage List

Access Admin > Collection & Storage > Data Storage

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

2. Create a New Data Storage

  1. Name: Enter a name for your Data Storage.
  2. Storage Type: Specify your Dataset Storage type. In this case it is a "Microsoft  SQL Server" database.
  3. Node Name: leave as "node1".
  4. Auth Type: Select either the authentication process should be performed via password and username or via the identity profile.
  5. Username/Password: Provide the credentials for accessing MSSQL Server database.
  6. Hostname: Provide a hostname or IP of the database host.
  7. Database name: Enter the name for MS SQL Server Database where MI Datasets will be stored.
  8. 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.
  9. Use Proxy: Define either you want to use a proxy to connect to the Data Storage or not.
  10. [Save]

3. Provide Data Storage Parameters

  1. Available to all content creators: Enable this parameter to allow all Power Users to use this Data Storage.
  2. Median Aggregation Available: Enable this parameter to be able to use median aggregation for Datasets and Metrics.
  3. [Initialize] the database to be able to save Datasets to it.
  4. [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.