Powershell and SQL Server have been natural buddies from day one. SQL Server administrators need to automate file operations and other Windows tasks in conjunction with their database duties all the time, and xp_cmdshell only gets you so far. Fortunately, since the introduction of the SQLPS Powershell module in SQL Server 2008R2, you no longer have to load tiresome SMO assemblies to interface with SQL Server through Powershell. SQLPS uses a suite of cmdlets to provide a pretty decent user experience for the Powershell/SQL developer, although it’s not without its quirks. Let’s take a look at a few quick tips and tricks I’ve picked up while using this module in the wild. I hope they save you some of the time it took me to learn them!
5 Tips and Tricks for using SQLPS
1. You don’t need SQL Server to install SQLPS
This is great news if you want to script against remote SQL Server instances (or even a DBaaS solution like AWS RDS) without installing the heavy SQL Server database engine on your local machine. You just need to download the Sql Server [version] Feature Pack where [version] is later than 2005 from http://www.microsoft.com/en-us/download/details.aspx?id=29065 and execute these three installers in the following order:
- Microsoft® System CLR Types for Microsoft® SQL Server® [version]
- Microsoft® SQL Server® [version] Shared Management Objects
- Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® [version]
Just like that, the SQLPS module will be set up and ready to use on your local machine! (h/t Max Trinidad)
2. The auto-loading is cantankerous
You should be able to start using SQLPS cmdlets in your scripts right away, but I’ve noticed that the SQLPS module sometimes has trouble loading implicitly. If you don’t explicitly import the module before running commands, you might see an error like this from time to time:
The Invoke-Sqlcmd command was found in the module SQLPS, but the module could not be loaded. For more information, run Import-Module SQLPS.
Due to this issue, Powershell gurus Mike Robbins and Ed “The Scripting Guy” Wilson recommend always explicitly importing the module with the I
mport-Module SQLPS command.
3. Disable name checking
When you run
Import-Module SQLPS, you’ll see this funky message:
WARNING: The names of some imported commands from the module ‘sqlps’ include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
Import-Module SQLPS -Verbose reveals that the problem cmdlets are “Encode-SQLName” and “Decode-SQLName”, which do not conform to Powershell’s verb standards. This is a choice on the SQLPS designers’ part and there’s nothing you can do about it, but if you don’t want to see the warning, disable it as follows:
Import-Module SQLPS -DisableNameChecking
4. Watch out for default timeouts
SQLPS cmdlets work smoothly with no issues whatsoever. Except when they don’t. (Here’s the lengthy bug list for the Invoke-Sqlcmd cmdlet on Microsoft Connect.) Never fear; you’re not likely to encounter most of those issues. The most important SQLPS bug to know about is that the Invoke-Sqlcmd cmdlet times out by default, even though the documentation claims it doesn’t. 30 seconds seems to be the default window, which isn’t nearly long enough to run, say, a backup or restore command on a decent-sized database. You’ll likely run into the following error if you try to invoke some long-running T-SQL through Invoke-Sqlcmd:
Invoke-Sqlcmd : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This excellent TechNet article outlines a couple workarounds for the issue for various SQL versions. Since I mainly work with SQL 2012 servers right now, I always specify the “QueryTimeout” parameter to the call with a value of 0, which does not set the timeout to zero seconds (as you might expect), but prevents it from timing out entirely. Here’s an example command using implicit Windows authentication:
InvokeSqlCmd -QueryTimeout 0 -Query "SELECT * FROM myTable" -Database myDB -ServerInstance mySQLServer
5. There’s only one cmdlet you really need
In one sense, the SQLPS module cmdlets are almost entirely redundant. Most of them are just wrappers around T-SQL that you could call yourself through the Invoke-SQLCmd cmdlet. For example, these two calls are functionally identical:
Backup-SQLDatabase -BackupAction Log -ServerInstance myServer -Database myDB -BackupFile "C:\backup.trn" Invoke-SQLCmd -Query "BACKUP LOG myDB TO DISK='C:\backup.trn'" -ServerInstance myServer -Database myDB
So if you can do anything on SQL Server you want through Invoke-Sqlcmd, why did the SQLPS designers choose to include so many other cmdlets? I suspect there are at least a couple of reasons.
First, providing native Powershell support for very common, basic things like backups and restores allows reluctant DBAs/sysadmins to perform SQL Server tasks without having to “get their hands dirty” with T-SQL. Plus, a pure Powershell cmdlet is going to be easier to read and maintain than one with long strings of embedded T-SQL.
Another reason may be related to Microsoft’s cloud strategy as they continue to push configuration management with Powershell through DSC. If you look at the full list of cmdlets in the module for SQL Server 2012, you’ll notice that a plurality–if not a majority–of them have to do with AlwaysOn Availability Groups. This is a high-availability technology new in Server 2012, and it’s not much fun to to work with in pure T-SQL; Powershell provides a much cleaner interface for accessing replicas and listeners. Microsoft wants people to use Powershell in cloud environments to automate database configuration tasks like availability group setup. In other words, these cmdlets are really intended more for database architects and cloud engineers than DBAs.
That’s all for now! Let me know if you have spotted any other quirks in the SQLPS module that I should add to this post.