Sev17

Last month I sat down with Blain Barton and TechNet Edge in an interview (Blain Barton Interviews Raymond James Chad Miller on PowerShell) and described a PowerShell-based solution for providing a classic centralized data dictionary of various data sources to our IT users. I’m pleased to announce I’ve started a CodePlex project called Really Simple Data Dictionary or RSDD which demonstrates the coding techniques used to develop the solution.

What is RSDD?

RSDD is a meta data collector for SQL Server and Oracle.  RSDD imports meta data into a central SQL Server repository from INFORMATION_SCHEMA or equivalent supported systems views. RSDD is packaged as PowerShell module, to get started you’ll need PowerShell 2.0 and SQL Server instance to host the repository. Check out the documentation for setup instructions. Once you’ve collected the meta data, the data can easily be exposed via reports or PowerPivot (see screenshots).

Next Steps

• Install RSDD (As always: Test in non-production environment first!)
• Leave feedback on the project site

Interested in Working on RSDD?

Contact me if you’re interested in contributing to RSDD

My thanks to everyone at the Space Coast SQL Server User Group for inviting me to speak. Feel free to post questions or comments. The presentation and supporting materials for the Powershell ETL session are available here:

A year ago I blogged about building a SQL Server 2008 Object Dependency Viewer based on a script by PowerShell MVP, Doug Finke (Blog|Twitter). Since then Doug has created an alternative solution based on the new Microsoft Automatic Graph Layout features in Visual Studio 2010. The approach Doug takes builds a DGML XML file using PowerShell which then can be opened in Visual Studio 2010.

I thought it would be interesting to create an updated version of the SQL Server Object Dependency Viewer using DGML. Rather than simply running Doug’s script as-is I decided to develop an alternative solution targeted for SQL Server. Because the new solution only requires creating a DGML XML file and SQL Server can emit XML natively I used the following T-SQL/XQuery (no PowerShell required! Nonetheless I included a one-liner at the end of this post)

Requirements:

• SQL Server 2008 or higher database
• Visual Studio 2010

Run the following script from SQL Server Management Studio

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

;WITH
xmlnamespaces
(
DEFAULT 'http://schemas.microsoft.com/vs/2009/dgml'
)
,Links AS
(SELECT 1 AS 'DirectedGraph')
,Link AS
(SELECT DISTINCT
OBJECT_NAME(referencing_id) AS [Source],
COALESCE(referenced_server_name + '.','') + COALESCE(referenced_database_name + '.','')
+ COALESCE(referenced_schema_name + '.','') + referenced_entity_name AS [Target],
o.type_desc AS SourceType
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.OBJECT_ID
AND o.type_desc != 'CHECK_CONSTRAINT')
 
SELECT
(
	SELECT [Source] AS "@Source", [Target] AS "@Target"
	FROM Link FOR xml PATH('Link'), type
)
FROM Links FOR xml AUTO, root('DirectedGraph'), type

Save the output as a dgml file, for example AdventureWorksLT.dgml. Next double-click to open the file in Visual Studio. You should see a dependency graph similar to this:

If you want to automate a the steps of saving and opening the DGML file. Save the T-SQL script above as dgml.sql and create a PowerShell script you can then call from sqlps host:

1
2
3

$fileName = "C:\Users\u00\Desktop\AdventureWorks.dgml"
Invoke-Sqlcmd -ServerInstance "Z003\R2" -Database "AdventureWorksLT" -InputFile "C:\Users\u00\Desktop\dgml.sql" -MaxCharLength 8000 | Select -ExpandProperty Column1 | Set-Content $fileName
invoke-item $fileName

Dave Levy (Blog|Twitter) posted a script in a blog post in which he uses a bit of SQL PowerShell Extensions (SQLPSX) and some Replication Management Objects (RMO) to script out SQL Server replication.  Overall Dave’s script is a good use of PowerShell and RMO. Scripting out objects is much easier to handle in PowerShell than a T-SQL solution, however some improvements to the script can be made.

EDIT: Dave pointed out I was missing the Append parameter for Out-File. The script below has been corrected.

One of the goals of SQLPSX is simplify SQL Server PowerShell scripting by providing functions over common tasks. I think this important as the PowerShell host which ships with SQL Server, sqlps, does not cover replication. The original script can be refactored to use SQLPSX instead of working with the RMO classes directly as well reduce some of the code as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

param ($sqlServer,$path,[switch]$scriptPerPublication)
Import-Module Repl
 
if ($sqlServer -eq "")
{
    $sqlserver = Read-Host -Prompt "Please provide a value for -sqlServer"
}
 
