SentryOne Document Snapshots in Azure DevOps Pipelines

In this blog, I'll walk you through how to set up a simple Azure DevOps pipeline that runs data source snapshots using SentryOne Document. This will help you document and track changes for your production, development, QA, and UAT data sources. We'll use Azure DevOps to orchestrate the SentryOne Document snapshot whenever a new version of a database is released. I won't go into depth talking about SentryOne Document, but I will give a brief overview of both SentryOne Document and Azure DevOps. I'll also provide an overview and requirements for each tool.

SentryOne Document Overview and Requirements

SentryOne Document allows you to generate documentation from various sources. You can easily maintain up-to-date documentation to ensure compliance rules and regulate data privacy by tracking data lineage. It is an easy-to-access and easy-to-maintain tool used to streamline data management tasks such as documentation. Below is a list of data sources you can document with SentryOne Document.

• Data Source
• SQL Server
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
• Power BI
• Excel
• Tableau

SentryOne Document has a remote agent that will run the database snapshot on the target data source. The remote agent installer is also packaged with a configuration tool which can be used to configure our data sources. The remote agent is lightweight and can be run on a VM and hosted on-prem or in the cloud.

Requirements

Below are the system requirements for the remote agent, and here is the accompanying SentryOne documentation link.

• Compatible Browsers
  • Chrome
  • Edge
• Remote Agent Requirements
  • Hardware
    • Recommend a relatively recent 64-bit processor with 8+ GB of RAM
  • Software
    • Windows PC with .NET 4.6.1 installed

Permission Requirements

When setting up a service account to run the remote agent, it is recommended that the agent has network access and permissions to all the data sources that you want to scan. In our case, we will be using SQL Server as our data source, which will require the following permissions.

SQL Server permission set:

• Definition permissions for any object you want to document
• Non-sysadmin users must be granted View Server State permissions to view log file objects within the documentation.

If you are using another data source mentioned above, here is a link to our documentation that shows you the required permission set for the remote agent service account.

Now that we are finished talking about SentryOne Document and its requirements, let's move onto the installation and configuration of the remote agent.

SentryOne Document Remote Agent Configuration and Installation Steps

At any time during the installation and configuration, if you run into any errors or have any questions, please feel free to reach out to our SentryOne support team.

Installation:

To install the remove agent log onto the SentryOne document portal and choose the “Download Config & Remote Agent” button.

Drop the installer on the VM that will be running the remote agent and open the executable. Read through the Setup Wizard overview and click next.

Acknowledge the SentryOne Document license agreement and click next.

Use the default installation path or select an alternative installation path for SentryOne Document install and click next.

The installation is ready to be completed, click the install button.

The ‘Launch SentryOne Document Remote Agent Configuration Tool’ option is enabled by default. Deselect the checkbox if you don't want the configuration tool to open automatically. If you deselected this option, then search your system to “SentryOne Configuration” and open the configuration tool.

The configuration tool will prompt you for the “Service Instance Name” and “Target Host URL.” The Service instance name is the name of the VM where you installed SentryOne Document. The ‘Target Host URL’ is set to ‘https://document.sentryone.com’ by default.

You will be prompted to enter the credentials for the service account that will run the agent.

On the next screen, you will have an option to set the maximum concurrent tasks and the directory where the local cache will be stored. By default, the maximum concurrent task is set to 4, but feel free to change that value. The local cache is stored on the C:\drive in the SentryOne directory.

The following screen will prompt you to authenticate to the SentryOne Document URL.

Once you have authenticated, you will be ready to install the SentryOne Document Configuration tool.

On the next screen, you’ll have a few options and you’ll want to run those in the order specified below.

Options:

1. Start Service—Select Start Service to activate the remote agent on your machine.
2. Start License Manager—Open the SentryOne License Manager. Enter a new license for your SentryOne Document remote agent service.
3. Configure Solutions—Select Configure Solutions to open the SentryOne Document configuration tool. Use the SentryOne Document configuration tool to configure solutions for your environment. Add Solutions and Solution Items with the configuration tool in the same way as the SentryOne Document Client. For full instructions on adding these, see Adding Solutions and Adding Solution items.

