SQL Server 2008 was the first version of SQL Server to include any significant support for PowerShell, but it was...
fairly minimal. With SQL Server 2012, Microsoft has gone whole-hog SQL Server, adding support throughout the product’s components, including Analysis Services and Integration Services as well as the core database engine. At first glance, this PowerShell support seems confusing, but with a few facts in mind you’ll be able to make sense of it.
For more about PowerShell:
Learn how to create Windows PowerShell scripts to manage SQL Server 2008 instances
Replicating tables without primary keys
First, there’s only one shell; despite Microsoft product teams’ proclivity for creating product-specific PowerShell console icons on your Start menu, they’re all the same shell. Those per-product icons simply load a product’s PowerShell add-ins as the shell starts, getting you up and running more quickly. You can still use Add-PSSnapin and Import-Module from any shell instance to load a product’s extensions on your own. In some cases, those product-specific icons may run an entire script, which you should also run if you want to load the product extensions on your own.
Next, be aware that SQL Server uses a mixed approach to administration. While the product’s extensions do add commands (or rather, “cmdlets”) to PowerShell, much of SQL Server’s PowerShell functionality comes in the form of PSProviders. Essentially, a PSProvider makes SQL Server look like a big disk drive, with “folders” for various components like Analysis Services and the database engine. Configuration settings are exposed as “files,” and you use a specific set of PowerShell cmdlets -- like Set-ItemProperty and Get-ItemProperty -- to manipulate those settings. It’s very similar to how you’d manipulate the registry using the HKLM: or HKCU: drives in PowerShell. Why couldn’t the SQL Server team just provide cmdlets for everything? That would certainly have been an easier-to-learn option, I suspect, but the dynamic nature of SQL Server would have made it challenging. As is, the “drive” can adapt itself to various SQL Server configuration and installation scenarios, whereas cmdlets expect a more static situation. The Internet Information Services team made the same decision, providing a range of cmdlets but also relying on the dynamic adaptability of a PSProvider. After loading the SQL Server extensions into the shell, run Get-PSDrive to see your SQL Server drive; change to it using the old CD command and start working away.
SQL Server does have a few unique conventions. For example, it doesn’t use the standard ` (backtick) character to escape otherwise-illegal characters in things like server and object names. Instead, the SQL Server team chose to use URL encoding so that a space comes out look like “%20.” A few provided cmdlets encode and decode for you, so you don’t need to remember hexadecimal values for unusual characters.
The best thing to remember? To ask for help. Community website PowerShell.com offers a SQL Server-specific Q&A forum; there are also sites like StackOverflow.com and ServerFault.com, where you can get assistance from your peers with tricky problems. Rather than banging your head against something for days on end, post a concise, well-worded question and see if someone can help you move forward.
Above all, don’t ignore PowerShell. Sure, you’ll always get a lot done with Transact-SQL, but PowerShell is Microsoft’s way forward, and the SQL Server team has integrated it pretty broadly throughout the product. It’s another tool in your arsenal, and one that you ignore at the peril of your career.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. Check out the archive page for Jones’ series, “SQL Server for the Reluctant DBA.”