if ($path -eq "")
{
    $path = Read-Host -Prompt "Please provide a value for output directory path"
}
 
    $scriptOptions = New-ReplScriptOptions
    $scriptOptions.IncludeArticles = $true
    $scriptOptions.IncludePublisherSideSubscriptions = $true
    $scriptOptions.IncludeCreateSnapshotAgent = $true
    $scriptOptions.IncludeGo = $true
    $scriptOptions.EnableReplicationDB = $true
    $scriptOptions.IncludePublicationAccesses = $true
    $scriptOptions.IncludeCreateLogreaderAgent = $true
    $scriptOptions.IncludeCreateQueuereaderAgent = $true
    $scriptOptions.IncludeSubscriberSideSubscriptions = $true
 
    $distributor = Get-ReplServer $sqlserver
 
if($distributor.DistributionServer -eq $distributor.SqlServerName)
{
	$distributor.DistributionPublishers | ForEach-Object {
		$distributionPublisher = $_
		if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
		{
			$outPath =  "{0}\from_{1}\{2}\"  -f $path,$distributionPublisher.Name.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
			New-Item $outPath -ItemType Directory | Out-Null
			Get-ReplPublication $distributionPublisher.Name | ForEach-Object {
				$publication = $_
				$fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
				if($scriptPerPublication)
				{
					$fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
				}
				Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
				Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName -Append
			}
		}
	}
}
else
{
    $outPath =  "{0}\from_{1}\{2}\"  -f $path,$distributor.SqlServerName.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
    New-Item $outpath -ItemType Directory | Out-Null
    Get-ReplPublication $distributor.SqlServerName | ForEach-Object {
		$publication = $_
		$fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
		if($scriptPerPublication)
		{
			$fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
		}
		Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
		Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName -Append
	}
}

Save the script as a .ps1 file, scriptPublications.ps1 and invoke with:

1
2

.\scriptPublications.ps1 -sqlserver "Z002\sql2k8" -path "C:\Users\u00\repl"
.\scriptPublications.ps1 -sqlserver "Z002\sql2k8" -path "C:\Users\u00\repl" -scriptPerPublication

Comments:

• Notice the use of New-ReplScriptOptions. This is a helper function to handle the replication scripting options.
• Replace a bool with a switch param. A switch is kind of like a bool, but you don’t specify $true or $false. In PowerShell scripting switch should be used over bool.
• Removed unneeded code

If you’re working with PowerShell and SQL Server one of things you’ll want to to do is load the SQL Server 2008 provider and cmdlets into a regular PowerShell. Michiel Wories, the creator of SMO and sqlps, provides an initialization script in his blog post SQL Server PowerShell is Here! The script will load SQL Server provider, cmdlets, required assemblies and set global variables expected by the SQL Server provider.

Creating the sqlps module

In PowerShell version 2, modules provide an alternative approach to initialization scripts used in PowerShell V1. To turn Michiel’s initialization script into a module simply create a folder called sqlps under \Documents\WindowsPowerShell\Modules and save the script as sqlps.psm1 instead of Initialize-SqlpsEnvironment.ps1.

You can then execute:

import-module sqlps

You’ll notice the following warning:

WARNING: Some imported command names include unapproved verbs which might make them less discoverable. Use the Verbose parameter for more detail or type Get-Verb to see the list of approved verbs.

Running get-command -Module sqlps, you’ll notice the Encode-SqlName and Decode-SqlName cmdlets and since neither is an approved verb – hence the warning. To avoid the warning message when loading your new sqlps module use

import-module sqlps –DisableNameChecking

An Alternative Approach

Rather than turning the original initialization script into a module, we could create a more structured implementation making use of a PowerShell manifest file (psd1) file. Using this approach we’ll need to copy the following snapins related files/folders from C:\Program Files\Microsoft SQL Server\100\Tools\Binn to  Documents\WindowsPowerShell\Modules\sqlps folder.

• en
• Microsoft.SqlServer.Management.PSProvider.dll
• Microsoft.SqlServer.Management.PSSnapins.dll
• SQLProvider.Format.ps1xml
• SQLProvider.Types.ps1xml

Next we’ll create a sqlp.psd1 manifest which contains the instructions for processing our new module:

@{
ModuleVersion="0.0.0.1"
Description="A Wrapper for Microsoft's SQL Server PowerShell Extensions Snapins"
Author="Chad Miller"
Copyright="© 2010, Chad Miller, released under the Ms-PL"
CompanyName="http://sev17.com"
CLRVersion="2.0"
FormatsToProcess="SQLProvider.Format.ps1xml"
NestedModules="Microsoft.SqlServer.Management.PSSnapins.dll","Microsoft.SqlServer.Management.PSProvider.dll"
RequiredAssemblies="Microsoft.SqlServer.Smo","Microsoft.SqlServer.Dmf","Microsoft.SqlServer.SqlWmiManagement","Microsoft.SqlServer.ConnectionInfo","Microsoft.SqlServer.SmoExtended","Microsoft.SqlServer.Management.RegisteredServers","Microsoft.SqlServer.Management.Sdk.Sfc","Microsoft.SqlServer.SqlEnum","Microsoft.SqlServer.RegSvrEnum","Microsoft.SqlServer.WmiEnum","Microsoft.SqlServer.ServiceBrokerEnum","Microsoft.SqlServer.ConnectionInfoExtended","Microsoft.SqlServer.Management.Collector","Microsoft.SqlServer.Management.CollectorEnum"
TypesToProcess="SQLProvider.Types.ps1xml"
ScriptsToProcess="Sqlps.ps1"
}

