Overview of DMV Tool using DBFS FUSE with Docker Containers on Linux Machine

This article is intended for database administrators and system administrators who plan to configure the DBFS FUSE file system on a Linux machine using the Docker containers. I am going to present the step-by-step procedure for setting up and configuring the DBFS FUSE file system to access local or remote SQL database instances on Linux CentOS docker containers.

Introduction to the Docker, DBFS, FUSE and DMV Tool

To make the most of this material, you’ll need some knowledge of the Docker Containers and SQL Server. I would recommend you refer to the previous articles that explored the topics:

Introduction to Docker and Docker toolbox

Docker and SQL Server on Linux

Now, let’s start with the basic concepts of Docker, DBFS, FUSE, and DMV Tool.


Docker allows us to package and run an application in a loosely isolated environment called a container. Docker is an open-platform engine that facilitates faster delivery, faster development, and faster deployment in an isolated environment.


DBFS (Database File System) is a process of mounting a virtual file system on the top of that database objects using the FUSE technique which provides an interface between a VFS and Kernel module. This setup lets us access those files using dbfs programs.

A big part of SQL Server monitoring is to ensure that SQL Server is performing optimally and it’s highly available. Traditionally, to get system metrics or performance information, one could rely on GUI admin tools such as SSMS, or command-line tools such as SQLCMD to run queries.

Now, there is another option. We can query the information using a new experimental Linux tool known as DBFS. It enables us to access live DMVs mounted to a virtual file system through the FUSE file-system concept. Thus, we can explore information about the database (Dynamic Management Views) using native bash commands.

There is no need to use any other tools or run SQL queries. The meta-data configuration file is used internally by the DBFS process to query the SQL Server databases.


  1. Access data in JSON or CSV format.
  2. Compatible grep, sed, and awk programs.
  3. Real-time DMV data
  4. Cross-platform support (Windows and Linux)

Note: This tool is currently only available for Ubuntu, Red Hat, and CentOS. SUSE coming soon!

DMV Tool

DMV TOOL is a command-line, cross-platform tool that allows for accessing the live data by mounting SQL query output file or DMVs (Dynamic Management Views) to a virtual directory using FUSE and by querying a file structure as an object.

DBFS Concepts

In Linux, everything is a file. FUSE (Filesystem in Userspace) is a loadable kernel program for all Linux distros. It provides a platform for non-privileged users to create their own file systems without even touching the existing kernel code. This is achieved by mounting a file system in a user space through the interface known as FUSE modules.

Diagram Flow Implementation

In this section, the implementation steps are represented in a diagram.

Getting Started

See the following steps to configure the DBFS:

  1. Log in to the console with the SU account (Super User Mode)
  2. Install the wget utility. The GNU Wget is a non-interactive, file retrieval utility for downloading files from the Web or via FTP.
# yum install wget
  1. To get Docker set up on a CentOS, start to download the .rpm package for the corresponding release and install it manually using YUM command. Browse the URL to download the latest docker-ce file for the Docker version that you want to install.

4. Download the rpm package using the wget utility

# wget https://download.docker.com/linux/centos/7/x86_64/stable/Packages/docker-ce-18.03.0.ce-1.el7.centos.x86_64.rpm
  1. Install Docker CE package using the following yum command:
#yum install docker-ce-18.03.0.ce-1.el7.centos.x86_64.rpm
  1. Check the docker package information using the yum info command.
#yum info docker
  1. Use the systemctl command to start Docker, verify and enable the Docker service
#systemctl start docker
#systemctl status docker
#systemctl enable docker
  • Verify that Docker is installed correctly by running the welcome image.
#docker run hello-world
  •  Let’s download the CentOS latest image from the library. Use the Docker Pull command to extract the CentOS image, the community edition of RHEL Linux distros. We can also define a specific image from the list using tags or just append a colon ‘:‘ and type latest that only gives the most current image of CentOS.
