Use PowerShell capabilities to improve your SQL Server administration

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

    Requires Free Membership to View

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.

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.”

This was first published in April 2012

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.