PowerShell For SQL Server
Yesterday I was looking in Google for syntax on a PowerShell commandlet and I came across a rant from a DBA complaining that they now had to learn PowerShell on top of everything else a DBA does. I had to take a look, and I found a nice gold nugget I had missed. With the release of SQL Server 2008 one of the enhancements they made to PowerShell was to create a new SQL Server PowerShell.
Included with the release of SQL Server 2008 there is an enhancement to PowerShell specific to SQL Server management. In fact, it has it’s own PowerShell prompt. You access the SQL Server PowerShell mode by starting PowerShell and entering the command SQLPS at the PowerShell prompt.
Once you are in the SQL Server PowerShell session you can perform most of the capabilities you have available in SQL Server Management Studio. What is this all about?
Microsoft has had methods to manipulate SQL Server for decades. Here is a bit of a history of Microsoft database management tools.
| Tool | Description |
| ISQL | DOS based SQL command tool with similar capabilities of Query Analyzer, able to run both scripts or interactive queries. Returned query results to console or file…limited formatting. Communicates with Database using Native TDS SQL Server Protocol. This was the tool that was originally ported from Sybase. |
| SQLDMO | SQL Data Management Objects released with SQL Server 6. Consists of COM Based Objects wrapping the capabilities of SQL Server. Provides objects to manage server Instances, Databases, Database Objects, Backup and Restore and other assorted features. SQL Server Enterprise Manager was converted to use SQLDMO when this was released. Continued through SQL Server 2000. |
| SQLDMO Custom Programs | Custom programs could be written utilizing SQLDMO to manage a database. Programs could be written with VBScript, or any language supporting COM. Also supported through DTS. |
| OSQL | Same features as ISQL. Server communication is through SQLDMO. ISQL was still available. |
| SQLSMO | SQL Server Management Objects released with SQL Server 2005, Dot Net managed code object wrappers for managing databases. Has same kind of features and similarity to SQLDMO. Has additional features since SQL Server has many more features. |
| SQLSMO Custom Programs | Custom Programs can be written in any language that supports SQL SMO. PowerShell can be used as a scripting language to manage a database directly using SQLSMO. You can also write programs in any Dot Net language such as C#, VB.Net or even ASP.Net. SQL Server Integration Services uses SQL SMO as well. |
| SQLCMD | Same features as ISQL. Communication is now through managed code objects SQLSMO |
| PowerShell for SQL Server | Released with SQL Server 2008. This includes new CommandLets and a PowerShell session that has wrappers around SQLSMO and SQLCMD. This simplifies the methods and object models of SQLSMO. |
Integrating these capabilities in PowerShell you can write a nice program that will go to multiple servers and perform a task on every database mounted in that instance. Since you are in PowerShell, you can run a Commandlet discovering and returning a list of SQL Server Instances so that you don’t even have to know where they are. The key is, you now have a programming environment that allows you to work with Network, WMI, File System, SQL Server and Database objects all in a single environment.
Previously, the only way you could do something of this nature was to write a program in VB (for SQL DMO) and now Dot Net languages. Now you can write a program in a tool similar to a batch file, or even run commands interactively from the PowerShell command line.
With the release of the PowerShell for SQL Server it gets even easier. You now have a command line tool written specifically for managing your SQL Servers in a command environment. It supports Script Files just like PowerShell. Go check it out. Here are a couple URLs to get you started.
technet.microsoft.com/en-us/magazine/dd320612.aspx
technet.microsoft.com/en-us/magazine/gg313741.aspx
Be sure to drop comments to me at btaylor@sswug.org. I’d be interested to know how you are using PowerShell, how you manage security for your scripts, and what tips, techniques, script libraries, or Online content that you find useful.
Cheers,
Ben