Comparing Python and PowerShell DBA Scripting

Comments 15

Share to social media

I became interested in scripting about a year or two ago when I landed a DBA job in a larger shop that was primarily focused on production support and Database Change Management. I was assigned several projects, each of which had at least three servers, Dev, QA, and Prod.

It quickly became apparent to me that keeping up with all the typical daily DBA tasks would require some automation. I had compiled an extensive set of SQL scripts to help with my daily tasks and I had dabbled with VBScript but it didn’t quite get my juices flowing. So, I continued searching, trying Perl and then Python, which I had tinkered with at home on a Linux box.

I ended up deciding on Python, mostly because of its clean syntax which makes it really easy to read. An article entitled “Python for Oracle Geeks” was also an influence. All DBAs are busy, so I was looking for a language that I could be productive with in a minimum amount of time. I am by no means an expert at Python or PowerShell. I am a full-time DBA, remember?

So, I’ve gotten to the point where I’ve automated several mundane DBA tasks and was pretty happy with my progress, when along comes PowerShell from Microsoft. I had heard about Monad but didn’t really pay it much attention because I was pretty satisfied with Python. Then, I sat for Gert Draper’s presentation on PowerShell at the Spring 2007 SQL Connections Conference in Orlando, Florida. I was very impressed so I bought Bruce Payette’s PowerShell book and dove in.

My thoughts could be summarised as follows

Why scripting?

  • Automate repetitive tasks
  • Query multiple servers for information in one script, run at the command line and returning the results in a single pass as text, HTML, or XLS.

Why Python?

  • Easy to read.
  • No comic cursing, parentheses, or braces.
  • Easy to learn
  • Plenty of documentation on the Internet to help a newbie
  • Available across multiple operating systems.
  • A general purpose programming language used by the likes of Amazon.com, Google, and Industrial Light and Magic.

Why PowerShell?

  • Finally, a shell scripting environment for Microsoft Windows.
  • Easier to get to the MS internals?
  • Less code to complete the same task? Maybe.
  • Learn about .Net, WMI, and the Windows Internals as you learn PowerShell
  • Future integration into SQL Server.

So, I’ve started translating my scripts to PowerShell in an effort to learn it and see how it compares to Python

The Examples

All of the examples to follow are simple ones that a full-time DBA has hacked together to try and ease the burden of the daily DBA chores.

Nothing earth shattering here, but I hope that you will find them useful. They are

  1. Listing installed hot fixes, i.e. DST patch
  2. Checking free space on drives.
  3. Finding a database across multiple servers
  4. Checking the version of SQL Server installed

I’ll show the Python code first then the equivalent PowerShell for each task along with appropriate comments. I’ll also demonstrate how to output the results as HTML, text, or Excel spreadsheet.

This past Spring, we had to make sure that the DST patch was installed on all of our database servers. We have approximately 300 of them.

For most of them, we could determine from the following script that the patch was installed. There were some special cases but this script saved a lot of time.

The basic idea for all the examples is to loop through a list of servers contained in a text file, connect to each server, run the query, and write the results out in the desired format.

The Python scripts demonstrated will work with either ActivePython 2.4 or 2.5 from ActiveState.com. It’s likely these scripts will work with IronPython also but I have yet to verify it.

Listing installed hot fixes, i.e. DST patch

Python:

import string,sys,win32com.client
from win32com.client import DispatchBaseClass
ListOfServers=’c:\\MyDir\\AFewServers.txt’
txtfile = open(‘C:\\MyDir\\DSTPatched.txt’,’w’)
for line in open(ListOfServers,’r’).readlines():
 servers = string.split(string.strip(line),’\n’)
 svr=servers[0]
 print svr
 objWMIService = win32com.client.Dispatch(“WbemScripting.SWbemLocator”)
 objSWbemServices = objWMIService.ConnectServer(svr,”root\cimv2″)
 colItems = objSWbemServices.ExecQuery(“Select * from
Win32_QuickFixEngineering Where ServicePackInEffect = ‘KB928388′”)
 for objItem  in colItems:
  txtfile.write (‘CS Name: ‘ + str(objItem.CSName) + ‘\n’)
  txtfile.write (‘Service Pack In Effect: ‘ +
str(objItem.ServicePackInEffect) + ‘\n’)
  txtfile.write (‘\n’)
