My organization is beginning to rollout SQL Server 2014 on Windows Server 2012 R2 servers with the Minimal Server Interface. This means it was finally time for me to actively use PowerShell to script my SQL Server installations since I wasn’t going to be able to lean on the GUI for the Windows Installer anymore. I’ve always been trying to find the time to use PowerShell but always too busy to sit down and really work with it.
There are three main tasks involved with how my organization installs SQL Server:
- The installed features and their configurations
- I completed this by running the SQL Server setup.exe from the command prompt with all the appropriate parameters set for a typical installation
- Moving system databases and log files to standardized paths
- Moving the system databases requires a combination of running some queries, reconfiguring SQL services, creating folders and setting permissions on folders, modifying and setting permissions on registry keys and moving files
- Configuring features and SQL Server settings
- this portion is simply setting some SQL server options and configuring things like Database Mail, SQL Agent Operators, setting options in model, backing up the Service Master Key and configuring some logins. All of this work is done via T-SQL
Now that I have begun using PowerShell, I love it. There were plenty of issues for me to work through and learn from but now I know with certainty that my servers are installed consistently every time no matter who does the work.
The great thing about PowerShell is that it can handle all of these tasks from beginning to end in a single script. Also, since I no longer need to spend time navigating the UI to complete configurations and the installer no longer includes any features that use a GUI like SSMS or SSDT the install duration is dramatically faster.
Below are a few of the things I learned that either cost me time to figure out or saved me time troubleshooting:
Trouble With the Command Line
There’s a lot of ways to run an executable from PowerShell: Invoke-Expression, Invoke-Command, Invoke-Item, Call Operator &, using the command shell cmd /c, Start-Process, the list goes on.
I was having problems because there’s a lot of parameters used when running SQL Installer from the command line and some of those parameters require they be quoted because of spaces used in their values. I tried nearly every possible cmdlet to get setup.exe to run but I ended up falling back to the trusty old command shell and the cmd /c command to get setup.exe and its parameters passed correctly.
Trouble Setting Permissions on Folders
I was trying to grant the NT SERVICE\MSSQLSERVER Virtual Account permission to some folders using the Set-Acl cmdlet and ran across the error described in this connect feedback. This was super frustrating and I worked around it by running icacls.exe from the command shell (using cmd /c) to set permissions on the problem folders the old fashioned way.
Trouble Loading SQLPS Module
In my all encompassing script the first step installs SQL Server. Part of that install adds some of the SQL Server folders to the Path environment variable on your machine. In one of those folders is the SQLPS module needed to run queries later on in my script.
However, because the installer updates the Path after the PowerShell session started and because the PowerShell tool does not automatically update its reference to the Path when a change occurs you need to use Import-Module from the full path to the SQLPS or your script will error letting you know it cannot find the SQLPS module even though you just installed it. You’d import it with a command similar to:
Import-Module “%Program Files%\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS”
Trouble With Multiple Variables in a Single Line
One portion of my script exports the Service Master Key and we save it with a particular file name SMK_hostname_yyyyMMdd.smk I used a couple variables to get the date formatted right and the host name then built the path in my PowerShell script to be something like “\mssqlserver\backup\SMK_$hostname_$date.smk”. It turns out PowerShell will only replace the last variable on a single line in a string so my filename was missing the host name. You need to build the file name in multiple steps into a single variable to use later in your query:
$serverName = hostname $currentDateFormatted = Get-Date -format yyyyMMdd $SMKFileName = "E:\mssqlserver\backup\SMK_$serverName" $SMKFileName = $SMKFileName + "_$currentDateFormatted.smk"
Your SQL query can then be built to use the previously generated path like this:
$query=@" BACKUP SERVICE MASTER KEY TO FILE = 'E:\mssql\mssqlserver\backup\$SMKFileName' "@
-Verbose is Helpful with Invoke-Sqlcmd
While troubleshooting my PowerShell script I used a lot of “string” | Out-Host commands to print information about where the script was and what it was doing. But once it ran a query with Invoke-Sqlcmd I was a little blind at first. I ended up using PRINT statements in my T-SQL and adding the -Verbose parameter so my PRINT statements would display back in the PowerShell window. This helped a lot with peeking inside the Invoke-Sqlcmd black box when troubleshooting where a query may have gone awry.