Storing data in Microsoft SQL Server 2016+
Data Storage in 6.0+ allows a Metric Insights server to connect to existing enterprise database infrastructure.
- Microsoft SQL Server must be installed and configured for use
- SQL Server User initializing the Data Storage must have relevant database permissions
This article describes how to initialize Data Storage with Microsoft SQL Server. The process of connecting MySQL is essentially the same.
Data Storage initialization can be performed by any SQL Server User having the corresponding Permissions to manipulate a database (including DDL commands):
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>;
The list page containing all Data Storages available in the system opens.
Below the grid, select [+ New Data Storage].
- Enter a Name for your Data Storage
- Storage Type: specify your Dataset Storage type. In this case it is a MSSQL database
- Node Name: leave as "node1"
- Username/Password: provide the credentials for accessing MSSQL Server database
- Provide Hostname or IP of the database host
- Enter the MS SQL Server Database name where MI Datasets will be stored
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
- You can use a Proxy to connect to the Data Storage:
- See Create a Proxy for details on configuring Proxy objects
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
/tempfolder in the dataprocessor container
Available to all content creators: enable this parameter to allow all Power Users to use this Data Storage
- Refer to the Data Storages Available to All article for details
Median Aggregation Available: enable this parameter to be able to use median aggregation for Datasets and Metrics
- Refer to Enable Median Aggregation for Datasets and Metrics for further details
- [Initialize] the database to be able to save Datasets to it
- [Test Connection] to verify that the Data Storage profile can communicate with the database
- 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
Access Dataset Editor> Data tab
Having configured the Data Storage profile, users can select it for storing their MI data (Datasets).
- To understand the process of building a Dataset, view Create a Dataset from any Data Source