Storing data in Microsoft SQL Server 2016+
Data Storage is only available in version 6.1+
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 to MariaDB/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>;
1. Admin > Collection & Storage > Data Storage
From the Admin menu, access the Data Storage option.
2. Data Storage List > [+ New Data Storage]
Add a New Data Storage from the UI.
3. Configure Connection Parameters
Name: provide the name of the Data Storage profile
- This name will be used internally in the Metric Insights UI and can differ from the database name
- Storage Type: choose one of the supported options. In this case, it is Microsoft SQL Server
Node Name: leave as "node1"
- node1 is the default name of your node in MI Version 6.0.
- Multiple node options will be added future releases
- Username/Password: credentials for accessing the Microsoft SQL Server
- Hostname: name or IP of the database host
- Database name: MS SQL Server database name where you will store your Metric Insights datasets
JDBC driver: from the dropdown, choose the 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
Click [Save] to proceed
4. Initialize your Data Storage and Test Connection
- Initializing will configure and setup the Data Storage for use.
- Click [Test Connection] to verify that the Data Storage profile can communicate with the database
- After Data Storage Initialization, configuration process is complete and can not be reverted.
- If you need to change the Database name, re-enter Password and save changes before reinitializing the Data Storage.
To improve write performance to SQL Storage, please see Installing a Microsoft SQL Agent to improve INSERT performance when storing data in SQL Storage. This is especially critical for saving large datasets (millions of rows).
5. Selecting the Data Storage (applicable to Datasets only)
Having configured the Data Storage profile, Users can select it for storing their MI data (Datasets).
This option is available from the Dataset Editor > Data tab
- To understand the process of building a Dataset, view Create a Dataset from any Data Source