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
- Listing installed hot fixes, i.e. DST patch
- Checking free space on drives.
- Finding a database across multiple servers
- 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:
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:
#
# 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
# 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
Load comments