txtfile.close

The results written to a text file are…

CS Name: AT-RISCSQL143
Service Pack In Effect: KB928388

CS Name: AT-RISCDCC15N01
Service Pack In Effect: KB928388

(Our patches are pushed by Marimba which doesn’t typically set a lot of these values in our environment.)

PowerShell:

foreach ($svr in get-content “C:\ MyDir\AFewServers.txt“)
      { $svr; get-wmiobject Win32_QuickFixEngineering | findstr KB928388 }

produces…

AT-RISCSQL143
ServicePackInEffect : KB928388
Description : Hotfix for Windows XP (KB928388)

Obviously, the most compelling concept here is that it took significantly fewer lines to produce the desired result. In defense of Python, the code that is needed to access the server, read input, and write output requires minimal changes once you’ve written your first Python script. You can quickly modify this initial Python template script to accomplish other tasks.

Checking free space on drives

Using Python, I just simply run master..xp_fixeddrives against a list of servers and format the output.

Python:

# CheckDiskSpace.py

# A script to check disk space on all database servers.  

import getpass,string,sys,win32com.client
from win32com.client import DispatchBaseClass

#ListOfServers=’c:\\MyDir\\AFewServers.txt’
ListOfServers=’c:\\MyDir\\AllServers.txt’

uid = ‘rdameron’

# take your pick: HTML or Excel
#htmfile = open(‘c:\\MyDir\\SQLChkSpaceLog.htm’,’w’)
htmfile = open(‘c:\\MyDir\\SQLChkSpaceLog.xls’,’w’)

htmfile.write(‘<TITLE>SQL Server Space Report</TITLE>\n’)

for line in open(ListOfServers,’r’).readlines():
  if line[0]<>’#’:
     servers = string.split(string.strip(line),’\n’)
     svr=servers [0]
     print svr
     htmfile.write(‘<TABLE WIDTH=100% CELLPADDING=2 BORDER=2>\n’)
     htmfile.write(‘<TR>\n’)
     htmfile.write(‘<TD BGCOLOR=aqua ALIGN=CENTER VALIGN=top WIDTH=20%><B><FONT FACE=”ARIAL” SIZE=2>’ + svr + ‘</FONT></B></TD>\n’)
     htmfile.write(‘<TD BGCOLOR=aqua ALIGN=CENTER VALIGN=top WIDTH=80%><B><FONT FACE=”ARIAL” SIZE=2>Free Space Report</FONT></B></TD>\n’)
     htmfile.write(‘</TR>\n’)
     htmfile.write(‘<TD BGCOLOR=aqua ALIGN=LEFT VALIGN=top WIDTH=10%><B><FONT FACE=”ARIAL” SIZE=2>Drive</FONT></B></TD>\n’)
     htmfile.write(‘<TD BGCOLOR=aqua ALIGN=LEFT VALIGN=top WIDTH=20%><B><FONT FACE=”ARIAL” SIZE=2>MB Free</FONT></B></TD>\n’)            
     htmfile.write(‘</TR>\n’)

     adoConn = win32com.client.Dispatch(‘ADODB.Connection’)

     connect  = “Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=master;Integrated Security=SSPI;” % (svr)

     sql = ‘

        SET NOCOUNT ON

