PowerShell Lessons Learned from Building an Automated SQL Installation and Patch Management Implementation

Attached are the PowerPoint slides from the recent presentation I gave for the SQL Server User’s Group meeting. 

Thank you all for attending!

Download the presentation here.

<Extracted Slide Text>

PowerShell Lessons Learned from Building an Automated SQL Installation and Patch Management Implementation

Presented by: Fany Carolina Vargas, Microsoft Corp., Sr. PFE, SQL Dedicated Support

Blog: http://blogs.msdn.com/b/sqlupdates/



Not covered in this session: PowerShell how-to and syntax details

Recommend Microsoft Virtual Academy JumpStart videos for this: http://www.microsoftvirtualacademy.com/training-courses/getting-started-with-powershell-3-0-jump-start

PowerShell: Understanding Why

Using PowerShell for SQL Tasks

Lessons Learned from Automating SQL Deployments

PowerShell: Understanding Why



User feedback about system management experience

Admin experience should be consistent

Command line tools are not always consistent in syntax and behavior

Often requires combination of GUI and command tools

Many of these tools must be downloaded separately from different places and have various version requirements and pre-requisites

Difficult to interact between tools since most return strings and success/fail error codes

VBScript often used for more complex logic, however it does not provide interactive sessions

.NET programs often used to tap into needed APIs, but also not interactive session and requires creating Exes/Dlls


PowerShell Capabilities

Command Line Shell &  Object-Oriented Scripting Language

Designed for system administrators

Built on .NET framework

Built-in commands called “cmdlets” which may accept object parameters

Verb-Noun naming convention

Can return objects back to the shell for additional manipulation

Have a consistent syntax (and many common parameters)

Allows direct interaction and manipulation of .NET objects (like a .NET program)


PowerShell Capabilities

Allows interactive commands and easy interaction with other executables (like a shell)

Provides consistent navigation of various data stores (get-PSProvider, get-PSDRIVE)

Registry, SQL,File System can be enumerated via DIR,CD commands

Extensible interface

Can load providers via import-module

Integrated with most Microsoft products (SQL,Hyper-V,Exhange, System Center Suite, SharePoint,etc)

Integrated into Windows for local and remote management

Key for Windows Server Core environments

Common management framework



Using PowerShell for SQL Tasks

SQL PowerShell Environment


SQLPS.exe: limited shell, not all OS functionality

SQL 2012 and higher

No longer just a mini-shell. You can instead import SQL modules for access to pre-packaged SQL cmdlets

Import-module SQLPS

This loads all SQL SMO classes, cmdlets and assemblies

Advanced users could optionally choose to import only needed assemblies (via .NET assembly load capabilities)


SQL PowerShell Environment

Accessing “start PowerShell” option within via management studio starts “Program Files(x86)\Microsoft SQL Server\110\Tools\Binn\sqlps.exe”

Sqlps always loads base pre-requisite powershell version for the SQL instance

SQL 2012  and SQL 2014 loads PowerShell 2.0 (can verify via get-host or $PSVersionTable)



Quick Concepts and Cmdlets


PSDrives: Allow object enumeration similar to file system



Get-Help: learn how to use a commandlet

Get-Command: to find commands

Get-Member: enumerate properties and methods of an object



Series of commands connected by the pipeline operator “|”

Each command sends its results to the next command in the pipeline

Example: Get-Process notepad | stop-Process



invoke-PolicyEvaluation  (PBM)


When to use PowerShell for SQL Tasks

Bridge gap between SQL and objects external to SQL instance

Collect inventory details about other services, service accounts, server info, instance info

Smart Backup/Restore scripts

Check for old files : get-childitem \\Server1\share1\ServerName\*.bak | where-object { $_.LastWriteTime -ge (Get-Date).AddDays(-10) } | Select name, LastWriteTime

Tie drive space and file age logic into your backup scripts

Enumerate and assign mount point permissions



When to use PowerShell for SQL Tasks

Tie into Active Directory (example find + collect AD user properties for a SQL user and import details into a SQLtable)

Tie in .NET objects to SQL objects easily

Example generate random password for a new SQLlogin

[Reflection.Assembly]::LoadWithPartialName(”System.Web” ) [System.Web.Security.Membership]::GeneratePassword(…….)

Tie PBM xml files + PBM cmdlets for central Policy Based Management evaluation

Automate SQL deployments

Write upgrade/Install scripts with logic to configure service accounts, folder permissions

Use PowerShell remoting capabilities for remote SQL deployments


When to use PowerShell for SQL Tasks

Always On Availability Groups setup

Extract cluster properties and IP addresses, feed that into configuration scripts

Monitoring and Auditing

Parse SQL errorlog

Utilize WMI commandlets for server management

get-wmiobject Win32_QuickFixEngineering | findstr KB12345

Allow SQL agent jobs to execute complex logic

Use PowerShell subsystem (be aware this is the more limited PowerShell environment)


