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: https://blogs.msdn.com/b/sqlupdates/
Agenda
Not covered in this session: PowerShell how-to and syntax details
Recommend Microsoft Virtual Academy JumpStart videos for this: https://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
History
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
SQL2008
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 2012and SQL 2014 loads PowerShell 2.0 (can verify via get-host or $PSVersionTable)
https://technet.microsoft.com/en-us/library/cc280450(v=sql.120).aspx
Quick Concepts and Cmdlets
General
PSDrives: Allow object enumeration similar to file system
Get-PSDRIVE
Discovery
Get-Help: learn how to use a commandlet
Get-Command: to find commands
Get-Member: enumerate properties and methods of an object
Pipelines
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
SQL
Invoke-SQLCmd
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.
Pros:
Easier administration and troubleshooting (can run logic locally if needed)
Easier per server transactional processing
Cons:
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 ofpossible 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 versionand 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)
-or-
Connect-WSMan $svr
Set-Item WSMan:\$svr\Service\Auth\CredSSP -Value $true
https://blogs.technet.com/b/heyscriptingguy/archive/2012/11/14/enable-powershell-quot-second-hop-quot-functionality-with-credssp.aspx
Lessons Learned
Prompt for passwords instead of storing within INI files
Collect password via read-hostAsSecureString 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
https://blogs.technet.com/b/ilikesql_by_dandyman/archive/2013/04/17/automating-the-installation-of-my-bi-demo-platform-with-data-explorer-geoflow-visual-studio-2012-bi-projects-and-database-projects-sql-server-2012-sp1-cu3.aspx
SSDTBI_x86_ENU.exe/ACTION=INSTALL /FEATURES=SSDTBI,SNAC_SDK /Q /IACCEPTSQLSERVERLICENSETERMS
Lessons Learned
SSDT (Visual Studio) command line install not documented (by design)
https://connect.microsoft.com/VisualStudio/feedback/details/759185/missing-documentation-on-admindeployment-xml
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
References
PowerShell the SQL Server Way https://sqlmag.com/powershell/powershell-sql-server-way
10 Tips for the SQL Server PowerShell Scripter https://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx
Technet Script Center https://technet.microsoft.com/en-us/scriptcenter/default.aspx
Windows Powershell Blog https://blogs.msdn.com/b/powershell/
Hey, Scripting Guy Blog https://blogs.technet.com/b/heyscriptingguy/
Technet Script Centerhttps://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