#docker pull centos:latest
  • Once the pull is complete, create a container from the extracted image. We’ll do that with a sudo docker run.
# docker run –it centos:latest bash

Here, -it refers to interactive terminal, centos:latest is the reference to the image, and bash defines the shell that will open when the container starts.

Now, we can see the status of all Docker containers with the command docker ps –a

# docker ps -a
  • Let’s install the wget utility
# yum install wget
  • Download the DBFS (dbfs-0.2.5-0) package using the wget utility
wget https://github.com/Microsoft/dbfs/releases/download/0.2.5/dbfs-0.2.5-0.x86_64.rpm
  • Download the EPEL repository using wget utility
#wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
  • Setup the EPEL package
#rpm -ivh epel-release-latest-7.noarch.rpm
  • Install the DBFS package
#yum install dbfs-0.2.5-0.x86_64.rpm
  • Verify the DBFS package information
#yum info dbfs
  • Let’s quit the Docker container session by typing EXIT. Now, commit the Docker image. This commits all the previous changes made to the centos:latest image. The committed image will have the wget utilities, repositories of EPEL and DBFS package.
#docker commit 7af55818f007
  • Add the tag to the committed image using the Docker tag command. Let us name the tag centos:dbfs. We can see the new image using Docker images.
#docker tag 88cd8b6c264890547bc0fa24047f5248f2f6c40b5147a580c18a78018f8c664c centos:dbfs
#docker images

Docker image is ready with the DBFS configuration.

Let’s see how the DBFS FUSE program executes DMVs and Custom queries remotely using the configuration file.

Getting Ready

We will use the image centos7:dbfs in this section. Ensure that this image is available and has the required package and configuration to build the FUSE filesystem.

The following command is executed to launch the container centos:dbfs with a bash console:

#docker run –it –device /dev/fuse --cap-add SYS_ADMIN centos:dbfs bash

To mount FUSE file-system based, we need to combine both –cap-add and –device options with the docker run command. We can mount the FUSE file systems inside a container, but FUSE must be enabled on the host system. Docker will not explicitly enable and load kernel modules on the host.

Let us take a look at the other parameters of the docker run command:

SYS_ADMIN performs a range of system administration operations

–device flag limits access to a specific device that we can use for the container. By default, the container can read and write these devices.

DBFS mount command should not be started as the root user. To start dbfs, you need to create a normal user account. Next, go ahead and create the user “docker” with the useradd command. This command is a low-level utility applied for adding/creating user accounts in Linux.

The useradd command in Linux performs the following things:

  • Edits /etc/passwd, /etc/shadow, /etc/group, and /etc/gshadow files.
  • Creates and populates the home directory.
  • Sets permission and ownership to the home directory.
#useradd docker
#passwd docker

Grant full permissions to the /tmp folder. Let the user docker have full read and write access to all files and associated directories and subdirectories in that /tmp directory – use the command:

#mkdir /tmp/dbfs
#mkdir /tmp/dbfs/sql
#chown -R docker /tmp

The first chown command makes the user docker own the /tmp directory. The second chmod command gives them full read and access permissions. The r gives read permission, the X gives execute permission to directories and not files.

Now, without exiting the session, log in to enter the user session. dbfs should always run at the user privilege. I’m using the docker user credentials which I created above to open a user session. We can see that the pwd is set to /home/docker.

Let’s prepare the configuration file – it requires the following parameters:

  • [Server friendly name]
  • hostname=[HOSTNAME]
  • username=[DATBASE_LOGIN]
  • password=[PASSWORD]
  • version=[VERSION]
  • customQueriesPath=[PATH_TO_CUSTOM_QUERY_FOLDER]

