{"id":68653,"date":"2016-10-06T15:17:01","date_gmt":"2016-10-06T15:17:01","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68653"},"modified":"2021-08-24T13:39:28","modified_gmt":"2021-08-24T13:39:28","slug":"representing-hierarchical-data-for-mere-mortals","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/representing-hierarchical-data-for-mere-mortals\/","title":{"rendered":"Exporting SQL Server Query Results and Configuration as YAML with PowerShell and SMO"},"content":{"rendered":"<p><b>This article shows how to export SQL Server query results and configuration data as YAML documents using PowerShell and SQL Server Management Objects (SMO). YAML is well-suited to hierarchical document content &#8211; human-readable, diff-friendly, and deeply nestable &#8211; which makes it practical for configuration snapshots, query-result capture with embedded sub-structures (XML columns, spatial types), and source-control-friendly object inventories. The article walks through: (1) the PSYaml PowerShell module installation and setup; (2) exporting simple query result sets with column metadata preserved; (3) handling XML column types (spatial types, structured XML) that .NET splits into component parts; (4) using SMO to capture endpoint configuration settings as YAML; (5) comparing configuration YAML files between instances to detect drift; (6) generating per-database object-count snapshots. Each example is a short PowerShell function producing a YAML file. Note: this article focuses on representing hierarchical document data (SQL results and configuration) as YAML, not on modelling hierarchical relationships in SQL (adjacency lists, HierarchyId, nested sets) &#8211; if you need the modelling topic, see the cross-links at the end.<\/b><\/p>\n<ul style=\"list-style: none;\">\n<li><a href=\"#post-68653-_Toc463524005\">Following Along<\/a><\/li>\n<li><a href=\"#post-68653-_Toc463524006\">Getting Query Results from a Server in a Hierarchical Way<\/a>\n<ul style=\"list-style: none;\">\n<li><a href=\"#post-68653-_Toc463524007\">Simple queries with metadata<\/a><\/li>\n<li><a href=\"#post-68653-_Toc463524008\">Queries that return XML columns<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#post-68653-_Toc463524009\">Using SMO<\/a>\n<ul style=\"list-style: none;\">\n<li><a href=\"#post-68653-_Toc463524010\">Saving Endpoint Configuration Settings<\/a><\/li>\n<li><a href=\"#post-68653-_Toc463524011\">Saving and Comparing Configuration Settings<\/a><\/li>\n<li><a href=\"#post-68653-_Toc463524012\">Getting the Quantities of each Object in Each Database.<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#post-68653-_Toc463524013\">Conclusion<\/a><\/li>\n<\/ul>\n<p>DBAs are often faced with having to create, maintain or inspect documents that contain hierarchical data and information. Sometimes, hierarchical information has even been shoehorned into a table or two, but at other times it happens for no better reason than the culture behind SMO and SSMS. SMO is resolute in recording SQL Server\u2019s information an object-oriented hierarchical way, even though it is, within any relational database, entirely based on relational tabular data and Metadata. Codd insists on it.<\/p>\n<p>The grey-muzzle DBA will, of course, have secure tricks for saving the more familiar tabular information, all the way from CSV to SQLite. Most of us, however, scratch our heads when faced with XML or JSON. The former is mark-up, tricky to inspect and over-ambitious in scope; the second is a subset of JavaScript, and inadequate for specifying data types in sufficient detail. My well-thumbed copy of \u2018XML in a Nutshell\u2019 runs to 680 pages. YAML is more intuitive to use, though: Although it is a superset of JSON, you can jettison those brackets and use an intuitive style that has been used for representing hierarchical information since before computers. A good YAML Deserializer will deal with JSON as well as YAML and output JSON dialect if you choose.<\/p>\n<p>Yes, YAML is equally readable and updateable by either machines or humans. YAML has been slow to gain traction because it has never had a human-oriented textbook or adequate training materials. The extremely clever people who devised it weren\u2019t natural communicators.<\/p>\n<p>In celebration of the introduction of <strong>PSYaml<\/strong>, I want to demonstrate how you\u2019d use YAML with PowerShell for recording server information or recording results that are easy to read into applications. (.NET developers will find it even easier because they can use \u00a0Antoine Aubry\u2019s\u00a0<a href=\"http:\/\/aaubry.net\/pages\/yamldotnet.html\">excellent YamlDotNet library<\/a> directly). We\u2019ll start simply.<\/p>\n<h1 id=\"post-68653-_Toc463524005\">Following Along<\/h1>\n<p>To follow along, you\u2019ll need my <strong>PSYaml<\/strong> module. I\u2019ve tried to make it self-installing and <a href=\"https:\/\/www.simple-talk.com\/blogs\/psyaml-powershell-yaml\/\">you\u2019ll see it described here<\/a>.<\/p>\n<h1 id=\"post-68653-_Toc463524006\">Getting Query Results from a Server in a Hierarchical Way<\/h1>\n<h2 id=\"post-68653-_Toc463524007\">Simple queries with metadata<\/h2>\n<p>Let\u2019s start with a simple PowerShell function that executes a query that returns a result. It not only records the data in a way that makes it easy to inspect, but also records when and where you executed the query and lists the table columns you\u2019d need to store the data.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">ConvertTo-YAML (\r\n    Get-QueryResult 'Select top 2 * from person.address' `\r\n                    'YourInstance' `\r\n                    'Adventureworks2014')<\/pre>\n<pre class=\"theme:powershell-output lang:ps decode:true\">---\r\n\r\n  Query: 'Select top 2 * from person.address' \r\n  Database: 'Adventureworks2014' \r\n  Instance: \u2018MyBigFatServer\u2019 \r\n  Schema: \r\n  - 'AddressID int  IDENTITY' \r\n  - 'AddressLine1 nvarchar (60) ' \r\n  - 'AddressLine2 nvarchar (60) NULL' \r\n  - 'City nvarchar (30) ' \r\n  - 'StateProvinceID int  ' \r\n  - 'PostalCode nvarchar (15) ' \r\n  - 'SpatialLocation AdventureWorks2014.sys.geography  NULL' \r\n  - 'rowguid uniqueidentifier  ' \r\n  - 'ModifiedDate datetime  ' \r\n  Date: 2016-09-30T11:59:58 \r\n  Data: \r\n  - \r\n    - 1 \r\n    - '1970 Napa Ct.' \r\n    - null \r\n    - 'Bothell' \r\n    - 79 \r\n    - '98011' \r\n    - \r\n       IsNull:   'False' \r\n       Lat:   '47.7869921906598' \r\n       Long:   '-122.164644615406' \r\n       M:   'Null' \r\n       STSrid:   '4326' \r\n       Z:   'Null' \r\n    - '9aadcb0d-36cf-483f-84d8-585c2d4ec6e9' \r\n    - 2007-12-04T00:00:00 \r\n  - \r\n    - 2 \r\n    - '9833 Mt. Dias Blv.' \r\n    - null \r\n    - 'Bothell' \r\n    - 79 \r\n    - '98011' \r\n    - \r\n       IsNull:   'False' \r\n       Lat:   '47.6867097047995' \r\n       Long:   '-122.250185528911' \r\n       M:   'Null' \r\n       STSrid:   '4326' \r\n       Z:   'Null' \r\n    - '32a54b9e-e034-4bfb-b573-a71cde60d8c0' \r\n    - 2008-11-30T00:00:00\r\n \r\n<\/pre>\n<p>The indentation represents the nesting level. The number of spaces used at any level isn\u2019t significant as long as the left margin lines up. The \u2018-\u2018 sign within the left margin means that your data is a list item. Most collections of data are list items or key:value pairs. The latter are represented by the key being followed by a colon\u2019:\u2019 followed by a value.<\/p>\n<p>You\u2019ll notice that we are mixing up lists and ordered hash tables. In YAML, you can represent collections of data as:<\/p>\n<ul>\n<li><strong>map<\/strong>s, which are unordered set of key: value pairs without duplicates.<\/li>\n<li><strong>omap<\/strong>s (ordered maps) that are ordered sequences of key: value pairs without duplicates.<\/li>\n<li><strong>pair<\/strong>s: which are ordered sequence of key: value pairs allowing duplicates.<\/li>\n<li><strong>set<\/strong>s which are unordered set of non-equal values.<\/li>\n<li><strong>seq<\/strong>s (Sequences) consisting of a sequence of arbitrary values.<\/li>\n<\/ul>\n<p>Generally, our data consists of structures of <strong>omap<\/strong>s and <strong>seq<\/strong>s. JSON has unordered sets of name\/value pairs without duplicates (maps) and arrays, which are ordered collections of values, equivalent to a seq. The YAML documentation and <a href=\"http:\/\/yaml.org\/refcard.html\">reference card<\/a> will show how to represent the other types.<\/p>\n<p>We\u2019re giving you the SQL column names at the top of the list and then providing each row as an ordered list. This is a compact way of storing longer results.<\/p>\n<p>One problem with this rendering is that it is done in a style that is economical on space. Because I know the column headings, I\u2019m doing each row as a simple list, as if it were CSV. You might something that is easier to read.<\/p>\n<p>If I do this<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true  \">ConvertTo-YAML (\r\n   Get-QueryResult 'Select top 2 * from person.address' `\r\n                   'MyBigFatServer' `\r\n                   'Adventureworks2014' doAsList $false ) \r\n\r\n<\/pre>\n<p>Then I get each row looking like this:<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true\">- \r\n      AddressID: 2 \r\n      AddressLine1: '9833 Mt. Dias Blv.' \r\n      AddressLine2: null \r\n      City: 'Bothell' \r\n      StateProvinceID: 79 \r\n      PostalCode: '98011' \r\n      SpatialLocation: \r\n       IsNull:   'False' \r\n       Lat:   '47.6867097047995' \r\n       Long:   '-122.250185528911' \r\n       M:   'Null' \r\n       STSrid:   '4326' \r\n       Z:   'Null' \r\n      rowguid: '32a54b9e-e034-4bfb-b573-a71cde60d8c0' \r\n      ModifiedDate: 2008-11-30T00:00:00 \r\n\r\n<\/pre>\n<p>Whichever way you do it, you\u2019ll notice in the full listing that the columns in the \u2018schema\u2019 section at the top get a datatype as well as the column name. Here is the extract.<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true \">---\r\n\r\n  Query: 'Select top 2 * from person.address' \r\n  Database: 'Adventureworks2014' \r\n  Instance: 'MyBigFatServer' \r\n  Schema: \r\n  - 'AddressID int  IDENTITY' \r\n  - 'AddressLine1 nvarchar (60) ' \r\n  - 'AddressLine2 nvarchar (60) NULL' \r\n  - 'City nvarchar (30) ' \r\n  - 'StateProvinceID int  ' \r\n  - 'PostalCode nvarchar (15) ' \r\n  - 'SpatialLocation AdventureWorks2014.sys.geography  NULL' \r\n  - 'rowguid uniqueidentifier  ' \r\n  - 'ModifiedDate datetime  '  \r\n<\/pre>\n<p>That suits me fine. With any structured document you can add all sorts of detail that are impossible with a tabular representation such as CSV. We tend to get around this with comments or descriptive filenames when there is no other way, but that boxes you in when you are doing automation work, so it is definitely a bonus to have this feature.<\/p>\n<p>YAML isn\u2019t alone in giving you this luxury. You can, of course use JSON or XML, Python or PowerShell. Here we\u2019ll show JSON. Here is just one record.<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true\">{\r\n    \"Query\":  \"Select top 2 * from person.address\",\r\n    \"Database\":  \"Adventureworks2014\",\r\n    \"Instance\":  \u2018MyBigFatServer\u2019,\r\n    \"Schema\":  [\r\n                   \"AddressID int  IDENTITY\",\r\n                   \"AddressLine1 nvarchar (60) \",\r\n                   \"AddressLine2 nvarchar (60) NULL\",\r\n                   \"City nvarchar (30) \",\r\n                   \"StateProvinceID int  \",\r\n                   \"PostalCode nvarchar (15) \",\r\n                   \"SpatialLocation AdventureWorks2014.sys.geography  NULL\",\r\n                   \"rowguid uniqueidentifier  \",\r\n                   \"ModifiedDate datetime  \"\r\n               ],\r\n    \"Date\":  \"2016-09-30T11:59:58\",\r\n    \"Data\":  [\r\n                 1,\r\n                 \"1970 Napa Ct.\",\r\n                 \"\",\r\n                 \"Bothell\",\r\n                 79,\r\n                 98011,\r\n                 {\r\n                     \"IsNull\":  false,\r\n                     \"Lat\":  47.7869921906598,\r\n                     \"Long\":  -122.164644615406,\r\n                     \"M\":  \"Null\",\r\n                     \"STSrid\":  4326,\r\n                     \"Z\":  \"Null\"\r\n                 },\r\n                 \"9aadcb0d-36cf-483f-84d8-585c2d4ec6e9\",\r\n                 \"2007-12-04T00:00:00\"\r\n             ]\r\n} \r\n<\/pre>\n<p>This can, incidentally, be deserialized successfully with <strong>ConvertFrom-YAML<\/strong>, so you get the choice of either style.<\/p>\n<h2 id=\"post-68653-_Toc463524008\">Queries that return XML columns<\/h2>\n<p>You\u2019ll notice that .NET\u2019s provider split your <strong>SpatialLocation<\/strong> up into its components. With <strong>ConvertTo-YAML<\/strong>, we can do the same for XML columns too if you want.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">ConvertTo-YAML (\r\n   Get-QueryResult 'Select top 1 * from [HumanResources].[JobCandidate] ' `\r\n                   'YourInstance' `\r\n                   'Adventureworks2014' ) \r\n<\/pre>\n<p>Yes, you don\u2019t see the XML, but a proper hierarchical rendering of the data. This is because the YAML converter is set to unwrap <strong>XMLDocuments<\/strong> as if they were objects. You can change that behaviour, of course if you wish to preserve the XML document. (I only show a bit of this YAML document)<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-2.png\" \/><\/p>\n<p>If you are still wondering why anyone would find this useful, try running the same queries in SSMS just to see how informative the result pane is in comparison.<\/p>\n<p>So, what does the query plan look like in YAML? Well, it isn\u2019t exactly pretty, but it is a lot easier to scan by eye.<\/p>\n<p>Just a sample here, of course. You\u2019ll have to try it out to see more.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-3.png\" \/><\/p>\n<p>Here is the PowerShell code to get the top five slow-running queries from which I took that excerpt, and produce a YAML document from them, including the SQL and the execution plan.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">ConvertTo-YAML (\r\nGet-QueryResult @\"\r\nSELECT st.text, qp.query_plan\r\n  FROM\r\n  (\r\n  SELECT TOP 3 dm_exec_query_stats.sql_handle,\r\n    dm_exec_query_stats.plan_handle\r\n    FROM sys.dm_exec_query_stats\r\n    WHERE\r\n    (\r\n    dm_exec_query_stats.max_worker_time &gt; 300\r\n OR dm_exec_query_stats.max_elapsed_time &gt; 300\r\n    )\r\n    ORDER BY dm_exec_query_stats.total_worker_time DESC\r\n  ) AS qs\r\n    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st\r\n    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp;\r\n\"@   'YourInstance' `\r\n     'Adventureworks2014' ) \r\n<\/pre>\n<p>Normally, you\u2019d want to do a xpath query to just get the nodes you really want because there is a lot of clutter in an XML query plan.<\/p>\n<p>You can, of course, easily create an XML hierarchical result that was created with the <strong>FOR XML PATH<\/strong> syntax. The only stricture is that you must supply a <strong>ROOT<\/strong>. You must also coerce it into an XML datatype as it is otherwise sent as <strong>ntext<\/strong> and can\u2019t easily be recognised as XML.<\/p>\n<pre class=\"theme:powershell-ise font:consolas lang:ps decode:true \">ConvertTo-YAML -verbose (\r\nGet-QueryResult @\"\r\nSELECT CONVERT( XML,(SELECT emp.NationalIDNumber AS [Employee\/@ID],\r\nper.FirstName AS [Employee\/Name\/First],\r\nper.MiddleName AS [Employee\/Name\/Middle],\r\nper.LastName AS [Employee\/Name\/Surname],\r\nCOALESCE(per.Title+ ' ','')+per.FirstName+' '+COALESCE(per.MiddleName+ ' ','')\r\n  + per.LastName+ COALESCE(' '+per.Suffix,'') AS [Employee\/Name\/Full],\r\nemp.BirthDate AS [Employee\/Personal\/DOB],\r\nemp.Gender AS [Employee\/Personal\/Gender],\r\nemp.MaritalStatus AS [Employee\/Personal\/Married],\r\nemp.JobTitle AS [Employee\/CurrentYear\/Job_Title],\r\nemp.VacationHours AS [Employee\/CurrentYear\/Vacation_hours],\r\nemp.SickLeaveHours AS [Employee\/CurrentYear\/SickLeave_Hours],\r\nemp.CurrentFlag AS [Employee\/CurrentYear\/Current]\r\nFROM HumanResources.Employee emp\r\nINNER JOIN Person.Person per\r\nON emp.BusinessEntityID = per.BusinessEntityID\r\nWHERE emp.BusinessEntityID IN ( 4,18,115)\r\nFOR XML PATH,ROOT('EmployeeList'),\r\nELEMENTS XSINIL)) AS Top_Employees;\r\n \"@   'MyBigFatInstance' `\r\n     'Adventureworks2014' )  \r\n<\/pre>\n<p>Which gives.. (just an extract again )<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2016\/10\/word-image-4.png\" \/><\/p>\n<p>The function that gave these results, Get-QueryResults, is this<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">function Get-QueryResult\r\n{&lt;#\r\n .SYNOPSIS\r\n   Returns a powershell object that records the date it was executed, on what instance and \r\n   database. It also records the nature of each column returned in SQL\r\n .DESCRIPTION\r\n   This is designed to be used in conjunction with ConvertTo-YAML or ConvertTo-XML \r\n    to save a query in such a way that the rows can be read and there is a full record of where and when\r\n    the expression was executed.\r\n    Don't try to use this for large queries where you should be using BCP. This is intended for \r\n    reports and diagnostic queries that need to be saved to file or inspected.\r\n .EXAMPLE\r\n   ConvertTo-YAML (\r\n     Get-QueryResult 'Select top 5 * from person.address' `\r\n                   'YourInstance' `\r\n                   'Adventureworks')\r\n   ConvertTo-YAML (\r\n     Get-QueryResult 'Select top 2 * from person.address' `\r\n                   'YourInstance' `\r\n                   'Adventureworks2014' doAsList $false )\r\n\r\n .PARAMETER SQL\r\n   The SQL Expression that you want scripted out\r\n .PARAMETER Sourceinstance\r\n   The SQL Server instance you want to use\r\n .PARAMETER Sourcedatabase\r\n   The source database (master if you don't care)\r\n .PARAMETER Credentials\r\n   internal use only. required for formatting\r\n#&gt;\r\n    \r\n    [CmdletBinding()]\r\n    param (\r\n        [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $true)]\r\n        [string]$sql,\r\n        #the valid SQL Expression that you want to execute\r\n\r\n        [parameter(Position = 1, Mandatory = $true, ValueFromPipeline = $false)]\r\n        [string]$Sourceinstance,\r\n        #the full name of the instance\r\n\r\n        [parameter(Position = 2, Mandatory = $false, ValueFromPipeline = $false)]\r\n        [string]$Sourcedatabase = 'master',\r\n        # e.g. Adventureworks\r\n\r\n        [parameter(Position = 3, Mandatory = $false, ValueFromPipeline = $false)]\r\n        [string]$Credentials = 'integrated security=True',\r\n \r\n        [parameter(Position = 4, Mandatory = $false, ValueFromPipeline = $false)]\r\n        [int]$doAsList = $true\r\n    )\r\n    \r\n    $Credentials = 'integrated security=True' #uid and password if SQL Server \r\n    $SourceConnectionString = \"Data Source=$Sourceinstance;Initial Catalog=$Sourcedatabase;$credentials\"\r\n    $sourceConnection = New-Object System.Data.SqlClient.SQLConnection($SourceConnectionString)\r\n    $sourceConnection.open()\r\n    $commandSourceData = New-Object system.Data.SqlClient.SqlCommand($sql, $sourceConnection)\r\n    $reader = $commandSourceData.ExecuteReader()\r\n    $Counter = $Reader.FieldCount\r\n    $schemaTable = $reader.GetSchemaTable();\r\n    $ResultSchema = $schemaTable.Rows | foreach-object {\r\n        \"$($_.ColumnName) $($_.DataTypeName) $(\r\n            if ($_.DataTypeName -in ('char', 'varchar', 'nchar', 'nvarchar'))\r\n            {\r\n                if ($_.IsLong -eq $true) { '(MAX)' }\r\n                else { '(' + $_.ColumnSize + ')' }\r\n            }\r\n            elseif ($_.DataTypeName -in ('decimal', 'numeric'))\r\n            { '(' + $_.NumericPrecision + ', ' + $_.NumericScale + ')' }\r\n        ) $(\r\n            if ($_.IsIdentity -eq $true) { 'IDENTITY' }\r\n            else { '' }\r\n        )$(\r\n            if ($_.AllowDBNull -eq $true) { 'NULL' })\"\r\n    }\r\n    \r\n    $Rows = @() #the array of arrays that will store our result\r\n    while ($Reader.Read()) # read the next row until done\r\n    {\r\n        if ($doAsList -eq $true)\r\n        {\r\n            $tuple = @() #do this row-by-row\r\n            for ($i = 0; $i -lt $Counter; $i++) #for each column\r\n            {\r\n                write-verbose $schemaTable.Rows[$i].DataTypeName\r\n                if ($Reader.IsDBNull($i) -eq $true) { $tuple += $null }\r\n                elseif ($Reader.GetFieldType($i).Name -eq 'DateTime')\r\n                { $tuple += $Reader.GetDateTime($i) }\r\n                elseif ($schemaTable.Rows[$i].DataTypeName -eq 'xml')\r\n                { $tuple += [xml]$Reader.GetValue($i) }\r\n                elseif ($Reader.GetFieldType($i).Name -eq 'Byte[]')\r\n                { $tuple += ,$Reader.GetValue($i) }\r\n                else { $tuple += $Reader.GetValue($i) };\r\n            }\r\n            $rows += ,$tuple\r\n            \r\n        }\r\n        else\r\n        {\r\n            $tuple = [ordered]@{ } #do this row-by-row\r\n            for ($i = 0; $i -lt $Counter; $i++) #for each column\r\n            {\r\n                $tuple.\"$($Reader.GetName($i))\" = &amp;{\r\n                 if ($Reader.GetFieldType($i).Name -eq 'DateTime')\r\n                    {$Reader.GetDateTime($i)}\r\n                 elseif ($schemaTable.Rows[$i].DataTypeName -eq 'xml')\r\n                    {[xml]$Reader.GetValue($i)}\r\n                 elseif ($Reader.IsDBNull($i) -eq $true)\r\n                    {$null}\r\n                elseif ($Reader.GetFieldType($i).Name -eq 'Byte[]')\r\n                    {$Reader.GetValue($i) }\r\n                else { $Reader.GetValue($i) }\r\n                };\r\n            }\r\n            $rows += $tuple\r\n        }\r\n        \r\n    }\r\n    \r\n    [ordered]@{\r\n        Query = $sql;\r\n        Database = $Sourcedatabase;\r\n        Instance = $SourceInstance;\r\n        Schema = $ResultSchema;\r\n        Date = Get-date;\r\n        Data = $rows;\r\n    }\r\n}\r\n<\/pre>\n<h1 id=\"post-68653-_Toc463524009\">Using SMO<\/h1>\n<h2 id=\"post-68653-_Toc463524010\">Saving Endpoint Configuration Settings<\/h2>\n<p>Let\u2019s now go to a slightly different task, recording configuration data. Endpoints, for example, can cause a problem, especially if you have a fairly subtle setting to outwit intruders. You need to record your settings in your CMS so you can reconstitute them on another server. We\u2019ll start by getting hold of SMOs Server object, also known as <br \/>\n <strong>Microsoft.SqlServer.Management.SMO.server<\/strong><\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers\r\nimport-module psyaml \r\n$ServerName = 'andrewctest.testnet.red-gate.com\\sql2008'\r\n$Credentials = 'integrated security=True'\r\n$SMO = 'Microsoft.SqlServer.Management.SMO' # to shorten some of the devotions to libraries\r\n$Conn = 'Microsoft.SqlServer.Management.Common.ServerConnection'\r\n$SQLConn = 'System.Data.SqlClient.SqlConnection'\r\n$connectionString = \"Data Source=$serverName;$credentials;pooling=False;multipleactiveresultsets=False;packet size=4096\"\r\n#this might need to be changed under some circumstances \r\ntry #making the connection\r\n{\r\n    $srv = new-object \"$Smo.Server\"(new-object $Conn(new-object $sqlConn($connectionString)))\r\n}\r\ncatch #if it isn't going to happen\r\n{\r\n    \"Could not connect to SQL Server instance '$servername': $($error[0].ToString() + $error[0].InvocationInfo.PositionMessage). The script is aborted\"\r\n    exit - 1 # return -1 if there is an error otherwise 0\r\n} # \r\n\r\n<\/pre>\n<p>Then we\u2019ll get the endpoint information, but also use the opportunity to add the essential details about the server and the date the record was made. No need to worry about what extra information you record in the YAML document. If it is useful, add it because it doesn\u2019t affect the ease with which we can import the settings as an object if we wish to transfer these settings to a different server.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">$EndPointFacts = @{\r\n    #create the hash table that will contain the settings\r\n    Server = $srv.Name;\r\n    Date = Get-date;\r\n    What = 'List of configuration settings';\r\n}\r\n# and now we simply walk through the endpoints, adding the permissions as a hash table\r\n# and inserting the whole lot into our holding hash table\r\n$EndPointFacts.settings = $srv.Endpoints |\r\nSelect Name, Protocol.Http.SslPort, Owner, EndpointState, EndpointType, Protocol,\r\nProtocolType, Payload, @{Name = \"Permissions\"; Expression = {$_.EnumObjectPermissions() }}\r\nConvertTo-YAML $EndPointFacts \r\n<\/pre>\n<p>This will give you something like this (I\u2019ve only included only a couple of endpoints)<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true\">---\r\n\r\n  Date: 2016-09-30T14:58:23 \r\n  Server: WhateverYourServerIs' \r\n  What: 'List of configuration settings' \r\n  settings: \r\n  - \r\n     Name: 'Dedicated Admin Connection' \r\n     Protocol.Http.SslPort: null \r\n     Owner: 'sa' \r\n     EndpointState: 'Started' \r\n     EndpointType: 'TSql' \r\n     Protocol: \r\n         Http:   '[Dedicated Admin Connection]' \r\n         Tcp:   '[Dedicated Admin Connection]' \r\n     ProtocolType: 'Tcp' \r\n     Payload: \r\n         DatabaseMirroring:   '[Dedicated Admin Connection]' \r\n         ServiceBroker:   '[Dedicated Admin Connection]' \r\n         Soap:   '[Dedicated Admin Connection]' \r\n     Permissions: null \r\n  - \r\n     Name: 'TSQL Default TCP' \r\n     Protocol.Http.SslPort: null \r\n     Owner: 'sa' \r\n     EndpointState: 'Started' \r\n     EndpointType: 'TSql' \r\n     Protocol: \r\n         Http:   '[TSQL Default TCP]' \r\n         Tcp:   '[TSQL Default TCP]' \r\n     ProtocolType: 'Tcp' \r\n     Payload: \r\n         DatabaseMirroring:   '[TSQL Default TCP]' \r\n         ServiceBroker:   '[TSQL Default TCP]' \r\n         Soap:   '[TSQL Default TCP]' \r\n     Permissions: \r\n         ColumnName:   null \r\n         Grantee:   'public' \r\n         GranteeType:   'ServerRole' \r\n         Grantor:   'sa' \r\n         GrantorType:   'Login' \r\n         ObjectClass:   'Endpoint' \r\n         ObjectID:   '4' \r\n         ObjectName:   'TSQL Default TCP' \r\n         ObjectSchema:   null \r\n         PermissionState:   'Grant' \r\n         PermissionType:   'CONNECT' \r\n<\/pre>\n<p>I won\u2019t go into the nuts and bolts of how you save this to a file. If you\u2019re unsure, I\u2019ll show how in the next illustration.<\/p>\n<p>Without sliding too far off-topic, you can store this endpoint configuration in source control and use it in a PowerShell script for building your server to host a database application. After all, endpoint settings are as much part of a database as any other aspect of access control.<\/p>\n<h2 id=\"post-68653-_Toc463524011\">Saving and Comparing Configuration Settings<\/h2>\n<p>Perhaps you just want to have your basic configuration settings written out in your CMS. (we will be using the same server object <strong>$srv<\/strong> in this code)<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">$settings=@{}\r\n$srv.Configuration.properties|foreach{ $settings.Add(($_.displayName), ($_.Runvalue))}\r\n$EnPointFacts = @{\r\n    Server = $srv.Name;\r\n    Date = Get-date;\r\n    What = 'List of configuration settings';\r\n    settings=$settings\r\n}\r\nConvertTo-YAML $EnPointFacts &gt;\"$env:TEMP\\ConfigSettings_$($srv.InstanceName)_$(Get-Date -format ddMMMyy).YAML\" \r\n<\/pre>\n<p>This will give you a simple list of your configuration data that can subsequently be saved or read from disk into a PowerShell object for analysis.<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true\">---\r\n\r\n  Date: 2016-09-30T14:43:59 \r\n  Server: 'andrewctest.testnet.red-gate.com\\sql2008' \r\n  What: 'List of configuration settings' \r\n  settings: \r\n    Ad Hoc Distributed Queries: 1 \r\n    server trigger recursion: 1 \r\n    set working set size: 0 \r\n    query governor cost limit: 0 \r\n    access check cache quota: 0 \r\n    PH timeout (s): 60 \r\n    Agent XPs: 1 \r\n    lightweight pooling: 0 \r\n    locks: 0 \r\n    nested triggers: 1 \r\n    ft crawl bandwidth (max): 100 \r\n    access check cache bucket count: 0 \r\n    xp_cmdshell: 1 \r\n    ft notify bandwidth (min): 0 \r\n    Database Mail XPs: 0 \r\n    awe enabled: 0 \r\n    affinity I\/O mask: 0 \r\n    recovery interval (min): 0 \r\n    show advanced options: 1 \r\n    fill factor (%): 0 \r\n    media retention: 0 \r\n    min memory per query (KB): 1024 \r\n    common criteria compliance enabled: 0 \r\n    clr enabled: 1 \r\n    SMO and DMO XPs: 1 \r\n    remote login timeout (s): 20 \r\n    cross db ownership chaining: 0 \r\n    ft crawl bandwidth (min): 0 \r\n    in-doubt xact resolution: 0 \r\n    scan for startup procs: 0 \r\n    user options: 0 \r\n    max worker threads: 0 \r\n    precompute rank: 0 \r\n    open objects: 0 \r\n    SQL Mail XPs: 0 \r\n    cursor threshold: -1 \r\n    remote admin connections: 0 \r\n    two digit year cutoff: 2049 \r\n    backup compression default: 0 \r\n    c2 audit mode: 0 \r\n    blocked process threshold (s): 0 \r\n    optimize for ad hoc workloads: 0 \r\n    user connections: 0 \r\n    transform noise words: 0 \r\n    priority boost: 0 \r\n    remote query timeout (s): 600 \r\n    filestream access level: 0 \r\n    default trace enabled: 1 \r\n    max degree of parallelism: 0 \r\n    ft notify bandwidth (max): 100 \r\n    disallow results from triggers: 0 \r\n    index create memory (KB): 0 \r\n    affinity mask: 0 \r\n    remote access: 1 \r\n    default full-text language: 1033 \r\n    query wait (s): -1 \r\n    max full-text crawl range: 4 \r\n    network packet size (B): 4096 \r\n    EKM provider enabled: 0 \r\n    allow updates: 0 \r\n    min server memory (MB): 8 \r\n    max text repl size (B): 65536 \r\n    Ole Automation Procedures: 1 \r\n    cost threshold for parallelism: 5 \r\n    Replication XPs: 0 \r\n    default language: 0 \r\n    remote proc trans: 0 \r\n    max server memory (MB): 2147483647 \r\n<\/pre>\n<p>What use is this? Well, in this case, I\u2019ve saved the information in a temporary location and I\u2019ve saved it for a few days. I\u2019ve then changed a setting. Can I find when I changed it? Well, yes.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">Compare-Object $(Get-Content \"$env:TEMP\\ConfigSettings_SQL2008_27Sep16.YAML\") `\r\n               $(Get-Content \"$env:TEMP\\ConfigSettings_SQL2008_29Sep16.YAML\") \r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps decode:true \">InputObject                                                   SideIndicator                                                                                               \r\n-----------                                                   -------------                                                                                               \r\n  Date: 2016-09-29T16:01:25                                   =&gt;                                                                                                          \r\n    Ole Automation Procedures: 0                              =&gt;                                                                                                          \r\n  Date: 2016-09-27T16:09:58                                   &lt;=                                                                                                          \r\n    Ole Automation Procedures: 1                              \r\n<\/pre>\n<p>Or you can let your source control system do the work for you, in which you\u2019d definitely want to remove the date!<\/p>\n<p>Now you have these on file you can access single settings from PowerShell. These get the setting for the cost threshold for parallelism for 27<sup>th<\/sup> September and checks the date on the file!<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true  \">$content = ConvertFrom-YAML  ([IO.File]::ReadAllText(\"$env:TEMP\\ConfigSettings_SQL2008_27Sep16.YAML\"))\r\n$content.settings.'cost threshold for parallelism'\r\n$content.Date \r\n<\/pre>\n<pre class=\"theme:powershell-output lang:ps decode:true \">5\r\n\r\n27 September 2016 16:09:58<\/pre>\n<h2 id=\"post-68653-_Toc463524012\">Getting the Quantities of each Object in Each Database.<\/h2>\n<p>So what about something a bit more spectacular? A stock-take of your database objects? Here is some simple code for getting the number of each type of database object from all the databases on a server. Once again, we\u2019ll leave out the obvious details of creating and connecting the SMO server object and cut to the essential details.<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true\">#firstly we find out the number of system views, tables and functions there are\r\n$SystemViews = $srv.databases['model'].Views.Count\r\n$SystemSchemas = $srv.databases['model'].Schemas.Count\r\n$SystemStoredProcedures = $srv.databases['model'].StoredProcedures.Count\r\n$SystemFunctions = $srv.databases['model'].UserDefinedFunctions.Count\r\n#now we create an empty array and then stock it with the number of each\r\n#type of object\r\n#We will start with a record of the number of system views and functions\r\n#that are exposed via the sys and Information_schema schemas in each database\r\n$Sizing = @(\r\n    [ordered]@{\r\n        Database = 'System\/Model';\r\n        'no. of Schemas' = $SystemSchemas;\r\n        'no. of Stored Procedures' = $SystemStoredProcedures;\r\n        'no. of Functions' = $SystemFunctions;\r\n        'no. of Views' = $SystemViews;\r\n    }\r\n)\r\n#now we add in all the system and user databases.\r\n$Sizing += $srv.databases | foreach{\r\n    [ordered]@{\r\n        Database = $_.Name;\r\n        'Object-count' = [ordered]@{\r\n            \"no. of Rules\" = $_.Rules.Count;\r\n            \"no. of Schemas\" = $_.Schemas.Count - $SystemSchemas;\r\n            \"no. of Sequences\" = $_.Sequences.Count;\r\n            \"no. of StoredProcedures\" = $_.StoredProcedures.Count - $SystemStoredProcedures;\r\n            \"no. of Synonyms\" = $_.Synonyms.Count;\r\n            \"no. of Tables\" = $_.Tables.Count;\r\n            \"no. of Triggers\" = $_.Triggers.Count;\r\n            \"no. of UserDefinedAggregates\" = $_.UserDefinedAggregates.Count;\r\n            \"no. of UserDefinedDataTypes\" = $_.UserDefinedDataTypes.Count;\r\n            \"no. of UserDefinedFunctions\" = $_.UserDefinedFunctions.Count - $SystemFunctions;\r\n            \"no. of UserDefinedTableTypes\" = $_.UserDefinedTableTypes.Count;\r\n            \"no. of UserDefinedTypes\" = $_.UserDefinedTypes.Count;\r\n            \"no. of Users\" = $_.Users.Count;\r\n            \"no. of Views\" = $_.Views.Count - $SystemViews;\r\n            \"no. of XmlSchemaCollections\" = $_.XmlSchemaCollections.Count\r\n        }\r\n    }\r\n}\r\nConvertTo-YAML $Sizing \r\n<\/pre>\n<p>So, on a nice clean instance, we get<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true\">---\r\n\r\n- \r\n    Database: 'System\/Model' \r\n    no. of Schemas: 13 \r\n    no. of Stored Procedures: 1393 \r\n    no. of Functions: 118 \r\n    no. of Views: 480 \r\n- \r\n    Database: 'AdventureWorks2014' \r\n    Object-count: \r\n      no. of Rules: 0 \r\n      no. of Schemas: 5 \r\n      no. of Sequences: 0 \r\n      no. of StoredProcedures: 11 \r\n      no. of Synonyms: 0 \r\n      no. of Tables: 71 \r\n      no. of Triggers: 1 \r\n      no. of UserDefinedAggregates: 0 \r\n      no. of UserDefinedDataTypes: 6 \r\n      no. of UserDefinedFunctions: 11 \r\n      no. of UserDefinedTableTypes: 0 \r\n      no. of UserDefinedTypes: 0 \r\n      no. of Users: 4 \r\n      no. of Views: 20 \r\n      no. of XmlSchemaCollections: 6 \r\n- \r\n    Database: 'model' \r\n    Object-count: \r\n      no. of Rules: 0 \r\n      no. of Schemas: 0 \r\n      no. of Sequences: 0 \r\n      no. of StoredProcedures: 0 \r\n      no. of Synonyms: 0 \r\n      no. of Tables: 1 \r\n      no. of Triggers: 0 \r\n      no. of UserDefinedAggregates: 0 \r\n      no. of UserDefinedDataTypes: 0 \r\n      no. of UserDefinedFunctions: 0 \r\n      no. of UserDefinedTableTypes: 0 \r\n      no. of UserDefinedTypes: 0 \r\n      no. of Users: 4 \r\n      no. of Views: 0 \r\n      no. of XmlSchemaCollections: 0 \r\n- \r\n    Database: 'msdb' \r\n    Object-count: \r\n      no. of Rules: 0 \r\n      no. of Schemas: 10 \r\n      no. of Sequences: 0 \r\n      no. of StoredProcedures: 475 \r\n      no. of Synonyms: 10 \r\n      no. of Tables: 144 \r\n      no. of Triggers: 0 \r\n      no. of UserDefinedAggregates: 0 \r\n      no. of UserDefinedDataTypes: 0 \r\n      no. of UserDefinedFunctions: 58 \r\n      no. of UserDefinedTableTypes: 1 \r\n      no. of UserDefinedTypes: 0 \r\n      no. of Users: 7 \r\n      no. of Views: 78 \r\n      no. of XmlSchemaCollections: 4 \r\n- \r\n    Database: 'tempdb' \r\n    Object-count: \r\n      no. of Rules: 0 \r\n      no. of Schemas: 0 \r\n      no. of Sequences: 0 \r\n      no. of StoredProcedures: 0 \r\n      no. of Synonyms: 0 \r\n      no. of Tables: 1 \r\n      no. of Triggers: 0 \r\n      no. of UserDefinedAggregates: 0 \r\n      no. of UserDefinedDataTypes: 0 \r\n      no. of UserDefinedFunctions: 0 \r\n      no. of UserDefinedTableTypes: 0 \r\n      no. of UserDefinedTypes: 0 \r\n      no. of Users: 4 \r\n      no. of Views: 0 \r\n      no. of XmlSchemaCollections: 0 \r\n- \r\n    Database: 'WideWorldImporters' \r\n    Object-count: \r\n      no. of Rules: 0 \r\n      no. of Schemas: 10 \r\n      no. of Sequences: 26 \r\n      no. of StoredProcedures: 42 \r\n      no. of Synonyms: 0 \r\n      no. of Tables: 48 \r\n      no. of Triggers: 0 \r\n      no. of UserDefinedAggregates: 0 \r\n      no. of UserDefinedDataTypes: 0 \r\n      no. of UserDefinedFunctions: 2 \r\n      no. of UserDefinedTableTypes: 4 \r\n      no. of UserDefinedTypes: 0 \r\n      no. of Users: 4 \r\n      no. of Views: 3 \r\n      no. of XmlSchemaCollections: 0 \r\n\r\n<\/pre>\n<p>We can, of course read this YAML into PowerShell and find out, for example, the total number of database objects, and the maximum number in any database<\/p>\n<pre class=\"theme:powershell-ise lang:ps decode:true \">Foreach-Object { ConvertFrom-YAML $OurYAMLfile } | foreach{ $_.GetEnumerator() } | #for every database \r\nWhere-Object Name -eq 'Object-count' | foreach { $_.Value } |\r\nforeach{\r\n    $_.GetEnumerator() |\r\n    Select-object @{ Name = \"Object\"; Expression = { $_.Name } },\r\n                  @{ Name = \"HowMany\"; Expression = { $_.Value } }\r\n} | Measure-Object HowMany -sum  -max |Select Sum, Maximum, Count \r\n<\/pre>\n<p>..to give\u2026<\/p>\n<pre class=\"theme:powershell-output lang:ps decode:true \">Sum Maximum Count\r\n--- ------- -----\r\n3075 1393 79<\/pre>\n<h1 id=\"post-68653-_Toc463524013\">Conclusion<\/h1>\n<p>As far as I\u2019m aware, YAML is the only format or language of data documents that is designed for both machines and humans. It is for XML what Markdown is for HTML. As data professionals, we can no longer avoid representing hierarchical information, and YAML is a good compromise. Already PostgreSQL is using YAML for EXPLAIN plans. It is great for <a href=\"https:\/\/www.simple-talk.com\/sql\/t-sql-programming\/documenting-your-sql-server-database\/\">headers of function and procedure scripts<\/a>. You can pack a lot of information into an extended property as well!<\/p>\n<h1>References<\/h1>\n<ol style=\"list-style: none;\">\n<li><a href=\"https:\/\/www.simple-talk.com\/blogs\/psyaml-powershell-yaml\/\">PSYaml: PowerShell does YAML<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/converting-xml-files-to-yaml-or-pson\/\">Converting XML files to YAML or PSON<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sysadmin\/powershell\/getting-data-into-and-out-of-powershell-objects\/\">Getting Data Into and Out of PowerShell Objects<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/t-sql-programming\/json-and-other-data-serialization-languages\/\">JSON and other data serialization languages<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/t-sql-programming\/documenting-your-sql-server-database\/\">Documenting your SQL Server Database<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Exporting SQL Server query results and configuration data as YAML using PowerShell and SQL Server Management Objects (SMO). Handling XML column types, capturing endpoint settings, object inventory scripts, and comparing config snapshots. Includes the PSYaml module setup.&hellip;<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,35],"tags":[],"coauthors":[6813],"class_list":["post-68653","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-powershell"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68653","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\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=68653"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68653\/revisions"}],"predecessor-version":[{"id":73409,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/68653\/revisions\/73409"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=68653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=68653"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=68653"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=68653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}