Notice a sqlps.ps1 script file is referenced which  is used to set the variables needed by the provider:

Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

Since the sqlps licensing terms from Microsoft allow redistribution as long as the original installer is included, I’ve included a sqlps module zip file.

Notes

1. SQL Server Management Studio is not required to run sqlps or the sqlps module demonstrated in this post as long as the required assemblies are installed
2. Like sqlps, Microsoft SQL Server 2008 Management Objects and Microsoft Core XML Services (MSXML) 6.0 are required.
3. If you’d also like to also run sqlps host without install SQL Server Management Studio download the installation from the SQL Server 2008 or R2 Feature Pack.

At the July 8th Tampa PowerShell User Group meeting, Ed Wilson (blog|twitter) gave a presentation on “PowerShell Best Practices.” One of the tips Ed mentioned is enabling Windows Search to index the content of PowerShell ps1 files. Although I’ve used grep or in PowerShell, Select-String to find strings within script files there are advantages to being able to search  in Windows Explorer and yes its OK to use the GUI sometimes.

If like me you haven’t used Windows Search features in years, you’re probably wondering how to you set the indexing options. So I’ve provided a step-by-step guide…

Changing Indexing Options

In Windows 7 or Vista on the Start Menu in the Search Programs type Indexing options

Select Indexing Options located under Control Panel

Under Indexing Options select Advanced

Next select the File Types tab

Select the ps1 file extension and with the file extension selected, choose Index Properties and File Content

Click OK and Close

Window will start indexing your ps1 file immediately. You will then be able to search the content of PowerShell scripts within Windows Explorer. Here’s an example searching for the string invoke-coalesce across all PowerShell scripts:

Although grep and select-string are more full-featured (for example the matching line and line numbers can be returned), Windows Search is fine for simple searches. You may want to enable content search for other types of script files such as .sql files also

A co-worked asked me to look at a T-SQL script I had written 8 years ago for scripting out linked servers and linked server logins from SQL Server. The script wasn’t working as expected. I hadn’t seen the code in some time, but looking at it now the fact that it did not work wasn’t surprising as the code uses some of bad practices like directly querying system tables which I’ve discouraged in previous posts. So rather than fix the T-SQL script, I fired up sqlps PowerShell host and in 5 minutes I had a much simpler and working one-line PowerShell command:
From SQL Server 2008 SSMS navigate to the Linked Servers folder in Object Explorer, right-click and start sqlps (PowerShell):
Script out Linked Servers and logins

PS SQLSERVER:\SQL\Z002\SQL1\LinkedServers get-childitem | %{$_.Script()}

A few things struck me about the PowerShell solution:

• This isn’t something that can be done from the GUI (SQL Server Management Studio). The functionality simply isn’t there nor should it be. Some advanced things are easier and better exposed in PowerShell than the GUI
• PowerShell is certainly much easier than my old T-SQL solution.
• Unlike the T-SQL solution, I doubt I will be asked to fix this simple PowerShell command in another 8 years.

The Microsoft Clustering and High Availablity bloggers have taken noticed of the Remote Desktop Connection Manager (RDCMan) utility from a cluster management perspective. I’ve previously blogged about Building A Remote Desktop Manager Connection List from the SQL Server Central Management Server (CMS) tables. The RDCMan use case for cluster administration is very useful and it just so happens that I built a similar solution for cluster servers. So, as a follow up here’s a way to auto generate an RDCMan file from a list of clusters, nodes and virtuals.

First create and populate a cluster, cluster_node and cluster_virtual tables defined in the following T-SQL script file:

Note: You’ll need to insert your cluster information into the three tables.
Next use the following T-SQL XQuery to generate the RDCMan XML and save the file as cluster.rdg.

Finally open the cluster.rdg file in Remote Desktop Connection Manager. RDCMan allows to you to have multiple rdg files open at one time which means our previous CMS-based list of SQL Server and our cluster-based list can both be used simultaneously.

Enjoy!

My thanks to everyone in attendence and PASS AppDev VC for inviting me to speak. I’ll post a link to the video as soon as its available. Video is available here. Feel free to post questions or comments. The presentation and supporting materials for the Powershell ETL session are available here:

On Tuesday June 22nd 9 PM Eastern I”m presenting ETL with PowerShell for PASS AppDev VC—a virtual chapter of Professional Association for SQL Server (PASS). In this presentation we will look at performing common data loading tasks with Powershell. A basic understanding of PowerShell is helpful, but not necessary. Specific topics covered include importing structured files, XML, WMI objects and ADO.NET data sources.

This is a subject which forms the foundation of many my production PowerShell scripting solutions. The Live Meeting is open to anyone, mark your calendar or check the recording afterward.