SQL Server

What is SQL Server?

SQL Server is a relational database management system developed by Microsoft, known for its scalability, security, and integrated data tools. It supports a wide range of applications, from small-scale projects to enterprise-level solutions, offering features like advanced analytics, in-memory processing, and robust data security.

Why Connect SQL Server to Monte Carlo?

Integrating Monte Carlo with your SQL Server allows you to monitor your data and tables. Monte Carlo can detect anomalous volume, freshness and schema changes by default. With the ability to run SQL queries on your SQL Server tables and views, you can create advanced monitors to analyze the data in your tables. For a full list of supported monitor types see Monitor Support.

After connecting SQL Server here is what you can expect to see:

Freshness and Volume

Our freshness and volume monitors are active by default. By collecting metadata on your tables, Monte Carlo’s machine learning algorithm can set automatic thresholds to alert when a table has gone too long without an update, and when an abnormal amount of rows are added or deleted.

Monitor & Lineage Support

Below are the monitors & lineage support for the SQL Server Integration today. Please reach out to your Monte Carlo representative if you have requirements for additional monitors.

Connecting SQL Server

Now that we know the value connecting SQL Server to Monte Carlo can bring, here are the necessary steps to set up the integration.

📘

Prerequisites

To create a service account, you will need admin credentials to your SQL Server database.

This guide explains how to create a read-only service account for Monte Carlo on SQL Server.

To review all steps necessary to integrate a data warehouse with Monte Carlo, please see here.

Create a read-only service account

Please run the SQL snippet below on your SQL Server database to create a read-only service account for Monte Carlo.

CREATE LOGIN montecarlo WITH PASSWORD = '<password>';

-- Grant access to all current tables. Repeat for all relevant databases.
-- Select permissions are necessary to create custom SQL monitors.

USE <database>;
CREATE USER montecarlo FOR LOGIN montecarlo;
ALTER ROLE db_datareader ADD MEMBER montecarlo;

-- Grant access to view server state to allow for collection of freshness and volume
USE master;
GRANT VIEW SERVER STATE to montecarlo;

FAQs

What is the minimum supported version?

Monte Carlo supports SQL server 2012 and newer.

Does Monte Carlo support connecting to a named instance?

Yes! In order to connect to a named instance:

• Usehostname\instance_name for the --host parameter.
• Make sure your instance is configured with a fixed TCP port.
• Your Data Collector or Agent will need UDP access to port 1434 (the SQL Server Browser service) and TCP access to the fixed port used by the instance.

What hosting solutions are supported?
This integration supports any instance that is reachable from one of our hosting options. Please see the networking docs for more detail.

Are cloud variants of SQL Server like AWS RDS, GCP Cloud SQL, and Azure SQL Server supported?

Yes, AWS RDS, GCP Cloud SQL, and Azure SQL Server are all supported.

What is the db_datareader role?

You can find details about this role here.

What types of SQL Expressions are or are not supported?

We cannot execute SQL with CTEs (queries that contain WITH statements) for rules on SQL Server. Monte Carlo wraps your SQL Statement in a CTE for safety, and SQL Server does not support nested CTEs.