A New, On-Premises Approach to Documentation and Data Lineage: Database Mapper Software
Published On: May 21, 2020
Categories: SQL Server, Database Documentation, DataOps, Database Mapper 0
Back in November 2019, we announced the general availability of Database Mapper, our cloud-based documentation and data lineage analysis solution. Since Database Mapper was released, the SentryOne team has been working hard to expand and improve its feature set.
"Wait a minute, John. We're not ready to move to the cloud yet!"
Well, that's not a problem. Not only have we been creating exciting new features, but we are also now providing a version of Database Mapper that you can install locally in an on-premises environment. In this blog post, I'm going to walk through the simple step-by-step setup and configuration process.
Database Mapper Prerequisites
There are a few requirements to get up and running without a hitch:
- A Windows server with IIS installed and Windows Authentication enabled for hosting the Database Mapper web application host and API.
- A SQL Server database instance for hosting the Database Mapper database.
- Microsoft .NET Core Hosting Bundle. This is required to host a .NET Core web application in IIS. If the .NET Core hosting components are not installed on the server, the installer will automatically retrieve the hosting bundle and install it.
Also, depending on your desired configuration, you might need:
- A Windows/Active Directory (AD) account that has access to the server hosting your SQL Server instance.
- A SQL Server login for Database Mapper created on your SQL Server instance.
We provide a bundle, which will install the following components:
- Database Mapper host and API web applications
- SentryOne License Manager
- .NET Core Hosting Bundle (if not already present on the system)
We will focus on the Database Mapper host and API setup, as it contains a couple of key configuration screens.
First, accept the EULA (after reading it, of course) and select an install location. By default, we install to %ProgramFiles(x86)%\SentryOne\SentryOne Document\.
The next window is the Database Server Configuration. You will enter the server and instance name of your SQL Server database, and if you are using a SQL Server login for Database Mapper, you can enter the username and password as well. If you prefer to use Integrated Security (Windows Authentication), just leave the Username and Password fields blank.
You will need to test the connection before proceeding; you can click Next to move on after testing the connection. If you encounter any connection issues, double-check the credentials entered and ensure there are no firewall rules blocking the connection.
Finally, set the hostname for your Database Mapper instance. This would typically be your server's hostname, but depending on your network's configuration, you might need to include the FQDN (fully qualified domain name), such as MyWebServer.MyCompany.domain. If you're not sure, this can easily be changed in IIS later.
Once these screens have been completed, the installation can proceed.
If you are using a single-server setup (i.e., you are installing the Database Mapper Software on the same server as the one on which your SQL Server instance is hosted) with Integrated Security, everything should work like a charm. Your Database Mapper Software instance will be available at the following URL:
You won't have any data yet, but the Solutions page should load successfully:
You can jump ahead to Next Steps.
If you are using a multiple-server setup (i.e., the Database Mapper web application is hosted on a different server than your SQL Server instance), and you want to use Integrated Security (Windows Authentication), there are a couple of extra steps required.
If you are running a multiple-server setup with Integrated Security, there will be a small change required in IIS. The Database Mapper API will need to be run under the identity of a Windows/AD account, which allows access to the remote SQL Server instance.
- Open the IIS Manager by entering inetmgr in the Windows search bar or the Run dialog.
- Expand the Server name and click Application Pools. You should see Database Mapper API listed in the Application Pools. Right-click it and click Advanced Settings.
- Scroll down to the Process Model heading and you should see the Identity setting.
- By default, it is set to Network Service. Click Network Service and a button should appear in the field. Click this button to open the Application Pool Identity dialog.
- Select Custom Account, click Select, and enter the credentials for your Windows/AD account.
SQL Server Configuration
On the SQL Server, you will need to make sure that the application can access the database. Connect to the instance using SQL Server Management Studio (SSMS) and open a new query window.
If you are using Integrated Security, run the following query to create the required login on the server, substituting in the appropriate domain/username as needed:
EXEC sp_grantlogin 'DOMAIN\Username'
Whether you’re using a SQL Server login or Integrated Security, to grant access to the Database Mapper database, you will need to run these queries (again, substituting the SQL Server login or Windows/AD username as needed):
Use [SentryOneDocument] EXEC sp_grantdbaccess '[DOMAIN\Username]', 'SentryOne Document' EXEC sp_addrolemember 'db_datareader', 'SentryOne Document' EXEC sp_addrolemember 'db_datawriter', 'SentryOne Document' EXEC sp_addrolemember 'db_ddladmin', 'SentryOne Document' GRANT EXECUTE TO [SentryOne Document]
These queries grant the user access to the Database Mapper database and assign it the db_datareader, db_datawriter, and db_ddladmin roles on that database only.
At this point, you should be able to browse to the Database Mapper instance using the URL below:
Now that you have set up Database Mapper, you can begin documenting your databases. You will need to configure the Database Mapper remote agent and create a solution. These steps are the same for both the cloud version of Database Mapper, and the on-premises Database Mapper Software.
The remote agent can be installed on your web server, database server, or on a separate server altogether. Our advice is to ensure that it is installed in a location in which it has consistent access to both the web server and the database. For example, a laptop would not be ideal, as it probably won't be switched on all the time or might need to be connected to a VPN to access the web server and database.
Once you have the remote agent configured and your first solution created, you can start documenting your data sources and browse your documentation from anywhere on your very own Database Mapper instance!
Get Started with Database Mapper Software Today
I hope this blog post helps you get up and running with Database Mapper Software with minimal fuss, and that you'll see how powerful the documentation, lineage, and data dictionary features are for tracking changes made to your data sources or for ensuring compliance in your data environment.
Take Database Mapper Software for a spin on your own and start a free 30-day trial.
For more information about how to use Database Mapper’s features, be sure to check out our documentation and videos!