On the license manager tab, you'll be prompted to enter the license for SentryOne Document and to authenticate. Once the license is configured, you can move onto configuring solutions tab.

Click on the configure solution button and a new window will pop up. A solution is a collection of information sources that are used to provide data for documentation and analysis. Click the ‘Add Solution’ button and name your solution. For the purposes of this demo, I'll name my solution ‘Test.’

Next, you'll be able to add an item to the solution, which will be the data source. In this case, you'll call the item ‘AdventureWorks2016” and select the ‘SQL Server’ as the source type. On the bottom of the screen, it will ask for the server name. Once you enter that information, test the connection to the SQL Server instance via the service account.

You'll receive a message stating that the data source was successfully added to the SentryOne Document Solution. You can add multiple items to a single solution, but in this case, you'll add a single SQL Server data source. There are two options for authentication to the SQL Server instance: integrated and SQL Server authentication. In this case, you'll be using integrated authentication. Once finished, test the connection and click the ‘Ok’ button.

Note, there are two checkboxes in the image above, and the descriptions are listed below:

• Only update this item as part of a manual snapshot—Selecting this checkbox only updates the solution item when you choose to snapshot the solution manually.
• Snapshot this item using a 32-bit process—Selecting this checkbox uses a 32-bit process to snapshot the solution item. Note: A more efficient 64-bit process is used to snapshot solution items by default.

Now that you have an item added to our solution, you can log onto the SentryOne Document portal and capture the CLI commands that you will use in our pipeline. When you log into the SentryOne Document portal, you’ll see your solution(s) listed. On the right side of the solution, you’ll see a few icons. The icon that looks like a camera will be used to take snapshot of the data source with specified in our solution.

When selecting the snapshot icon, another window will open with the CLI commands that you need to automate and run our snapshots. The first CLI command is to authenticate the service account that is running the remote agent. The second CLI command will execute the snapshot.

Copy the CLI commands to a text editor and save them for later. To get SentryOne Document snapshots running in a pipeline, you have another step to complete. Log back into the VM that is running the remote agent and open CMD as administrator. Change the directory to where you installed the SentryOne Remote agent, and then run the first CLI command.

Commands:

Change directory: CD “C:\Program Files (x86)\SentryOne\SentryOne Document Remote Agent”

Authentication command: SentryOne.Document.CommandLine.exe auth -t https://document.sentryone.com

After running the authentication command, a new browser window will appear, which you will need to authenticate once more. After successfully authenticating, you are finished with the install and configuration of SentryOne Document. Next, you'll move onto setting up an Azure DevOps pipeline to run your snapshots.

What is Azure DevOps?

Azure DevOps is a product provided by Microsoft, formally known as Team Foundation Server and Visual Studios Team Services. This tool provides version control, reporting, requirement management, project management, automated build and release management. The tool covers the software development lifecycle and enables DevOps capabilities. Azure DevOps can orchestrate continuous builds and releases. In this case, you will set up a continuous deployment trigger that will take snapshot of your data source is when it is released.

Setting up an Azure DevOps Pipeline

I’m not going to go into much detail on the build side of packaging up database upgrade scripts and integrating them into your project. I’ll mainly focus on setting up a release side of the pipeline that will take a snapshot of your data source. The diagram below shows the entire pipeline from beginning to end. The red highlighted section is where you'll set your focus.

You need to log into Azure DevOps and select the project you are working on. Next, you'll need to add an Azure DevOps agent to the VM that is running the SentryOne Document Remote agent. The reason for this is so you can orchestrate the SentryOne Document CLI commands via the Azure DevOps agent. Navigate to the releases section in Azure DevOps and select ‘Deployment groups.’ On top of the screen, click ‘New Deployment Group’ and enter the name and description, as shown below.

The ‘Use a personal access token in the script authentication’ option under the PowerShell snippet will need to be selected. Copy the PowerShell script to your clipboard and log onto the same box that you installed the SentryOne Document remote agent and open PowerShell as administrator. When running the script in PowerShell, it will ask for some information as specified below:

