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.<\/p>\n
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.<\/p>\n
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?<\/p>\n
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.<\/p>\n
My thoughts could be summarised as follows<\/p>\n
Why scripting?<\/p>\n
Why Python?<\/p>\n
Why PowerShell?<\/p>\n
So, I’ve started translating my scripts to PowerShell in an effort to learn it and see how it compares to Python<\/p>\n
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.<\/p>\n
Nothing earth shattering here,\u00a0but I hope that you will find them useful. They are<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
The Python scripts demonstrated will work with either ActivePython 2.4 or 2.5\u00a0from ActiveState.com<\/a>. It’s likely these scripts will work with IronPython also<\/a> but I have yet to verify it.<\/p>\n The results written to a text file are…<\/p>\n CS Name: AT-RISCSQL143 CS Name: AT-RISCDCC15N01 (Our patches are pushed by Marimba which doesn’t typically set a lot of these values in our environment.)<\/em><\/p>\n foreach ($svr in get-content “C:\\ MyDir\\AFewServers.txt<\/a>“) produces…<\/p>\n AT-RISCSQL143 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.\u00a0You can quickly modify\u00a0this\u00a0initial Python template script to accomplish other tasks.<\/p>\n Using Python, I just simply run master..xp_fixeddrives against a list of servers and format the output.<\/p>\n import\u00a0getpass,string,sys,win32com.client #ListOfServers=’c:\\\\MyDir\\\\AFewServers.txt’ uid\u00a0=\u00a0‘rdameron’<\/p>\n #\u00a0take\u00a0your\u00a0pick:\u00a0HTML\u00a0or\u00a0Excel htmfile.write(‘<TITLE>SQL\u00a0Server\u00a0Space\u00a0Report<\/TITLE>\\n’)<\/p>\n for\u00a0line\u00a0in\u00a0open(ListOfServers,’r’).readlines(): \u00a0\u00a0\u00a0\u00a0\u00a0adoConn\u00a0=\u00a0win32com.client.Dispatch(‘ADODB.Connection’)<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0connect\u00a0 =\u00a0“Provider=SQLOLEDB.1;Data\u00a0Source=%s;Initial\u00a0Catalog=master;Integrated\u00a0Security=SSPI;”\u00a0%\u00a0(svr)<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0sql\u00a0=\u00a0‘<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SET\u00a0NOCOUNT\u00a0ON<\/p>\n EXEC\u00a0master.dbo.xp_fixeddrives<\/p>\n ‘<\/p>\n \u00a0\u00a0\u00a0\u00a0\u00a0adoConn.Open(connect) \u00a0\u00a0\u00a0\u00a0\u00a0while\u00a0not\u00a0query[0].EOF: htmfile.close() Now, here are two ways to check free space with PowerShell…<\/p>\n #1 Output as HTML<\/p>\n 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}<\/p>\n #2 Output as table<\/p>\n foreach ($svr in get-content “C:\\MyDir\\AFewServers.txt<\/a>“){ import\u00a0string,sys,win32com.client \n#\u00a0Python\u00a0automatically\u00a0stores\u00a0the\u00a0command-line\u00a0arguments\u00a0as\u00a0a\u00a0list\u00a0of\u00a0strings\u00a0in\u00a0the\u00a0argv\u00a0variable\u00a0of\u00a0the\u00a0sys\u00a0module. #\u00a0Accept\u00a0command-line\u00a0argument\u00a0for\u00a0name\u00a0of\u00a0database\u00a0to\u00a0search\u00a0for. #ListOfServers=’c:\\\\MyDir\\\\AllServers.txt’ txtfile\u00a0=\u00a0open(‘C:\\\\MyDir\\\\FindDB.txt’,’w’) for\u00a0line\u00a0in \u00a0open(ListOfServers,’r’).readlines():Listing installed hot fixes, i.e. DST patch<\/h3>\n
Python:<\/h4>\n
\nfrom\u00a0win32com.client\u00a0import\u00a0DispatchBaseClass
\nListOfServers=’c:\\\\MyDir\\\\AFewServers.txt’
\ntxtfile\u00a0=\u00a0open(‘C:\\\\MyDir\\\\DSTPatched.txt’,’w’)
\nfor\u00a0line\u00a0in\u00a0open(ListOfServers,’r’).readlines():
\n\u00a0servers\u00a0=\u00a0string.split(string.strip(line),’\\n’)
\n\u00a0svr=servers[0]
\n\u00a0print\u00a0svr
\n\u00a0objWMIService\u00a0=\u00a0win32com.client.Dispatch(“WbemScripting.SWbemLocator”)
\n\u00a0objSWbemServices\u00a0=\u00a0objWMIService.ConnectServer(svr,”root\\cimv2″)
\n\u00a0colItems\u00a0=\u00a0objSWbemServices.ExecQuery(“Select\u00a0*\u00a0from
\nWin32_QuickFixEngineering\u00a0Where\u00a0ServicePackInEffect\u00a0=\u00a0‘KB928388′”)
\n\u00a0for\u00a0objItem \u00a0in\u00a0colItems:
\n\u00a0\u00a0txtfile.write\u00a0(‘CS\u00a0Name:\u00a0‘\u00a0+\u00a0str(objItem.CSName)\u00a0+\u00a0‘\\n’)
\n\u00a0\u00a0txtfile.write\u00a0(‘Service\u00a0Pack\u00a0In\u00a0Effect:\u00a0‘\u00a0+
\nstr(objItem.ServicePackInEffect)\u00a0+\u00a0‘\\n’)
\n\u00a0\u00a0txtfile.write\u00a0(‘\\n’)
\ntxtfile.close<\/div>\n
\nService Pack In Effect: KB928388<\/p>\n
\nService Pack In Effect: KB928388<\/p>\nPowerShell:<\/h4>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0 { $svr; get-wmiobject Win32_QuickFixEngineering | findstr KB928388 }<\/p>\n<\/div>\n
\nServicePackInEffect : KB928388
\nDescription : Hotfix for Windows XP (KB928388)<\/p>\nChecking free space on drives<\/h3>\n
Python:<\/h4>\n
\n#\u00a0
\n#\u00a0A\u00a0script\u00a0to\u00a0check\u00a0disk\u00a0space\u00a0on\u00a0all\u00a0database\u00a0servers.\u00a0\u00a0<\/p>\n
\nfrom\u00a0win32com.client\u00a0import\u00a0DispatchBaseClass<\/p>\n
\nListOfServers=’c:\\\\MyDir\\\\AllServers.txt’<\/p>\n
\n#htmfile\u00a0=\u00a0open(‘c:\\\\MyDir\\\\SQLChkSpaceLog.htm’,’w’)
\nhtmfile\u00a0=\u00a0open(‘c:\\\\MyDir\\\\SQLChkSpaceLog.xls’,’w’)<\/p>\n
\n\u00a0\u00a0if\u00a0line[0]<>’#’:
\n\u00a0\u00a0\u00a0\u00a0\u00a0servers\u00a0=\u00a0string.split(string.strip(line),’\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0svr=servers [0]
\n\u00a0\u00a0\u00a0\u00a0\u00a0print\u00a0svr
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TABLE\u00a0WIDTH=100%\u00a0CELLPADDING=2\u00a0BORDER=2>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TR>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0BGCOLOR=aqua\u00a0ALIGN=CENTER\u00a0VALIGN=top\u00a0WIDTH=20%><B><FONT\u00a0FACE=”ARIAL”\u00a0SIZE=2>’\u00a0+\u00a0svr\u00a0+\u00a0‘<\/FONT><\/B><\/TD>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0BGCOLOR=aqua\u00a0ALIGN=CENTER\u00a0VALIGN=top\u00a0WIDTH=80%><B><FONT\u00a0FACE=”ARIAL”\u00a0SIZE=2>Free\u00a0Space\u00a0Report<\/FONT><\/B><\/TD>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<\/TR>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0BGCOLOR=aqua\u00a0ALIGN=LEFT\u00a0VALIGN=top\u00a0WIDTH=10%><B><FONT\u00a0FACE=”ARIAL”\u00a0SIZE=2>Drive<\/FONT><\/B><\/TD>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0BGCOLOR=aqua\u00a0ALIGN=LEFT\u00a0VALIGN=top\u00a0WIDTH=20%><B><FONT\u00a0FACE=”ARIAL”\u00a0SIZE=2>MB\u00a0Free<\/FONT><\/B><\/TD>\\n’)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<\/TR>\\n’)<\/p>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0query\u00a0=\u00a0adoConn.Execute(sql)<\/p>\n
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0drive=query[0].Fields(0).Value
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0free=query[0].Fields(1).Value
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TR>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0VALIGN=top><FONT\u00a0FACE=”COURIER”\u00a0SIZE=2>%s<\/FONT><\/TD>\\n’\u00a0%\u00a0(drive))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<TD\u00a0VALIGN=top><FONT\u00a0FACE=”COURIER”\u00a0SIZE=2>%s<\/FONT><\/TD>\\n’\u00a0%\u00a0(free))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<\/TR>\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0query [0].MoveNext()
\n\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0htmfile.write(‘<\/TABLE>\\n’)<\/p>\n
\nadoConn\u00a0=\u00a0None<\/p>\n<\/div>\nPowerShell<\/h4>\n
\n\u00a0\u00a0\u00a0 $dt = new-object “System.Data.DataTable”
\n\u00a0\u00a0\u00a0 $cn = new-object System.Data.SqlClient.SqlConnection\u00a0>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 “server=$svr;database=master;Integrated Security=sspi”\u00a0
\n\u00a0\u00a0\u00a0 $cn.Open()
\n\u00a0\u00a0\u00a0 $sql = $cn.CreateCommand()
\n\u00a0\u00a0\u00a0 $svr
\n\u00a0\u00a0\u00a0 $sql.CommandText = “EXEC master.dbo.xp_fixeddrives”
\n\u00a0\u00a0\u00a0 $rdr = $sql.ExecuteReader()
\n\u00a0\u00a0\u00a0 $dt.Load($rdr)
\n\u00a0\u00a0\u00a0 $cn.Close()
\n\u00a0\u00a0\u00a0 $dt | Format-Table -autosize
\n}<\/p>\n<\/div>\nFinding a database across multiple servers\u00a0<\/h3>\n
Python<\/h4>\n
\n#\u00a0Also,\u00a0demonstrates\u00a0passing\u00a0arguments\u00a0from\u00a0the\u00a0command\u00a0line\u00a0and\u00a0error\u00a0handling
\n#\u00a0Writes\u00a0output\u00a0to\u00a0a\u00a0text\u00a0file.
\n#\u00a0
\n#\u00a0Usage:\u00a0finddb.py\u00a0DatabaseName<\/p>\n
\nfrom\u00a0win32com.client\u00a0import\u00a0DispatchBaseClass<\/p>\n
\n#\u00a0If\u00a0you\u00a0want\u00a0to\u00a0see\u00a0the\u00a0list\u00a0of\u00a0command-line\u00a0arguments,\u00a0remove\u00a0the\u00a0#\u00a0in\u00a0column\u00a01\u00a0to\u00a0uncomment\u00a0the\u00a0line\u00a0–\u00a0print\u00a0sys.argv.
\n#\u00a0sys.argv[0]\u00a0always\u00a0contains\u00a0the\u00a0name\u00a0of\u00a0the\u00a0script.
\n#print\u00a0sys.argv\u00a0\u00a0<\/p>\n
\ndbname\u00a0=\u00a0sys.argv[1]<\/p>\n
\nListOfServers=’c:\\\\\u00a0MyDir\\\\AFewServers.txt’<\/p>\n
\ntxtfile.write(‘Looking\u00a0for\u00a0database:\u00a0\u00a0‘+\u00a0dbname\u00a0+\u00a0‘\\n’)
\ntxtfile.write(‘\\n’)<\/p>\n
\n\u00a0\u00a0if\u00a0line[0]<>’#’:
\n\u00a0\u00a0\u00a0\u00a0\u00a0servers\u00a0=\u00a0string.split(string.strip(line),’,’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0svr=servers[0]
\n\u00a0\u00a0\u00a0\u00a0\u00a0print\u00a0svr
\n\u00a0\u00a0\u00a0\u00a0\u00a0sql\u00a0=\u00a0None
\n\u00a0\u00a0\u00a0\u00a0\u00a0txtfile.write(svr\u00a0+\u00a0‘:\u00a0\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0adoConn\u00a0=\u00a0win32com.client.Dispatch(‘ADODB.Connection’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0connect\u00a0=\u00a0“Provider=SQLOLEDB.1;Data\u00a0Source=%s;Initial\u00a0Catalog=UMRdb;Integrated\u00a0Security=SSPI;”\u00a0%\u00a0(svr)
\n\u00a0\u00a0\u00a0\u00a0\u00a0sql\u00a0=\u00a0“SELECT\u00a0name\u00a0FROM\u00a0master..sysdatabases\u00a0WHERE\u00a0name\u00a0=\u00a0‘”\u00a0+\u00a0dbname\u00a0+\u00a0“‘”
\n\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0try:\u00a0\u00a0\u00a0#\u00a0Python\u00a0uses\u00a0‘try\u00a0–\u00a0except’\u00a0to\u00a0do\u00a0error\u00a0handling
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0adoConn.Open( connect)
\n\u00a0\u00a0\u00a0\u00a0\u00a0except:\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0txtfile.write(‘\\t’\u00a0+\u00a0“Oops,\u00a0I\u00a0wasn’t\u00a0able\u00a0to\u00a0connect\u00a0to\u00a0”\u00a0+\u00a0svr\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +\u00a0“.\u00a0\u00a0Make\u00a0sure\u00a0the\u00a0server\u00a0name\u00a0is\u00a0correct.\u00a0\\n”)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0continue
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0qry\u00a0=\u00a0adoConn.Execute(sql)
\n\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0if\u00a0qry[0].EOF:\u00a0\u00a0#database\u00a0was\u00a0not\u00a0found\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0txtfile.write(‘\\t’\u00a0+\u00a0‘Not\u00a0here!’\u00a0+\u00a0‘\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0while\u00a0not\u00a0qry[0].EOF:\u00a0\u00a0#database\u00a0was\u00a0found
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0db=qry[0].Fields(0).Value
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0txtfile.write(‘\\t’\u00a0+\u00a0‘Found\u00a0it!\u00a0‘)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0txtfile.write(‘\\t’\u00a0+\u00a0db\u00a0+\u00a0‘\\n’)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 qry[0].MoveNext()
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\ntxtfile.close()
\nadoConn\u00a0=\u00a0None<\/p>\n<\/div>\nPowerShell:<\/h4>\n