I have to admit that the issue of whether or not to use PowerShell to manage SQL Server is something I have pondered ever since Microsoft first integrated PowerShell with SQL Server 2008. The initial PowerShell integration with SQL Server 2008 used a mini-shell that was of limited use. Then, with SQL Server 2012, Microsoft provided a fully capable PowerShell environment, making PowerShell more useful by letting you add other snap-ins. However, scripted management for SQL Server is nothing new. T-SQL has been an integral management tool for SQL Server since the beginning.
Why should you use PowerShell to manage SQL Server? To help answer that question, I checked out some of the TechNet articles in which Microsoft illustrates the "amazing power" of being able to list instances and enumerable database objects—all with a few lines of code. According to these articles, you can automate operations by running scripts and even execute T-SQL scripts. Huh? I feel bad about pointing this out, but SQL Server Management Studio (SSMS) enumerates SQL Server objects almost instantly with no code, and T-SQL stored procedures and batches already provide automation. There has to be something more to encourage SQL Server professionals to use PowerShell.
Pros and Cons
What are the pros and cons to using PowerShell to manage SQL Server? I think we probably know the cons best. PowerShell is foreign to most SQL Server professionals and there's a substantial learning curve if you don't have a developer background. Because I have a developer background, PowerShell has always come naturally to me—but at the same time, I recognize that PowerShell has all of the ingredients that make it difficult for non-developers to learn. Plus, SQL Server already possesses a scripted management environment with T-SQL—one that most SQL Server professionals are aware of and are proficient at using. So, PowerShell potentially adds more complexity into the day-to-day operating environment.
Learn more: SQL Server and PowerShell FAQs
However, PowerShell for SQL Server does have real pros, as well. On the pro side, PowerShell is becoming more central to the Windows management model and can provide a common framework for managing all the different servers in a Microsoft Windows infrastructure. PowerShell can manage Windows, Exchange, SharePoint, and SQL Server. PowerShell is also an advanced .NET-based object oriented language that is arguably the most powerful scripting environment there is. Unlike T-SQL, PowerShell can access resources that are outside of SQL Server. PowerShell is also capable of performing advanced math and a multitude of other functions that are supported in the .NET Framework—far beyond T-SQL. PowerShell is also capable of running T-SQL queries using the Invoke-Sqlcmd cmdlet.
PowerShell Bridges SQL Server/OS gap
Although I don't see any SQL Server professionals dropping T-SQL or SSMS in favor of PowerShell, I think that PowerShell is a valuable addition for managing SQL Server. PowerShell's ability to bridge the SQL Server/OS gap and access systems and network resources, both inside and outside of SQL Server, is something that T-SQL can't do. One of the things that I use PowerShell for is to run sets of SQL Server benchmarks where a driver script executes and times a set of queries from multiple clients to multiple SQL Server systems—that can't be done with just T-SQL, and PowerShell provides a far more robust platform than Windows Shell scripts.
What exactly are the PowerShell integration components for SQL Server? There are two PowerShell snap-ins for SQL Server 2008 and 2012: a SQL Server provider named sqlps and a set of SQL Server cmdlets. The sqlps PowerShell provider lets you navigate through a SQL Server instance and its objects exactly like you would navigate a standard file system. You can use the methods of properties of the different objects to manage them. The cmdlets have a verb-noun naming convention and they perform single-function commands. Some cmdlets include Add-SqlAvailabilitiyDatabase, Join-SqlAvailabilityGroup, Backup-SqlDatabase, Restore-SqlDatabase, and Invoke-Sqlcmd. At this point, the cmdlets are primarily focused on Availability Groups.
PowerShell can be a valuable SQL Server management tool, but learning it isn't always easy. For more information about PowerShell and SQL Server 2008, check out "SQL Server PowerShell Overview." Another good resource is "Understanding and Using PowerShell Support in SQL Server 2008." For some practical scripts, visit Bucky Woody's blog.