When to use PowerShell for SQL Tasks

Make use of other pre-packaged APIs, extensions, and cmdlets

Example: codeplex.com has various powershell extensions for SQL

Use SMO to script SQL objects

Manipulate object properties directly and at a granular level

Example: Instead of simply listing indexes, can extract and manipulate each index object directly

Can minimize amount of code needed

Work with variables more easily(remove the need for dynamic building of T-SQL statements)

Utilize PowerShell remoting and parallelism capabilities (Jobs/Workflows)


Lessons Learned from Automating SQL Deployments


Deployment Tools Used

PowerShell to script SQL installations and configuration

Call into pre-existing T-SQL scripts via invoke-sqlcmd

Make use of command line SQL setup options, configuration files, custom XML configuration files

System Center suite

Orchestrator to create run-books and workflows

Virtual Machine Manager to configure VMs


Lessons Learned

Create scripts which can run locally, and then create a parent script for remoting.


Easier administration and troubleshooting (can run logic locally if needed)

Easier per server transactional processing


Additional complexity when passing variables to child scripts + all commands must be able to run from remote servers as well.

Error prone if PowerShell versions are not consistent across servers

Remote server needs to be able to execute the exact command (versus the –ComputerName option which may translate nicely to a different remote command)


Lessons Learned

Beware of  possible one-at-a-time processing with pipelines

get-process | get-member  !=  get-member -inputobject (get-process)

If script requires access to registry, make sure proper bitness of powershell.exe is being used, otherwise you will incorrectly access 32-bit registry keys

$osObj=get-wmiobject -Class Win32_Processor

$constOSArchitecture =$osObj.Architecture

#if running 32 bit powershell, but OS is 64 bit

if($env:PROCESSOR_ARCHITECTURE -eq "x86" -and ( -not ($constOSArchitecture -eq 0) ) ){

            Write-host("Running 32-bit powershell in 64-bit OS. Script cannot continue. Please restart powershell in 64-bit mode.")





Lessons Learned

Remember that while writing PowerShell scripts is very similar to .NET programming, it is also a shell and pipeline buffer is being built dynamically

Problem may be specific to how data is passed to the shell

Example: Issue with different output formatting on older versions of PowerShell



Lessons Learned

For easier administration:

Store and execute scripts centrally (UNC share)

Place script output centrally (UNC share)

Use latest version of PowerShell on central management server

Establish a standard PowerShell version within your environment

Document the required minimum PowerShell version

If script uses newer constructs, specify minimum version  and indicate why that is the minimum required version in a comment:

#requires -Version 2.0

#requires -Version 3.0


Lessons Learned

Running scripts from UNC share remotely requires PowerShell impersonation (access denied error otherwise)

Client needs to specify list of machines which can delegate its credentials

Enable-WSManCredSSP -role client -DelegateComputer $servernameArr

Server specifies it intends to delegate (this requires elevation)

Enable-WSManCredSSP -role server –Force (on the server itself)


Connect-WSMan $svr

Set-Item WSMan:\$svr\Service\Auth\CredSSP -Value $true



Lessons Learned

Prompt for passwords instead of storing within INI files

Collect password via read-host  AsSecureString and SecureStringToBSTR

Internet downloaded files must be unblocked

Use commandlet:  unblock-file

For W2012 or higher

Use Mount-DiskImage to easily mount ISO files



Lessons Learned

Automating SSDT and SSDT-BI installation

Both are separate from SQL installation and require different params

SSDT-BI command line install not well documented yet





Lessons Learned

SSDT (Visual Studio) command line install not documented (by design)




Passing Variables to Invoke-Command script blocks can be tricky

Invoke-Command -Session $s1 -ScriptBlock{param($arg2) &"\\Share1\scrip1.ps1" $arg2} -ArgumentList $arg1



Make use of classes (available in PowerShell 2.0 +) for cleaner Object Oriented code


$def2= @‘ public class SQLPatch{      public string DisplayName;

            public string LastUsedSource;

            public string PackageName;

            public string Installed;

            public string LocalPackage; }'@  

Add-Type -TypeDefinition $def

$SQLPatch = New-Object SQLPatch


Demo: Walkthrough PowerShell Script Snippet


PowerShell the SQL Server Way http://sqlmag.com/powershell/powershell-sql-server-way

10 Tips for the SQL Server PowerShell Scripter http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx

Technet Script Center http://technet.microsoft.com/en-us/scriptcenter/default.aspx

Windows Powershell Blog http://blogs.msdn.com/b/powershell/

Hey, Scripting Guy Blog http://blogs.technet.com/b/heyscriptingguy/

Technet Script Center  https://technet.microsoft.com/en-us/scriptcenter/dd742419.aspx

Windows PowerShell Quick Reference https://www.microsoft.com/en-us/download/details.aspx?id=7097  

</Extracted Slide Text>





Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services

Skip to main content