Skip to main content

Mine Microsoft SQL Server

Connect docs via MCP

Once you have crawled assets from Microsoft SQL Server, you can mine its query history to construct lineage between your database assets. Atlan connects to SQL Server and reads its Query Store directly, with no export or upload step.

To mine lineage from Microsoft SQL Server, review the order of operations and then complete the following steps.

Prerequisites​

Before you begin, make sure you have:

  • Crawled assets from Microsoft SQL Server so that connections are available for mining
  • Administrative access to your Microsoft SQL Server instance, or contact with your SQL Server administrator, to enable Query Store and grant permissions

Enable query store and grant permissions​

Query Store captures the executed-query history that Atlan mines. It's available from SQL Server 2016 and later, and is always on for Azure SQL Database and Azure SQL Managed Instance. On the box product (SQL Server 2016+), it's off by default.

  1. For each database you want to mine, enable Query Store and set the capture mode to ALL for full coverage:

    ALTER DATABASE <DB> SET QUERY_STORE = ON;
    ALTER DATABASE <DB> SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

  2. The miner reuses the credentials you configured for the crawler. Grant that login VIEW DATABASE STATE on each database you want to mine, so it can read the Query Store catalog views:

    USE <DB>;
    GRANT VIEW DATABASE STATE TO <user>;

    Databases where the login lacks this permission are skipped, and the miner mines the rest.

info

Query Store retains history for a limited window (governed by STALE_QUERY_THRESHOLD_DAYS and its allocated storage). Queries executed before Query Store was enabled, or already evicted from the retention window, can't be mined. Enable it ahead of the period you intend to mine.

Select miner​

To select the Microsoft SQL Server miner:

  1. In your Atlan workspace, click Connectors in the left sidebar.

    • If you are using the Old UI (Classic), click New Workflow in the top navigation.

  2. Click Marketplace.

  3. Search for SQL Server Miner and select it.

  4. Click Install.

  5. Once installation completes, click Setup Workflow on the same tile.

Configure miner​

To configure the Microsoft SQL Server miner:

  1. For Connection, select the connection to mine. (To select a connection, the crawler must have already run.) The miner reuses the crawler connection's credentials.

  2. For Miner Extraction Method, choose your extraction method:

    • In Query History, Atlan connects to your SQL Server instance and mines query history directly.
    • In Self-Deployed Runtime, Atlan uses a Self-Deployed Runtime deployed within your network to mine query history. For details on deploying the runtime, see:

  3. For Start date, choose the earliest date from which to mine query history. The miner mines from this date up to now, across every database on the connection that has Query Store enabled. It defaults to two days ago, and you can select up to 30 days back.

    info

    Atlan can only mine queries that Query Store has captured and still retains. A start date earlier than when Query Store was enabled, or older than its retention window, returns only the history that's still available.

  4. (Optional) To check for Query Store availability or permission issues before running, click Preflight checks. The check reports which databases are usable and lists any that aren't (Query Store disabled, in a read-only or error state, capture mode NONE, or missing VIEW DATABASE STATE), with the reason.

warning

If running the miner for the first time, Atlan recommends setting a start date roughly three days prior to the current date and then scheduling it daily. Atlan requires a minimum lag of 24 to 48 hours to capture all the relevant transformations that were part of a session. Learn more about the miner logic here.

Run miner​

To run the Microsoft SQL Server miner, after completing the configuration:

  • To run the miner once, immediately, at the bottom of the screen, click the Run button.
  • To schedule the miner to run hourly, daily, weekly, or monthly, at the bottom of the screen, click the Schedule & Run button.

Once the miner completes running, you see lineage for SQL Server assets built from the queries executed between the start date and when the miner ran! 🎉

See also​