Integrating Monte Carlo with SQL Server allows you to monitor your data assets through custom SQL monitors, which can be created in either the UI wizard and/or programmatically via monitors as code (API/SDK too). These monitors can be used to generate notifications (incidents) to relevant stakeholders and circuit break pipelines.

Integration Setup

To connect Monte Carlo to a SQL Server database, follow these steps:

1. Enable network connectivity between the database and Monte Carlo's data collector.
2. Create a read-only service account with the appropriate permissions on your database.
3. Provide service account credentials to Monte Carlo (requires Using the CLI).

1. Enable network connectivity

Follow the Network Connectivity guide to IP Allowlist or VPC peer to provide access to SQL Server.

2. Create a read-only service account

📘

Prerequisites

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

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;

3. Provide service account credentials to Monte Carlo

1. Please follow this guide to install and configure the CLI.
2. Please use the command montecarlo integrations add-sql-server to connect. For reference, see help here.

montecarlo integrations add-sql-server \
	--name aloy-dev \
	--host sample-cluster.123456789.us-east-1.rds.amazonaws.com \
	--user montecarlo \
	--password -1

Monitor Support

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

Category	Monitor Type	Support
Automated Anomaly Detection	Freshness
Automated Anomaly Detection	Volume
Automated Monitoring	Schema Changes
Opt-in Monitor	SQL Rule
Opt-in Monitor	Comparison Rule
Opt-in Monitor	Field Quality Rule
Opt-in Monitor	Freshness Rule
Opt-in Monitor	Volume Rule
Opt-in Monitor	Field Health Monitor
Opt-in Monitor	Dimension Tracking
Opt-in Monitor	JSON Schema Changes

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 the AWS cloud (e.g. AWS hosted, site-to-site VPN with AWS, publicly accessible, etc). 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 for rules on SQL Server. Monte Carlo wraps your SQL Statement in a CTE for safety, and SQL Server does not support nested CTEs.