{"id":77647,"date":"2018-03-20T19:40:08","date_gmt":"2018-03-20T19:40:08","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77647"},"modified":"2021-08-24T13:39:20","modified_gmt":"2021-08-24T13:39:20","slug":"mount-sql-server-dmvs-custom-queries-using-dbfs-linux","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/mount-sql-server-dmvs-custom-queries-using-dbfs-linux\/","title":{"rendered":"Mount SQL Server DMVs and Custom Queries Using DBFS on Linux"},"content":{"rendered":"<p>Given that Microsoft now considers Linux a \u2018first-class citizen\u2019, SQL Server 2017 offers native support for Linux. It is engineered to be cross-platform, and that\u2019s good news for Linux administrators and open source developers. Microsoft\u2019s support for Linux (and open source in general), has come into clearer focus, and the company\u2019s mission now seems to be all about bringing its tools to wherever its users are.<\/p>\n<p>Microsoft has been known for proprietary software, and this openness to the open source community came as a surprise; the development is quite recent, and one in the right direction. SQL Server has had a myriad of GUI and CLI tools. These tools have also, along with SQL Server itself, have become cross platform. These tools are almost equally as good on Linux as Windows, and they help effectively manage, develop, and administer SQL Server. Some of the tools are:<\/p>\n<ul>\n<li>SSMS<\/li>\n<li>Sqlcmd<\/li>\n<li>Bcp<\/li>\n<li>Sqlpackage<\/li>\n<li>Mssql-conf<\/li>\n<li>Mssql-scripter<\/li>\n<li>DMV tool<\/li>\n<li>SQL Operations Studio<\/li>\n<li>MSSQL-Cli<\/li>\n<li>SQL Extension for VS code<\/li>\n<\/ul>\n<p>At first, it seems like a myriad of them. Taking one piece at a time helps with understanding the tools. Let us try to classify these tools based on what they are useful for:<\/p>\n<table class=\"table--tight\" border=\"1\">\n<thead>\n<tr>\n<td>&nbsp;<\/td>\n<td>\n<p><strong>SSMS<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Sqlcmd<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>bcp<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>SqlPackage<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Mssql-conf<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Mssql-scripter<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>DMV<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>SQL Ops Studio<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>MSSQL-Cli<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>SQL Extension for VS Code<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p><strong>Cross-platform support<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>SQL development<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>Administration and configuration<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>Continuous Integration &amp; Continuous Deployment<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>Data Loading, Extraction and Migration<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p><strong>Miscellaneous Tools (Wizards and Monitoring Solutions)<\/strong><\/p>\n<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<td>&nbsp;<\/td>\n<td>&nbsp;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>To have a better understanding of what to use these tools for, consider the following points:<\/p>\n<ol>\n<li>If you want to manage SQL Server instances or databases, use customized widgets, integrated terminals such as PowerShell and Bash in a light-weight editor on Windows, Linux or Mac then Microsoft SQL Ops Studio would be the tool of choice.<\/li>\n<li>If you are used to SSMS and Windows, and love to manage SQL Server instances or databases with the most traditional way with full GUI (Graphical User Interface) support then SQL Server Management Studio (SSMS) is a good choice.<\/li>\n<li>Choose SSDT (SQL Server Data Tools) to create or maintain database code, and have designer support on Windows<\/li>\n<li>Use Visual Studio Code and mssql extension to write T-SQL scripts in a light-weight editor across platforms: Windows, Linux or Mac<\/li>\n<li>If you are working with a Linux SQL Server instance and would like to change the configuration script for Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu, then mssql-conf utility can be used to set the various server and database level parameters<\/li>\n<li>The tools such as Visual Studio SQL Server Data Tools (SSDT), mssql-scripter, sqlcmd, bcp, sqlpackage are SQL Server DevOps tools are used to build applications in a transparent and flexible way.<\/li>\n<\/ol>\n<p>In this article, I would like to talk about the \u2018DMV TOOL\u2019, a command line, cross-platform tool that allows accessing live data by mounting DMVs (Dynamic Management Views) to a virtual directory using FUSE and by querying file structure as an object. This article covers the following aspects:<\/p>\n<ul>\n<li>Introduction to the DMV tool<\/li>\n<li>Explain DBFS concepts<\/li>\n<li>Step by step installation of DMV tools<\/li>\n<li>Discuss configuration details<\/li>\n<li>Data extraction process<\/li>\n<li>How to Setup DBFS to run custom queries<\/li>\n<li>and more\u2026<\/li>\n<\/ul>\n<p>The DMV data can also be accessed using various data extraction commands that will be discussed later in this article.<\/p>\n<p>The two most common ways of interacting with systems are the graphical interface (GUI) and the command-line interface (CLI). The former makes interaction easy, but sometimes at the cost of efficiency; the latter has a learning curve, but once you\u2019re comfortable with the command line, you skyrocket in terms of efficiency.<\/p>\n<p>The advent of these tools supercharges the command line interface itself. In general, it\u2019s known that going away from the GUI and using CLI increases efficiency. You really need to start working with command line tools in order to get a taste of efficient management.<\/p>\n<p>Using the DMV Tool, the SQL queries that fetch metadata can be run without the need to directly log into the SQL instance. The tool uses the database configuration file to facilitate database administrators to write commands to fetch the metadata of the SQL instance without directly accessing the underlying system objects. We\u2019ll go through the how-to later in this article.<\/p>\n<h2>DBFS Concepts<\/h2>\n<p>Database Filesystem (DBFS) uses the FUSE module and creates zero-byte files for each of the DMVs. One of these returns the data in a CSV format and the other in JSON. When a file is read from within the filesystem, the corresponding DMV is queried for information, and the information is displayed as though simply opening a CSV or a JSON file.<\/p>\n<p>One of the major contributions of the Open Source Foundation (OSF) is the \u2018everything is a file\u2019 concept. The idea here is that you can envision the interface object\u2014in this case, the DMVs\u2014as directory file system. When the filesystem is mounted, the programs can read data from each DMV file.<\/p>\n<p>Knowing the architecture gives an idea of the metadata about the SQL instance. One\u2019s proficiency with scripting also comes into picture. Some of us write BASH or PowerShell scripts to get data. However, traditional DBAs may prefer fetching the metadata using a SQL query rather using a script. This is because, when we deal with aggregation or data crunching, scripting seems overpowering, and overall, it is inefficient for the job at hand. That brings us to taking advantage of writing a custom query to pull related information.<\/p>\n<p>The following Linux distributions support DBFS:<\/p>\n<ul>\n<li>Red Hat Enterprise Linux<\/li>\n<li>Ubuntu<\/li>\n<li>CentOS<\/li>\n<\/ul>\n<p>The installation and configuration of DBFS outlined in this article was tested on CentOS. Since RedHat Enterprise Linux (RHEL) is the upstream distro for CentOS, the instructions are also compatible with RHEL.<\/p>\n<h2>Installation and Configuration<\/h2>\n<p>The step by step instructions to install DBFS are illustrated below:<\/p>\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li>Connect to the server and enter the SU mode on the console (or explicitly call the commands using sudo)<\/li>\n<li>Download the DBFS (dbfs-0.2.5-0) package using the wget utility to carry out the installation and configuration process on CentOS, the community-based-and-supported Linux distribution that draws on RHEL.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>The wget utility is a non-interactive free tool to download files from the internet. By non-interactive, I mean that it can work in the background or it doesn\u2019t require much human interference.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">sudo wget https:\/\/github.com\/Microsoft\/dbfs\/releases\/download\/0.2.5\/dbfs-0.2.5-0.x86_64.rpm<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1021\" height=\"511\" class=\"wp-image-77648\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-61.png\" \/><\/p>\n<ol>\n<li style=\"list-style-type: none;\">\n<ol>\n<li>Install the <a href=\"https:\/\/fedoraproject.org\/wiki\/EPEL\">EPEL<\/a> repository with the following command<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p>Download the corresponding rpm file for the installation. The downloaded file depends on the base OS version, select the file accordingly. In my case it\u2019s CentOS 7. The process is same for RHEL 7 as well.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">sudo wget <a href=\"https:\/\/dl.fedoraproject.org\/pub\/epel\/epel-release-latest-7.noarch.rpm\">https:\/\/dl.fedoraproject.org\/pub\/epel\/epel-release-latest-7.noarch.rpm<\/a><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1004\" height=\"219\" class=\"wp-image-77649\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-62.png\" \/><\/p>\n<ol>\n<li>Enable and Configure the EPEL (<a href=\"https:\/\/fedoraproject.org\/wiki\/EPEL\">Extra Packages for Enterprise Linux<\/a>) using rpm (<a href=\"http:\/\/rpm.org\/\">RedHat Package Manager<\/a>)\n<p>The EPEL packages are managed by a special group called EPEL group within the Fedora Project. This group creates, maintains and manages a set of additional packages that are of a high quality. Software that are not part of the core repository, or updates that have not been included in the core repository are available in the EPEL repository.<\/p>\n<p>The following Linux distros support the EPEL repository:<\/p>\n<ul>\n<li>Red Hat Enterprise Linux (RHEL)<\/li>\n<li>CentOS<\/li>\n<li>Scientific Linux<\/li>\n<li>Oracle Linux<\/li>\n<\/ul>\n<p>Depending on the operating system you use, the steps to enable the EPEL repository may vary. Installation of packages from the EPEL repository is usually straightforward, with some of the distros easing the process further.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">sudo rpm -ivh epel-release-latest-7.noarch.rpm<\/pre>\n<p>OR<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">sudo yum install epel-release-latest-*.noarch.rpm<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"387\" class=\"wp-image-77650\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-63.png\" \/><\/p>\n<p>A wildcard (*) is used with the YUM command during installation, since this allows the command to work no matter which version you are installing.<\/p>\n<\/li>\n<li>Install the DBFS package using yum. YUM is a command line tool used to install software.\n<pre class=\"theme:vs2012 lang:sh decode:true\">sudo yum install dbfs-0.2.5-0.x86_64.rpm<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"978\" height=\"550\" class=\"wp-image-77651\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-64.png\" \/><\/p>\n<\/li>\n<\/ol>\n<p>Now that the packages are downloaded and installed, you can configure DBFS.<\/p>\n<ol>\n<li>Open a new <em>user<\/em> console or terminal for configuration<\/li>\n<li>Create the dbfs directory and sql subdirectory\n<pre class=\"theme:vs2012 lang:sh decode:true\">mkdir dbfs\r\nmkdir .\/dbfs\/sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"680\" height=\"139\" class=\"wp-image-77652\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-65.png\" \/><\/p>\n<\/li>\n<li>Configure DMVTool. Create a dmvtool.config file in the home directory\n<pre class=\"theme:vs2012 lang:sh decode:true\">pwd\r\n  vi dmvtool.config<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"403\" height=\"58\" class=\"wp-image-77653\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-66.png\" \/><\/p>\n<\/li>\n<li>Enter the content as per your configuration\n<pre class=\"theme:vs2012 lang:sh decode:true\">[server friendly name]\r\nhostname=[HOSTNAME]\r\nusername=[DATBASE_LOGIN]\r\npassword=[PASSWORD]\r\nversion=[VERSION]\r\ncustomQueriesPath=[PATH_TO_CUSTOM_QUERY_FOLDER]<\/pre>\n<p>In the following example, <strong>node2<\/strong> is the server\u2019s friendly name. When working with the sql files, a relative path to the SQL output file is used when defining the dump directory. The location of the dump directory will be <strong>&lt;MOUNT DIR&gt;\/&lt;ServerFriendly Name&gt;\/CustomQueries\/<\/strong>. Assume that the <strong>dmv1<\/strong> is the mount directory. The path to the dump directory would then be <strong>.\/dmv1\/node2\/CustomQueries\/<\/strong>.<\/p>\n<p><strong>localhost<\/strong> is the hostname of the SQL instance. The <strong>username<\/strong> and <strong>password<\/strong> are the access credentials for the SQL instance. Instead of using the SA account, you may want to define a user and grant them the <strong>VIEW SERVER STATE<\/strong> permissions to enable the user to query the DMVs. If the <strong>version<\/strong> is 16 or higher (SQL Server 2016 or later), the DMV data can be exported as JSON. Otherwise, the data would be exported as Tab-Separated Values (TSV). <strong>customQueriesPath<\/strong> is the location of the SQL file to which the custom queries will be written. Be sure to replace the home directory with your own.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">[node2]\r\nhostname=localhost\r\nusername=sa\r\npassword=thanVitha@2015\r\nversion=16\r\ncustomQueriesPath=\/home\/thanvitha\/dbfs\/sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"535\" height=\"209\" class=\"wp-image-77654\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-67.png\" \/><\/p>\n<\/li>\n<li>Create the mount directory and fuse the DMVs as flat files\n<pre class=\"theme:vs2012 lang:sh decode:true\">mkdir dmv1\r\ndbfs -c .\/dmvtool.config -m .\/dmv1<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"694\" height=\"145\" class=\"wp-image-77655\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-68.png\" \/><\/p>\n<table>\n<tbody>\n<tr>\n<td colspan=\"2\">\n<p>Required:<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-m\/&#8211;mount-path<\/p>\n<\/td>\n<td>\n<p>The mount directory for SQL server(s) DMV files<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-c\/&#8211;conf-file<\/p>\n<\/td>\n<td>\n<p>The location of the configuration file<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td colspan=\"2\">\n<p>Optional:<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-d\/&#8211;dump-path<\/p>\n<\/td>\n<td>\n<p>The dump directory. The Default path is &#8220;\/tmp\/sqlserver&#8221;<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-v\/&#8211;verbose : Start in verbose mode<\/p>\n<\/td>\n<td>\n<p>Enable the logging in the verbose mode<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-l\/&#8211;log-file<\/p>\n<\/td>\n<td>\n<p>Log file path (only be used with verbose mode)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-f<\/p>\n<\/td>\n<td>\n<p>Foreground DBFS run<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>-h<\/p>\n<\/td>\n<td>\n<p>Print<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<li>Browse to the server friendly name and use the ls command to list out the contents.\n<pre class=\"theme:vs2012 lang:sh decode:true\">cd .\/dmv1\/node2\r\nls<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"312\" height=\"243\" class=\"wp-image-77656\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-69.png\" \/><\/p>\n<\/li>\n<\/ol>\n<h2>Data Extraction<\/h2>\n<p>The data extraction from the DMV files is relatively simple. Linux contains a rich collection of tools or command line utilities to manipulate files and related contents using a pattern. The various tools are<\/p>\n<ul>\n<li>Awk : Aho, Weinberger and Kernighan, this is a text pattern scanning and processing language.<\/li>\n<li>Grep : global regular expression print, in simplest terms, it will search the input files for the search string and print the matching line<\/li>\n<li>Sed : Stream Editor, it performs the basic text transformation in an input file or from a pipeline<\/li>\n<li>Cut: The cut command is used to cut out the sections from each line of files and writing the result to standard output<\/li>\n<li>Cat : cat (as in concatenate) is one of the most frequently used commands on Unix-like operating systems. It has three related functions with regard to text files: displaying them, combining copies of them and creating new ones.<\/li>\n<li>Join : It joins the lines of two files on a common field<\/li>\n<li>And more\u2026<\/li>\n<\/ul>\n<p>The GitHub source has a list of examples to try and explore the possibilities of manipulating the DMV related file structures.<\/p>\n<p>Let me take you through a couple of examples to showcase the simple transformation of flat file concepts using very handy Linux\/Unix commands.<\/p>\n<p>To list the case-insensitive DMVs use <strong>ls<\/strong> and <strong>grep<\/strong><\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">ls | grep -I dm_os_sys_memory<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"519\" height=\"59\" class=\"wp-image-77657\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-70.png\" \/><\/p>\n<p>Or<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">ls -I \"*.JSON\" | grep dm_os<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"497\" height=\"346\" class=\"wp-image-77658\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-71.png\" \/><\/p>\n<p>To view the contents of the <strong>dm_os_sys_memory<\/strong>, use the <strong>CAT<\/strong> command. <strong>CAT<\/strong> by default, concatenates the contents in a file and displays it in the StandardOut (the monitor). Alternatively, based on what text editor you have on your distro, you could use any of vi, vim, or even nano commands to view the contents.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">cat dm_os_sys_memory<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1017\" height=\"250\" class=\"wp-image-77659\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-72.png\" \/><\/p>\n<p>To view columns 1,2,6,7 of the databases system table, use <strong>CUT<\/strong><\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">cut \u2013d$ '\\t' -f1,2,6,7 databases<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"588\" height=\"114\" class=\"wp-image-77660\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-73.png\" \/><\/p>\n<p>Or print the first column of OS information using <strong>AWK<\/strong><\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">cat dm_os_sys_info | awk '{print $1}'<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"565\" height=\"54\" class=\"wp-image-77661\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-74.png\" \/><\/p>\n<p>List columns of databases with tab separation<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">awk '{print $1,$2,$3,$5,$7}' databases | column -t<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"718\" height=\"133\" class=\"wp-image-77662\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-75.png\" \/><\/p>\n<p>List the count of the Simple and Full database recovery models of an instance<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">awk '\/SIMPLE\/{x++;}END {print x}' databases\r\nawk '\/FULL\/{x++;}END {print x}' databases<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"676\" height=\"80\" class=\"wp-image-77663\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-76.png\" \/><\/p>\n<p>Print the line number along with the pattern matching line in the database file<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">grep -n SIMPLE databases<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"467\" height=\"329\" class=\"wp-image-77664\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-77.png\" \/><\/p>\n<h2>How to Setup DBFS to run custom queries<\/h2>\n<p>This section describes the steps required to create custom queries:<\/p>\n<p>Every time you create a custom SQL query, the output is dumped as virtual files which are mounted to a virtual directory. There is no need to log into the SQL Server using a GUI or command line tool or run these SQL queries.<\/p>\n<ol>\n<li>Prepare the custom query\n<pre class=\"theme:vs2012 lang:sh decode:true\">select name from sys.databases <\/pre>\n<\/li>\n<li>Change the present working directory to ~ or user home\n<pre class=\"theme:vs2012 lang:sh decode:true\">cd ~<\/pre>\n<\/li>\n<li>Create the query file\u2014the *.SQL file\u2014at the custom query path defined in the config file.\n<pre class=\"theme:vs2012 lang:sh decode:true\">vi .\/dbfs\/sql\/db.sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"603\" height=\"180\" class=\"wp-image-77665\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-78.png\" \/><\/p>\n<\/li>\n<li>Browse to the DBFS customQueriespath mount point, correcting the path for your environment.\n<pre class=\"theme:vs2012 lang:sh decode:true\">cd .\/dmv1\/node2\/customQueries\/<\/pre>\n<\/li>\n<li>Display the contents using the cat command.\n<pre class=\"theme:vs2012 lang:sh decode:true\">cat db.sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"447\" height=\"110\" class=\"wp-image-77666\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-79.png\" \/><\/p>\n<\/li>\n<\/ol>\n<h2>How to edit the custom SQL query<\/h2>\n<p>Editing the custom query is simple. To edit the contents of the SQL file, browse to the SQL file and then modify the SQL with a multi-line SQL statement. Now, save the file. Next, browse the custom query path to view the results.<\/p>\n<p>Copy the following SQL into the db.sql file:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT @@SERVERNAME Servername,\r\nCONVERT(VARCHAR(25), DB.name) AS dbName,\r\nCONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],\r\n(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,\r\n(SELECT SUM((size*8)\/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],\r\n(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,\r\n(SELECT SUM((size*8)\/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],\r\n(SELECT SUM((size*8)\/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)\/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,\r\nconvert(sysname,DatabasePropertyEx(name,'Updateability'))  Updateability,\r\nconvert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,\r\nconvert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,\r\nconvert(sysname,DatabasePropertyEx(name,'Version')) Version \r\nFROM sysdatabases DB\r\nORDER BY dbName<\/pre>\n<p>Open the file using<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">vi .\/dbfs\/sql\/db.sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"701\" height=\"578\" class=\"wp-image-77667\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-80.png\" \/><\/p>\n<p>Now browse to the location of the custom query mounted directory, and using cat, display the metadata (the contents of the JSON) in a table-like format.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">cd .\/dmv1\/node2\/customQueries\/\r\ncat db.sql<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1007\" height=\"218\" class=\"wp-image-77668\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-81.png\" \/><\/p>\n<h2>Unmount DBFS file system<\/h2>\n<p>Since DBFS is a file system, the FUSE module can be manually unmounted using the fusermount command along with the -u option.<\/p>\n<p>It\u2019s a good practice to unmount the filesystem before shutting down the server.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">fusermount -u &lt;mount_directory&gt;<\/pre>\n<p>Let&#8217;s change the present working directory to home.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">cd ~<\/pre>\n<p>Issue the following command to unmount the dmv1 file. In this case dmv1 is the mount point.<\/p>\n<pre class=\"theme:vs2012 lang:sh decode:true\">fusermount -u .\/dmv1<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"488\" height=\"39\" class=\"wp-image-77669\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/03\/word-image-82.png\" \/><\/p>\n<h2>Wrapping Up<\/h2>\n<p>Microsoft\u2019s initiative gives us the ability to consume DMVs related data from the command line in a Linux world. I\u2019m looking forward to seeing more features in this tool!<\/p>\n<h3>Highlights<\/h3>\n<ul>\n<li>Support for Custom Query execution<\/li>\n<li>Data can be viewed as JSON<\/li>\n<li>Bash supports utilities such as cut, grep, sed, and awk, etc.<\/li>\n<li>Projection of the live\/real time DMV data<\/li>\n<li>Cross-platform tools which work on both SQL Server on Windows as well as SQL Server on Linux<\/li>\n<li>DBFS is available for Ubuntu, RedHat Enterprise Linux, and CentOS as of writing this article.<\/li>\n<\/ul>\n<h2>References<\/h2>\n<ul>\n<li><a href=\"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2017\/05\/17\/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views\/\">https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2017\/05\/17\/try-new-sql-server-command-line-tools-to-generate-t-sql-scripts-and-monitor-dynamic-management-views\/<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/Microsoft\/dbfs\">https:\/\/github.com\/Microsoft\/dbfs<\/a><\/li>\n<li><a href=\"http:\/\/rpm.org\/\">http:\/\/rpm.org\/<\/a><\/li>\n<li><a href=\"https:\/\/fedoraproject.org\/wiki\/EPEL\">https:\/\/fedoraproject.org\/wiki\/EPEL<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>With SQL Server now supported on Linux, traditional SQL Server DBAs will need to learn how to work with Linux, and Linux administrators will need to learn how to work with SQL Server. Fortunately, there are several tools available to assist. In this article Prashanth Jayaram describes how to work with the DMV tool which treats queries as files. &hellip;<\/p>\n","protected":false},"author":318750,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,143532],"tags":[95506],"coauthors":[55785],"class_list":["post-77647","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-tools-sql-server","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77647","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/318750"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77647"}],"version-history":[{"count":10,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77647\/revisions"}],"predecessor-version":[{"id":77677,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77647\/revisions\/77677"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77647"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77647"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77647"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77647"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}