SQL Sentry + Database Mapper = Environment Map

Jason Hall

Published On: May 12, 2021

Categories: Data Lineage, Monitoring, Documentation, Environment Map 0

The Problem

You’ve solved database performance monitoring and documentation. Unfortunately, now you get lost between them.

What Is Environment Map?

Environment Map is a feature within SolarWinds® Database Mapper (formerly SentryOne Document) designed to integrate with SQL Sentry®. It leverages the lineage engine from Database Mapper and the detailed environment, performance, and workload data from SQL Sentry to assemble an interactive node map. This map lets you navigate your entire database estate to answer questions such as the following:

  1. Which applications are using which databases?
  2. Which users and workstations are running workloads against databases?
  3. Which database objects aren’t in use?
  4. How have database usage patterns changed over time?
  5. Which databases, users, and related objects have been added or removed?

Environment Map is a different and intuitive way to navigate your data platform. If you’re using SQL Sentry and Database Mapper, you just need to turn on this feature. In this article, I’ll show you the quickest way to get up and running with Environment Map.

What You'll Need

Environment Map lives at the junction of SQL Sentry and Database Mapper. To make use of Environment Map, you'll need to have both products deployed.

If you're reading this blog post, you likely already have one or both products deployed. In case you don't, let’s take a look at how you can quickly get started with a free trial.

  • SQL Sentry—You can sign up for a free SQL Sentry trial here. Then, follow the installation instructions in this guide.
  • Database Mapper—Begin a free Database Mapper trial by signing up here. It’s important to note you have a choice when deploying Database Mapper. You can deploy it to your own on-premises or hosted infrastructure, or you can use Database Mapper online with our software as a service (SaaS) platform.

    If you choose to deploy with your own infrastructure, follow these installation instructions. With an on-premises deployment, you'll need a Windows workstation or server, and you’ll need to add and configure Internet Information Services (IIS). You’ll also need a SQL Server database for your snapshot data.

    If you choose a SaaS deployment, you'll only need to install the remote agent to help manage your Database Mapper solutions and gather metadata from your data sources. With a SaaS deployment, you’ll only need a Windows system to run the Database Mapper remote agent from. For a large and/or complex deployment, you can configure multiple agents in pools. For the sake of this blog post, we'll put everything on the same system.

Getting Set Up

I skipped through installation because someone better than me already wrote great documentation on deployment. Assuming you have everything working, start by making sure SQL Sentry is up and running, and then I’ll walk you through how to set things up in Database Mapper.

SQL Sentry

Once SQL Sentry is installed, you'll be asked to add targets to watch. Go ahead and add the targets you’re interested in. If you don't have anything in mind, start with the SQL Server you provisioned for SQL Sentry. You can monitor it for free!

You need to make sure SQL Sentry is squared away first because Environment Map works off rollup data from SQL Sentry. If you installed SQL Sentry for the first time just now, it's going to be a few minutes before rollup data exists. You can do this first and let it run while you configure your Database Mapper solution for Environment Map.

Below, you can see the two targets I've added beneath my default SQL Sentry "site."

SQL Sentry Database Mapper Environment Map_Image 1

If your navigation pane looks similar, you should be ready to take your snapshot by the time you’ve gotten everything for it configured.

Solution Configuration

With SQL Sentry doing its thing, you can focus on Database Mapper.

In the Solution Configuration tool shown in the screenshot below, you can see I've added a solution called "Jason's SQL Sentry." In Database Mapper, a solution is a logical container meant to encapsulate connections to data sources that are linked or related in meaningful ways.

SQL Sentry Database Mapper Environment Map_Image 2

I had to answer a few questions to get the solution added. I'll review the less obvious ones below.

After you name the solution, you're asked to add the first data source, and you need to choose a type for the source. There are many options, but for now, you're only interested in the one for Environment Map.

SQL Sentry Database Mapper Environment Map_Image 4

Choosing the correct source type ensures you target the SQL Sentry database rather than the higher-level SQL Server on which it resides.

Next, you'll be asked for credentials. You'll need a user account capable of accessing the SQL Sentry database and executing stored procedures. In this example, I'm using Windows integrated authentication.

SQL Sentry Database Mapper Environment Map_Image 5

