Brent Ozar posted the results of poll he conducted on Powershell adoption among database professionals in a post entitled, Powershell poll results. Of particular note, out of the 100 polled, which I assume are mostly database professionals only about 20% use Powershell. I wonder if the results of the poll are really that different when comparing DBAs with other system administration groups (Web, Exchange, Server, etc.). I mean Windows administrators really have never been heavy scripters. There is of course, a big opportunity for Powershell to change this. But, it wasn’t so much the poll results which caught attention as it was the statements about Powershell benefits. You see I’m working on a Powershell presentation for SQL Server user groups and one thing I noticed is that we (DBAs who use Powershell) haven’t done a great job of articulating the benefits of Powershell to our fellow SQL Server DBAs. I made a previous incomplete attempt to do so in my post, Is PowerShell necessary for a DBA?.
DBAs are niche group of administrators and inevitably the Powershell discussion boils down to one question — “Why should I learn Powershell when I have a pretty good set of tools in T-SQL, SSMS and SSIS”. Notice the question isn’t about VBScript vs. Powershell because DBAs like most Windows administration groups, have not used VBScript extensively. They do however, make heavy use of T-SQL. The use of a T-SQL as scripting language with near 100% adoption is what sets DBAs apart from their web, server or Exchange administrator counterparts. So the argument is really one of Powershell vs. T-SQL (and maybe SSIS), with that mind here is my attempt to better define the benefits of Powershell to DBAs:
- Multi-server Automation — Using Powershell you can do several things across multiple servers: execute a query, retrieve properties or even update configurations. Many of my previous blog posts retrieve a list of SQL Servers from either a SQL table or text file and collect various information.
- Retrieving Poperties is Easier — Powershell makes getting properties easier than T-SQL or SSMS. For example this one line command exposes 97 properties of a SQL instance. It would take many SQL queries or lines of C# code to accomplish the same thing.
$server = new-object (”Microsoft.SqlServer.Management.Smo.Server”) ‘Z002SQL2K8′
- Non-SQL tasks — Powershell provides a better method for doing tasks outside of the SQL space.
DBAs need to do things like check disk space, hotfixes, and delete files. These tasks are easy to accomplish with Powershell, but are impossible or ugly to do with T-SQL (hint: if you’re using xp_cmdshell, you probably should look into Powershell). Here’s an example getting disk space
get-wmiobject win32_logicaldisk -computername ‘Z002′
- Simple ETL — While SSIS is great at complex ETL, Powershell makes it easy to to automate simple data loads. A Powershell script with a call to BULK INSERT or Data.SqlClient.SqlBulkCopy may be all that is required to load an CSV file. See SQLServerCentral Article on Importing Powershell Output into SQL Server for several examples.
- The non-DBA DBA — A DBA is unlikely to use Powershell to do database backups or create tables, but for administrators thrust into a DBA role that must support databases yet are not DBAs, Powershell provides a common scripting language. Granted this really isn’t of a benefit to a DBA, but probably is for a administrator who doesn’t know T-SQL. See Dan Jones post on this subject:
- Toolsmithing – The ability to quickly create useful tools that provide functionality you would normally have to purchase is especially important with the current economic conditions. Many IT departments are being told to only make purchases directly tied to business driven project. Powershell makes it very easy for a system administrator who may not have a developer background to quickly fashion useful tools. For examples see my previous blog postings on Build your own SQL Dependency viewer and Disk, database and table space charts.
Getting started with Powershell SQL tasks can be a bit of a learning curve, if you’re looking at using SQL + Powershell you may want to check out my Codeplex project SQL Server Powershell Extensions which provides over 100 functions for common SQL administration tasks.
Can you think of any additional items or am I way off on my initial assumption (Powershell vs. T-SQL)?