• Enter deployment group tags for agent: Y
• Enter comma to separate list of tags: You can tag the agent however you want, but I tagged my agent “SentryOneDocument.”
• Enter the service account you want to run the agent: I used the same service account as the SentryOne Document remote agent. Feel free to create another service account to run the Azure DevOps agent.
• Enter the password for the account: Enter password for service account.

You can verify that the Azure DevOps service is running by opening services and checking to see if ‘Azure Pipelines Service’ is running. Now that the Azure DevOps Agent is set up, you can authenticate the service account that runs the Azure DevOps Agent to the SentryOne Document portal. Open a command prompt window as administrator and change directory to where you installed SentryOne Document. Run the authentication command and a browser window will open where you can authenticate the service account. Please note if you do not have an account set up for the service account, you'll need to create an account via the SentryOne Document portal.

Command: CD “C:\Program Files (x86)\SentryOne\SentryOne Document Remote Agent”

Authentication command: SentryOne.Document.CommandLine.exe auth -t

Once you have authenticated with the service account, you can log back into Azure DevOps and navigate to the releases section. On the ‘Releases’ screen in the top left you'll see a plus sign next to the words ‘New.’ Select to create a new release pipeline and then for the ‘Select a template’ section type ‘Empty job,’ click create.

Click the ‘Empty job’ and name it SentryOne Document Snapshot.

Click the jobs link inside of the SentryOne Document snapshot stage.

Remove the Agent job by selecting it and clicking the delete key. Click the ellipses button on the stage and select “New Deployment Group job.”

On the Deployment Group job, add the display name and the deployment group that you created earlier. Those are the only settings that you have to set up on the deployment group job.

Next, you need to add a command line job to the ‘SentryOne Document’ job. Click the ‘Plus’ sign on the right side of the deployment group job and search for ‘Command line.’ Add the CMD task to the deployment group job and name it ‘Snapshot.’ Copy the SentryOne Document snapshot CLI command that you copied earlier into the ‘Script’ window. If you did not copy the script to run the snapshot from earlier, then log into the SentryOne document portal and click on the camera icon next to your solution.

There should be two CLI commands: the first command is to authenticate, and the second command is to run the SentryOne Document snapshot against the solution. If you expand the ‘Advanced’ node on the CMD task, you’ll see a place to enter the ‘Working directory.’ Enter the directory where you installed SentryOne Document and check the ‘Fail on standard error’ checkbox.

Save the job and create a release by clicking on the ‘Create release’ button on the top right of the screen. A new window will open where you can specify which stage you'd like to deploy. If you want to deploy the release automatically, do not select the ‘SentryOne Document Snapshot’ stage. Click create at the bottom of the screen.

Once the instance of the release is created, you’ll see a link to the instance at the top of the screen. Click on the link to view the running release. On the instance of the release, you can view the logs for troubleshooting purposes. If you’d like to see the logs of the stage, click on the logs button under the stage you deployed.

You’ll notice if the stage is highlighted in green, then the deployment was successful, however, if there is a red highlight around the stage, then the deployment failed, and you’ll need to troubleshoot. Now you can log onto the SentryOne document portal and click on the ‘Clock’ sign next to the solution.

Here you can see the snapshot in progress against our solution. Note, that sometimes a deployment will succeed in Azure DevOps, but the snapshot will fail in logs section of the SentryOne Document portal. To troubleshoot this, select the task that failed and download the log file. If you’re having trouble diagnosing the error message, then feel free to submit a support ticket at SentryOne.

If you want these snapshots to run on a predefined schedule, you can set that up back on the SentryOne Document release definition. Click on the ‘Schedule set’ under the artifact node and enable the schedule release trigger.

As an example, let's say that new versions of your data source are released every Wednesday at noon. Therefore, you'll want to schedule a snapshot to run every Wednesday at 1 PM. Toggle the ‘Schedule’ switch and set your schedule.

Great! You have successfully run a SentryOne Document snapshot against your solution and set up a schedule.

Conclusion

You’ve just set up SentryOne Document snapshots in an Azure Release Pipeline. This snapshot captures an instance-wide view of your data source at a specific point in time. These snapshots can be run on a schedule or manually by creating a deployment of your release. These snapshots can be run after a new version of your data set is released.