In the Database drop-down menu, make sure you choose the SQL Sentry database. Otherwise, solution setup can't complete.

Next, you’ll choose OK and then OK again on the following screen. Then, your solution will be added to the list.

Database Mapper

Database Mapper is a web browser-based application, so you’ll want to open your browser and navigate to the client URL. For a SaaS deployment, this will be at document.sentryone.com. For an on-premises deployment, the default URL is http://localhost:44302. You can change this in IIS, but make sure you know what you intend to do. It might be beneficial to contact support for guidance if you're nervous about changing it.

Your first view of the client should be slightly familiar.

SQL Sentry Database Mapper Environment Map_Image 6

These are the same solutions from the Solution Configuration tool! They’re all there, including your new solution, thanks to the magic of the Database Mapper API.

Typically, I’d use a command-line interface automated through continuous integration and continuous delivery (CI/CD) pipelines to generate my snapshots, but today I'll do a manual snapshot and see if SQL Sentry is ready for us.

SQL Sentry Database Mapper Environment Map_Image 9

Notice I've enabled enhanced logging so I can view more detail on snapshot progress. Large SQL Sentry databases with many diverse targets can take an extended amount of time to complete snapshots.

SQL Sentry Database Mapper Environment Map_Image 8

The snapshot, documentation, and lineage generation happen asynchronously, and overhead is kept to a minimum. At the same time, enhanced logging can help you understand what happened if anything goes wrong during the snapshot and lineage generation processes.

Now, simply press the Start Snapshot button, close the dialog box, and navigate to the log.

By the time I wrote the previous paragraph and went to check my snapshot, it was already finished. I took a minute to review the log; then, I was ready to use Environment Map.

SQL Sentry Database Mapper Environment Map_Image 10

Using Environment Map

Environment Maps are accessed via the Lineage feature in Database Mapper. It’s considered to be lineage analysis for a SQL Sentry deployment.

Two options on the lineage viewer will help you navigate Environment Map:

It’s in our nature as analytical people to want more detail. These settings help control this in the lineage viewer. A word of caution—when we say it increases the granularity and detail, we really mean it. At the highest granularity and detail settings, a busy database environment won’t be something easily consumable by a human. If you still want this level of detail, I suggest switching to the Text View.

SQL Sentry Database Mapper Environment Map_Image 11

This is a view starting from a connected client that I didn’t know connects to this database. While writing this walkthrough, I discovered something I need to understand better!

SQL Sentry Database Mapper Environment Map_Image 12

This is one great thing about Environment Map. When I come back to the lineage view in a few weeks, I can see if the client is still there or if there are any new clients showing up.

Increasing your granularity level includes more database objects. Here, you can see stored procedures and tables.

SQL Sentry Database Mapper Environment Map_Image 13

Increasing the dependency level widens the scope of the view, as shown below.

SQL Sentry Database Mapper Environment Map_Image 14

Note how this view, at the highest dependency level, is almost too busy to use. You rearrange things and expand nodes, but a cleaner option would be to use the menu option to toggle on text view. Text view changes the node map to a tree structure, making it easier to navigate larger hierarchies. Lowering the dependency level would allow you to remain in the node map with a more digestible view.

Wrap-Up and Next Steps

At this point, you’ve successfully configured SQL Sentry and Database Mapper to provide you with a comprehensive map of your database estate.

That isn't all, though! From here, you can work with documentation directly related to Environment Map. The screenshot below is documentation output listing the known clients connecting directly to this database.

SQL Sentry Database Mapper Environment Map_Image 15

Environment Map is a great way to navigate the topology, architecture, and usage patterns for your data estate. The value of having these details at your fingertips is hard to measure, and Database Mapper provides it to your entire team. Environment Map will help guide you to a clear understanding of your environment, how it’s used, and who’s using it.

Jason has worked in technology for over 20 years. He joined SentryOne in 2006 having held positions in network administration, database administration, and software engineering. During his tenure at SentryOne, Jason has served as senior software developer and founded both Client Services and Product Management. His diverse background with relevant technologies made him the perfect choice to build out both of these functions. As SentryOne experienced explosive growth, Jason returned to lead SentryOne Client Services, where he ensures that SentryOne customers receive the best possible end to end experience in the ever-changing world of database performance and productivity.