In the following example, I’m connecting to a SQL instance, the Windows Server, HQDBT01.

  • Hqdbt01 is the server’s friendly name.
  • The location of the output file or dump directory would be //CustomQueries/. For example, the /tmp/dmv is the mount directory, then the path to the dump directory would be /tmp/dmv/hqdbt01/CustomQueries/.
  • Hqdbt01 is a hostname of the SQL instance.
  • The username and password are the SQL instance access credentials. I would recommend you define the database user and grant the required permission to that user. Then, the VIEW SERVER STATE permission must enable the user to query DMVs.
  • The version is set to 16 or higher (SQL Server 2016 or later), the fetch the data as JSON. Otherwise, the data would be exported as Tab-Separated Values (TSV).
  • CustomQueriesPath is the path of the SQL file to which the custom queries will be written.

Change the directory to /tmp and create a directory,dmv, the mount directory. Now, FUSE the filesystem as flat files using the dbfs client.

$cd /tmp/
$mkdir dmv
$dbfs –c ./dmvconfig.tool –m ./dmv

The DMV data can also be accessed using various data extraction commands.

$cat dm_exec_sessions

List only those databases where the database recovery model is set to SIMPLE:

$cat databases| grep SIMPLE

List the database name, database information, and sizes of columns of the database where the recovery model is set to SIMPLE. We can see that the output of one program is redirected to another program for further processing. The output is formatted as tab-separated values.

$cat databases| grep SIMPLE | awk ‘{print $1,$2,$5}’| column -t

Configure DBFS to Run Custom SQL Queries

Creating a custom SQL query is pretty straightforward. As per the configuration file, go to the path /tmp/dbfs/sql and write the SQL statement in a SQL file, demo.sql.

#vi /tmp/dbfs/sql/demo.sql

Type the following SQL code in the demo.sql:

SELECT name,user_acces_desc,is_read_only, state_desc,recovery_model_desc from

Save the file by pressing escape :wq

The JSON output is then dumped into a file and mounted to a virtual directory. The thing is, the Docker container doesn’t have any tools installed on the source image. Still, we can fetch the SQL data.

Browse the directory /tmp/dmv/hqdbt01/customQueries

$cd /tmp/dmv/hqdbt01/customQueries/
$cat demo.sql|awk ‘{print $1,$2,$3,$4}’|column -t

Let’s update the demo.sql file with the following SQL statements or SQL of your choice:

SELECT c.session_id
, c.auth_scheme
, c.node_affinity
, s.login_name
, db_name(s.database_id) AS database_name
, CASE s.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncomitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS transaction_isolation_level
, s.status
, c.most_recent_sql_handle
FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id

Browse the directory /tmp/dmv/hqdbt01/customQueries and view the file

$cat demo.sql

The docker image is configured and tested with the dbfs FUSE technique to mount the programs as virtual directories on the Docker container.

Adding an Existing Project to GitHub Using the Command Line

The first step is to save the Docker image to a compressed file. This is the file that we are uploading to the github repository.

#docker save centos7:dbfs > centos7_dbfs.tar

Install Git on CentOS using yum

#yum install git

Let’s initialize or re-initialize the local directory as a Git repository

git init

Add the .tar files to the local repository. This stages them for the first commit

git add centos7_dbfs.tar

Commit the file that we’ve staged in the local repository

git commit -m "centos7 built with dbfs configuration"

Add the URL to the remote repository where the local repository will be pushed

git remote add origin <remote repository URL>

Push the changes in your local repository to GitHub.

git push –u origin master

Wrapping Up

In a nutshell, Docker can get more applications running on the same hardware than other technologies. It makes it easy for developers to create ready-to-run container applications quickly, and it makes managing and deploying applications much easier.

We have installed the Docker engine, provisioned Docker Linux VM, and installed the DBFS FUSE package. We have seen the complete life cycle of the Docker containers. It is that simple to build, use and test programs using the Agile process. Do you agree?

Prashanth Jayaram

Prashanth Jayaram

Prashanth Jayaram is working as a DB Manager for a leading MNC, Database Technologist, Author, Blogger, Automation Expert, Technet WIKI Ninja, and Powershell Geek.

Leave a Reply

Your email address will not be published. Required fields are marked *