EXEC master.dbo.xp_fixeddrives

     adoConn.Open(connect)
     
     query = adoConn.Execute(sql)

     while not query[0].EOF:
        drive=query[0].Fields(0).Value
        free=query[0].Fields(1).Value
        htmfile.write(‘<TR>\n’)
        htmfile.write(‘<TD VALIGN=top><FONT FACE=”COURIER” SIZE=2>%s</FONT></TD>\n’ % (drive))
        htmfile.write(‘<TD VALIGN=top><FONT FACE=”COURIER” SIZE=2>%s</FONT></TD>\n’ % (free))
        htmfile.write(‘</TR>\n’)
        query [0].MoveNext()
     
     htmfile.write(‘</TABLE>\n’)

htmfile.close()
adoConn = None

Now, here are two ways to check free space with PowerShell…

PowerShell

#1 Output as HTML

foreach ($svr in get-content “C:\MyDir\AFewServers.txt”) { $svr; Get-WmiObject -Class Win32_LogicalDisk -Filter “DriveType=3” -ComputerName $svr | ConvertTo-Html -property DeviceID,FreeSpace,Size -title “Free Space” -body $svr >> c:\FreeSpace.html}

#2 Output as table

foreach ($svr in get-content “C:\MyDir\AFewServers.txt“){
    $dt = new-object “System.Data.DataTable”
    $cn = new-object System.Data.SqlClient.SqlConnection >
                 “server=$svr;database=master;Integrated Security=sspi” 
    $cn.Open()
    $sql = $cn.CreateCommand()
    $svr
    $sql.CommandText = “EXEC master.dbo.xp_fixeddrives”
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

Finding a database across multiple servers 

Python

# A script to find all occurrences of a database on multiple servers.
# Also, demonstrates passing arguments from the command line and error handling
# Writes output to a text file.

# Usage: finddb.py DatabaseName

import string,sys,win32com.client
from win32com.client import DispatchBaseClass

# Python automatically stores the command-line arguments as a list of strings in the argv variable of the sys module.
# If you want to see the list of command-line arguments, remove the # in column 1 to uncomment the line – print sys.argv.
# sys.argv[0] always contains the name of the script.
#print sys.argv  

# Accept command-line argument for name of database to search for.
dbname = sys.argv[1]

#ListOfServers=’c:\\MyDir\\AllServers.txt’
ListOfServers=’c:\\ MyDir\\AFewServers.txt’

txtfile = open(‘C:\\MyDir\\FindDB.txt’,’w’)
txtfile.write(‘Looking for database:  ‘+ dbname + ‘\n’)
txtfile.write(‘\n’)

for line in  open(ListOfServers,’r’).readlines():
  if line[0]<>’#’:
     servers = string.split(string.strip(line),’,’)
     svr=servers[0]
     print svr
     sql = None
     txtfile.write(svr + ‘: \n’)
     adoConn = win32com.client.Dispatch(‘ADODB.Connection’)
     connect = “Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=UMRdb;Integrated Security=SSPI;” % (svr)
     sql = “SELECT name FROM master..sysdatabases WHERE name = ‘” + dbname + “‘”
     
     try:   # Python uses ‘try – except’ to do error handling
        adoConn.Open( connect)
     except: 
        txtfile.write(‘\t’ + “Oops, I wasn’t able to connect to ” + svr 
                      + “.  Make sure the server name is correct. \n”)
        continue
        
     qry = adoConn.Execute(sql)
     
     if qry[0].EOF:  #database was not found 
        txtfile.write(‘\t’ + ‘Not here!’ + ‘\n’)
        
     while not qry[0].EOF:  #database was found
        db=qry[0].Fields(0).Value
        txtfile.write(‘\t’ + ‘Found it! ‘)
        txtfile.write(‘\t’ + db + ‘\n’)
         qry[0].MoveNext()
        
txtfile.close()
adoConn = None

PowerShell:

foreach ($svr in get-content “C:\MyDir\AFewServers.txt“){
    $dt = new-object “System.Data.DataTable”
    $cn = new-object System.Data.SqlClient.SqlConnection    “server=$svr;database=master;Integrated Security=sspi”
    $cn.Open()
    $sql = $cn.CreateCommand()
    #$svr
    $sql.CommandText = “SELECT @@SERVERNAME AS Server_Name, name FROM master..sysdatabases WHERE name = ‘DatabaseYourLookingFor'”
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table
}

Checking the version of SQL Server installed

Python

#
# A script to check the SQL Server version and service pack on multiple servers
# Writes output to a text file.
#
import string,sys,win32com.client
from win32com.client import DispatchBaseClass

ListOfServers=’c:\\MyDir\\AllServers.txt’
uid = ‘rdameron’
txtfile = open(‘c:\\MyDir\\SQLVersions.txt’,’w’)

for line in open(ListOfServers,’r’).readlines():
  if line[0]<>’#’:
     servers = string.split(string.strip(line),’\n’)
     svr=servers[0]
     print svr
     sql = None
     txtfile.write(‘\n’)
     txtfile.write( svr + ‘:  \n’)
     adoConn = win32com.client.Dispatch(‘ADODB.Connection’)
     connect = “Provider=SQLOLEDB.1;Data Source=%s;Initial Catalog=UMRdb;Integrated Security=SSPI;” % (svr)
     sql = “SELECT SERVERPROPERTY(‘ProductVersion’) AS Version, SERVERPROPERTY(‘ProductLevel’) as SP”
     #print sql
     adoConn.Open(connect)
     alog = adoConn.Execute(sql)
     while not alog[0].EOF:
        version=alog[0].Fields(0).Value
        sp=alog[0].Fields(1).Value
        txtfile.write(‘Version: ‘ +  version + ‘\n’)
        txtfile.write(‘ServicePack: ‘ + sp + ‘ \n’)
        alog[0].MoveNext()
txtfile.close()
adoConn = None

PowerShell:

 foreach ($svr in get-content “C:\MyDir\AFewServers.txt“){
    $dt = new-object “System.Data.DataTable”
    $cn = new-object System.Data.SqlClient.SqlConnection “server=$svr;database=master;Integrated Security=sspi”
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = “SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY(‘ProductVersion’) AS Version, SERVERPROPERTY(‘ProductLevel’) as SP”
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

Conclusions:

I’m pretty excited about what PowerShell offers. I’m beginning to think it’s easier to get things done with it and it looks like you can get more done with fewer lines of code using PowerShell. I found Python a bit easier to read initially but I’m getting used to the PowerShell syntax with the help of Bruce Payette’s PowerShell book and several PowerShell blogs and forum posts. Plenty of support has built up around PowerShell that helped me to write this article both from Microsoft and PowerShell users. One thing I won’t forget is Gert Drapers saying during his conference presentation that prior to PowerShell he used Perl for his admin scripts. It obvious from Bruce Payette’s book that Microsoft did take a significant amount of inspiration from languages like Python and Perl to build PowerShell. No matter which scripting language you choose, I think to be really effective in a large environment you must learn to script. I’m hooked on PowerShell because of what it can do to make my life simpler in our Microsoft environment but also the exposure to .Net and WMI that it provides for a DBA

Additional Reading:

“Windows PowerShell in Action” by Bruce Payette

“Dive Into Python” by Mark Pilgrim

code prettified by the Simple-Talk Prettifier

About the author

Ron Dameron

See Profile

Ronald Dameron is a Senior Database Administrator for the largest life insurer in the United States. With almost 20 years of IT experience he has worked on a wide range of environments to include: mainframes, Oracle on Amdahl's Unix variant UTS, Sybase on AIX and Microsoft SQL Server since version 7. He is most comfortable with Microsoft SQL Server as a database developer and DBA. He is currently exploring how PowerShell can simplify his life as a DBA and the new features of SQL Server 2005. In his spare time, he enjoys rescucitating old PC hardware with Ubuntu Linux. To stay sane during his busy days at work, he practices the Yang style of Tai Chi or Power Yoga every morning. He feeds the hungry masses at his house with his Weber Kettle grill or the recipes of Rachel Ray.

Ron Dameron's contributions