Help & DocumentationGetting DataConnecting to Data SourcesStoring DataInstalling a Microsoft SQL Agent to improve INSERT performance when storing data in SQL Storage

Installing a Microsoft SQL Agent to improve INSERT performance when storing data in SQL Storage

Microsoft SQL Storage is great for storing large sets of data. However, INSERT operations for millions of rows can take hours. To solve for this, Metric Insights created a SQL Agent that runs directly on the Microsoft SQL Server. This agent facilitates the bulk insert of datasets via a CSV file. 

PREREQUISITE:

This article covers the following:

  1. How does the Microsoft SQL Agent work?
  2. Prerequisites
  3. Install the Microsoft SQL Agent

You can also set up Windows Server Shared Folder to improve data loading performance.

How does the Microsoft SQL Agent work?

The goal of the MS SQL Agent is to accomplish the following actions:

  1. Fetch data from any source
  2. Generate a CSV file from the fetched data
  3. Send the CSV file to the remote SQL Agent
  4. Execute a BULK INSERT using the CSV file in Microsoft SQL Server

See the diagram below for a more detailed look at how this works.

  1. Metric Insights sends a job request to the Dataprocessor (DP)
  2. DP collects data from a specific source, then generates a CSV file
  3. DP sends the CSV to the Microsoft SQL Agent via HTTPS
  4. SQL Agent saves the CSV file to a local folder
  5. DP executes a BULK INSERT of the CSV through the SQL Agent
  6. DP sends a command to the SQL Agent to delete the CSV file once the dataset is saved to SQL Server

Prerequisites

  • JRE 8+ (Java Runtime Engine) required on Windows environment hosting Microsoft SQL Server
  • SQL Data Storage profile already created in Metric Insights

Install the Microsoft SQL Agent

  1. Go to Admin menu > Collection & Storage > Data Storage > select desired SQL Data Storage profile
  2. In the SQL Data Storage profile, scroll down to the bottom to where it says Data Upload Type > select Agent
  3. Set Agent Endpoint to the SQL Server host (IP or URL)
    • e.g., https://10.50.0.100
  4. Set a password for HTTPS authentication in Agent Password 
  5. Set Microsoft SQL Server CSV folder path to the folder where CSV files will be saved on the SQL Server host
  6. Click [Save & Download Agent] 

After downloading the zip, move it to the Windows machine hosting SQL Server. On the Windows host:

  1. From the zip file, extract the content to the desired location 
    • e.g., C:\MetricInsights\SQLAgent\
    • the location should reflect the location set in the UI 
  2. There are three subfolders extracted from the zip:
    • bin
    • conf
    • lib
  3. In the conf folder, open the application.properties file in a text editor:
    • Ensure a password is set for the parameter server.ssl.key-store-password
    • Ensure the parameter agent.security.http.password is set to the same password that was set in the UI for Agent Password
  4. Now, install the SQL Agent by going to the bin folder and running install.bat with administrator rights. On install completion, the Metric Insights MSSQL Agent Daemon should appear in the list of Windows Services (see image below)
  5. Start the daemon in the Windows Services window, or run start.bat from the conf folder
  6. Create a CSV folder in the same location as specified in the UI
    • Ensure the MS SQL user has read and write permissions to this folder

Metric Insights is now ready to bulk insert large datasets into MS SQL Storage! Test to confirm you can create datasets. If you encounter any issues, please contact [email protected] for help.