Powershell Backup All Databases Locally and to Amazon S3

Previously I had posted a Powershell script that would use 7Zip to compress and encrypt a folder and then send it to Amazon S3. This script could have many uses, but in my case it was designed to fulfill the need for off site storage of compressed database backups. While it did all the heavy lifting of getting the data encrypted, compressed and uploaded to S3 I left the creation of the database backup folder as an exercise for the user to complete.

I now have a working system that I would like to share in case anyone was unable to create the backup folder using Powershell. There were a few hiccups that I found, such as not being able to use the Start-Transcript method in a SQL Agent Job and having my MSDB data file triple in size after 3 months of backups, but after working out those issues the script provides a very cost effective method of creating both local and offsite database backups with 1 week or 1 month retention. Currently I have it running on a server with about 100 databases ranging in size from 7MB to 700MB with daily full backups and hourly differential backups being sent to Amazon S3 and stored with 1 month retention for about $5 a month. The total raw backup size is 2.2GB per full backup set and 100-400MB for each differential backup set, but the script gets about 10:1 compression so the daily storage on S3 is about 500MB total for all of the full and differential backups.

You will need to modify the settings in both the psBackupAllDBtoAmazonS3.ps1 and ps7ZiptoAmazonS3.ps1 files to get this to work on your server, but once you do you can sit back and let the scripts do it’s magic. You can set it to run as a scheduled task, but we chose to run it as an SQL Agent Job so that our server monitoring software would notify us if there were any issues with the script.

#Description: Powershell 2.0 script to backup all databases in MS SQL Server to the local disk and then zip and encrypt the folder using 7zip and then send it to Amazon S3 for offsite storage. #Author: Greg Bray (6/14/2010) #Website: http://codeblog.theg2.net/2010/06/powershell-backup-all-databases-locally.html #License: free for personal or commercial use under the Creative Commons Attribution 3.0 United States License. #Prerequisite: You must first install the latest version of 7Zip from http://www.7-zip.org/ (licensed under the GNU LGPL) #Prerequisite: You must also install the Amazon Web Services SDK for .NET from http://aws.amazon.com/sdkfornet/ (licensed under the Apache License, Version 2.0) #Prerequisite: You must also download the ps7ZiptoAmazonS3.ps1 from http://codeblog.theg2.net/2010/02/powershell-7-zip-amazon-s3-upload.html #Prerequisite: You must set the settings listed below as well as the settings listed in the ps7ZiptoAmazonS3.ps1 file #Recommended: I recommend using CloudBerry S3 Explorer for managing Amazon S3 files. http://cloudberrylab.com/default.aspx?page=cloudberry-explorer-amazon-s3 #Recommended: I recommend using http://PowerGui.org Script editor if you want to change or debug this script. <#Example calling from Run, a batch file, SQL Agent Job, or the task Scheduler: NOTE: may need to run "Set-ExecutionPolicy RemoteSigned" in powershell to allow unsigned local scripts to execute powershell.exe -Command "& 'c:\Backup\psBackupAllDBtoAmazonS3.ps1'" #> #Script will backup all active databases on the server except those listed on an exclusion list. #Performs Differential backup except when $bFullBackup is true (currently set to do full backup when run between 00:00 and 01:00) #Uses a 1 week local retention and either a 1 week or 1 month offsite retention on Amazon S3 depending on the definition of $S3FilePath #Offsite files will be compressed and encrypted using 7zip. Local files will not be compressed or encrypted. #There also will be a transcript log created that can be used to monitor the script. NOTE: If run using SQL Agent Job Start-Transcript will not work. $strLogfile = "c:\Backup\Backup Log.rtf" if (($host.Name -notmatch '^PowerGUI') -and ($host.name -ne "Windows PowerShell ISE Host")) {#PowerGUI and ISE Hosts do not support transcriptions yet :-( #Start-Transcript $strLogfile -Append | Out-Null #Append output to log file. Disabled as SQL Agent Jobs don't work with powershell transcripts #NOTE: Transcripts don't work with SQL Agent Jobs either... See http://stackoverflow.com/questions/2383605/powershell-transcript-is-empty-when-running-script-from-sql-agent-job-in-2005-sql } #Settings $strBackupPathBase = "c:\Backup" #Base path on server where to store backups. NOTE: Do not include trailing slash. $arrDBsToExclude = @("tempdb", "master") #DBs not included in the backup set. NOTE: Master database does not support differential backups, but will be included in full backups (see $strBackupQuery definition below) $dtStart = [DateTime]::Now #capture start date to track total time $bFullBackup = ($dtStart.Hour -eq 0) #Perform full backup instead of differential when run between 00:00 and 01:00 $strDateTimeStamp = $dtStart.ToString("yyyy-MM-dd_HHmm").ToUpper() #Timestamp appended to all backup files. NOTE: this is not the actual backup time, but when the script starts running $strBackupPath = "{0}\{2}-{1:ddd}\{1:HH}" -f $strBackupPathBase, $dtStart, $dtStart.DayOfWeek.value__ #Full path where files will be stored locally (1 week retention). If folder exists it will be deleted #See $S3FilePath and DB Connection string definitions below. Also update encryption settings in ps7ZiptoAmazonS3.ps1 Write-Host ("`r`n`r`n{0} - Starting SQL Server Differential backup with these parameters:`r`n`tBackupPath='{1}'`r`n`tTimestamp={2}`r`n`tFullBackup={3}`r`n`tExclude Databases: ${arrDBsToExclude}" -f [DateTime]::Now, $strBackupPath,$strDateTimeStamp,$bFullBackup) #Delete backup directory if it already exists. This creates a 1 week local retention for the backup files if([System.IO.Directory]::Exists($strBackupPath)){ Remove-Item $strBackupPath -Force -Recurse } New-Item $strBackupPath -type directory | Out-Null #Create directory again #Setup database connection $conn = new-object System.Data.SqlClient.SqlConnection $conn.ConnectionString = 'Data Source=localhost;Initial Catalog=master;User ID=sa;Password=SAPASSWORD' #NOTE: use ' instead of " to prevent issue with $ character if($conn.State -ne [System.Data.ConnectionState]::Open){ $conn.Open();} #Open connection if not already open #Get a list of all databases $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= "SELECT name FROM sys.databases" $sqlReader = $cmd.ExecuteReader() $lstDatabases = New-Object System.Collections.ArrayList while ($sqlReader.Read() -eq $true){ [void]$lstDatabases.Add($sqlReader.GetString(0)) #Add database name to list } $sqlReader.Close() #Backup all databases to disk $strBKType = "DIFF" $intBackups = 0 $strBackupQuery = "BACKUP DATABASE [{0}] TO DISK = '{1}' WITH DIFFERENTIAL, INIT" #0: Database name, 1: Backup file path if($bFullBackup){# Perform full backup instead of differential $strBackupQuery = "BACKUP DATABASE [{0}] TO DISK = '{1}' WITH INIT" #0: Database name, 1: Backup file path $arrDBsToExclude = @() + ($arrDBsToExclude | where {$_ -notmatch "master"}) #make sure master database is included in the full backup $strBKType = "FULL" } foreach($strDatabase in $lstDatabases){ if($arrDBsToExclude -notcontains $strDatabase){ #write-Host ("`r`n{0} - Backing up database: {1}" -f [DateTime]::Now, $strDatabase) $strFilename = "{0}\{1}_{2}_{3}.bak" -f $strBackupPath, $strDatabase, $strBKType, $strDateTimeStamp $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= $strBackupQuery -f $strDatabase, $strFilename $intBackups += 1 $intReturn = $Null $intReturn = $cmd.ExecuteNonQuery() if ($intReturn -eq $Null) { write-Host ("{0} - Database {1} backup failed ({2} of {3}). Retry after 10 seconds." -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) Start-Sleep -s 10 $intReturn = $cmd.ExecuteNonQuery() if ($intReturn -eq $Null) { write-Host ("{0} - Database {1} backup failed ({2} of {3}). Script aborted." -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) exit 2 #Quit script if there is an error } } #write-Host ("{0} - Database {1} backed finished successfully ({2} of {3})" -f [DateTime]::Now, $strDatabase,$intBackups,$lstDatabases.Count) } } #Perform basic monthly db maintinece here. Will execute on first day of the month when the full backup is created if($bFullBackup -and ($dtStart.Day -eq 0)){ #Clean up MSDB backup history logs to prevent excessive growth of MSDB. See http://technet.microsoft.com/en-us/library/ms188328.aspx write-Host ("{0} - Start server maintinence: sp_delete_backuphistory on MSDB" -f [DateTime]::Now) $cmd = new-object System.Data.SqlClient.SqlCommand $cmd.Connection=$conn $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandText= 'use msdb; Declare @date datetime; Set @date = dateadd (mm, -1, getdate()); Exec sp_delete_backuphistory @date' $Null = $cmd.ExecuteNonQuery() } if($conn.State -eq [System.Data.ConnectionState]::Open){ $conn.Close();} #Close database connection #Call powershell script to encrypt data and send it to Amazon S3. NOTE: there are other settings configured in the script file. #$S3FilePath = "DB2/{2}-{0:ddd}/Hour_{0:HH}_{1}_Backup.7z" -f $dtStart,$strBKType,$dtStart.DayOfWeek.value__ #S3 path using DB2 bucket and 1 week retention $S3FilePath = "DB2/{0:dd}/Hour_{0:HH}_{1}_Backup.7z" -f $dtStart,$strBKType #S3 path using DB2 bucket and 1 month retention & 'C:\Backup\ps7ZiptoAmazonS3.ps1' "$strBackupPath" "$S3FilePath" $dtEnd = [DateTime]::Now $tsTotal = $dtEnd.Subtract($dtStart) #Get total processing time write-Host ("`r`n`r`n{0} - Finished SQL Server Backup. Total processing time: {1}`r`n`tBackup Path:{5}`r`n`tS3 Storage Path:{7}`r`n`tFull Backup:{6}`r`n`tTotal databases backed up: {2} of {3}`r`n`t{4} Exclusions: - ${arrDBsToExclude} " -f $dtEnd,$tsTotal,$intBackups,$lstDatabases.Count,$arrDBsToExclude.Count, $strBackupPath, $bFullBackup, $S3FilePath) if (($host.Name -notmatch '^PowerGUI') -and ($host.name -ne "Windows PowerShell ISE Host")){ #Stop-Transcript #Disabled for use with SQL Job. } exit $LASTEXITCODE #Make sure to pass exitcode from ps7ZiptoAmazonS3 to host for error tracking

Lap Around .NET 4 with Scott Hanselman

Things were slow at the office today, so I used the free time to catch up on some of the recorded sessions from DevDays 2010. Scott Hanselman’s overview of the new features in .NET 4 is one of the best presentation I have seen about .NET 4 so far. How many presentations include 14,000 generations of monkeys trying to write Shakespeare? Here is the synopsis:

In this session, Scott Hanselman gives a deep and broad tour of the .NET 4 release, with a focus on making your development experience easier. See lots of demos (and very few slides) showcasing the key new features in the .NET Framework 4 including MEF, improvements in ASP.NET, threading, multi-core and parallel extensions, additions to the base classes, changes and additions to the CLR and DLR, what's new for the languages (Visual Basic and C#), and of course, what's new in Windows Presentation Foundation and System.Web. Come and see how all these new features and capabilities improve your overall .NET experience!

Enjoy!

TED Fullscreen Video Multiple Monitor Userscript: Yay!

I like to think that I can hack out JavaScript as good as any web developer, but occasionally I still fall into rookie mistakes. For instance, I totally forgot to test my last userscript for fixing Slashdot articles while zooming in anything but my default browser (Google Chrome). In theory it should have worked with Greasemonkey, but when I tried testing it today using Firefox it failed miserably. Luckily with a few code changes I was able to update the script and get it to work with both Firefox and Google Chrome as well as fix a huge bug when visiting Slashdot’s main page as a non-registered user (another thing I probably should have tested!).

Considering that it was my first attempt at a user script I am not too upset and will chalk it up to experience. Tonight my plans for the evening fell apart, so I decided to try and fix a big pet peeve of mine: no multiple monitor support for video players on certain websites. Maybe I am the only one that thinks this is a problem, but I hate it when websites have a Flash or Silverlight video player embedded onto their page and don’t have an option to enlarge the video or open it in a pop-out window. Most people do fine using the fullscreen mode built into the player, but both Silverlight and Flash fail at fullscreen with multiple monitors since they will exit fullscreen as soon as you try and click on something outside the video (for security purposes apparently). This means you can either stop multi-tasking and only watch the video or you have to settle for a small video player that is absolutely tiny on anything larger than 1024 resolution width. For instance: here is a video that a friend brought to my attention as viewed on a 1600x900 LCD TV.

That is a lot of white space going to waste, and while the fullscreen mode works I often watch videos on the LCD TV while browsing the web on a projector connected to the same computer and end up switching in and out of fullscreen mode multiple times. I’ve had this same problem before with Channel9 and fixed it using a bookmarklet, but that actually requires clicking a link in my favorites every time I loaded a video, which was less than ideal. Now that Chrome supports userscripts I am working on converting some of the bookmarklets that I have into userscripts, and since I already have a working solution for Channel9 I thought that I would try creating one for TED.com next.

So if you ever watch TED videos on a machine with multiple monitors then today is your lucky day.  This userscript will enable multiple monitor fullscreen video for any page that uses the TED.Com flash player. Videos that are embedded from YouTube currently are not supported, but YouTube already lets you increase the width of the player, open it in a pop-out window, or even use the browser’s zoom feature to resize the video so that shouldn’t be a problem. And this time I did do some basic testing in both Firefox and Chrome so hopefully it will work as advertised.

Enjoy!

Slashdot Zoom Fix: a Greasemonkey and Chrome compatible userscript

One of my top ten visited websites is probably Http://www.slashdot.org who’s headline is “News for nerds, stuff that matters”. The site usually posts over 20 stories a day covering technology, math, physics, and computer related topics, which help me keep informed and entertained. One problem that I often have is that the text size is very hard to read when using a high resolution monitor or when using the projector that I have setup on a media center computer at home. Usually this is easy to solve, since one of the 11 buttons on my mouse is mapped to the CTRL button and can be combined with the scroll wheel to zoom in and out on web pages quickly. However in this case there are boxes on the right hand side of the screen that also enlarge when the page is zoomed, causing the text width to be smaller and more difficult to read. Here is an article on Slashdot normal and zoomed:

Normal

Zoomed

The site is designed using CSS and for some reason the advertisement and “Interviews” box will get bigger when the page is zoomed, shrinking the width of the textbox significantly. If you are a registered user Slashdot will not display the ad but will still have a side panel on the right that grows larger when you try and zoom in. I initially created a bookmarklet that could be used to remove the side panel by clicking on a link in my favorites to inject some javascript into the webpage to alter the CSS classes, but that still required that I manually click the button every time that I visit he website.

I planned on making a Google Chrome Extension, but recently Chrome announce that they now will automatically convert Greasemonkey scripts into Chrome Extensions. This means you can write a cross-browser compatible script that can run in Firefox, Chrome or any other browser that supports Greasemonkey scripts. It only took a few minutes to convert the bookmarklet into a userscript, and I posted it on userscripts.org so that anyone can use it. The script will automatically remove the sidebar so that zooming works much better. You can even change the script to automatically zoom a specified amount, however I left this off by default since the right amount highly depends on the width of your screen. Here are the results:

Normal

Zoomed

 

Hopefully someone else will find this useful. I have a few other bookmarklets that I use and will probably convert into userscripts soon, as well as a few new ones that I want to work on when I get some free time.

Powershell, 7-Zip, Amazon S3 Upload Script with AES-256 Encryption

I recently was tasked with finding a way to store some backup files from our server in a secure and reliable off-site location. After talking with our hosting provider, who wanted around $600 a month for off-site tape rotation, we decided to look at using Amazon Simple Storage Service (Amazon S3) to store the files in the cloud instead. We needed a way to automate the upload process and make sure that the data was encrypted, so I spent a few days working on a Powershell script (using the excellent PowerGui Script Editor) that uses 7-zip to create a .7z archive with AES-256 encryption and then send it up to Amazon S3 using the Amazon Web Services SDK for .NET. Here is the script:

#Description: Powershell 2.0 script to zip and encrypt a folder using 7zip and then send it to Amazon S3 #Author: Greg Bray (2/19/2010) #Website: http://codeblog.theg2.net/2010/02/powershell-7-zip-amazon-s3-upload.html #License: free for personal or commercial use under the Creative Commons Attribution 3.0 United States License. #Prerequisite: You must first install the latest version of 7Zip from http://www.7-zip.org/ (licensed under the GNU LGPL) #Prerequisite: You must also install the Amazon Web Services SDK for .NET from http://aws.amazon.com/sdkfornet/ (licensed under the Apache License, Version 2.0) #Recommended: I recommend using CloudBerry S3 Explorer for managing Amazon S3 files. http://cloudberrylab.com/default.aspx?page=cloudberry-explorer-amazon-s3 #Recommended: I recommend using http://PowerGui.org Script editor if you want to change or debug this script. #Usage: ps7ZiptoAmazonS3 'BackupFolderPath' 'S3ObjectKey' 'TranscriptLogFile' # BackupFolderPath = Folder to backup (do not include a trailing slash) # S3ObjectKey = Object key where to store file on Amazon S3. Note: If object already exists on S3 it will be overwritten. # TranscriptLogFile = Optional log file where the transcript will be appended. #NOTE: you also will need to set the Zip and Encrypt settings, Amazon S3 settings, and 7Zip/AWSSDK file paths below <#Example calling from another powershell script (with log file): & 'c:\Backups\ps7ZiptoAmazonS3.ps1' 'c:\Backups\DataFolder' 'S3Backups/DataFolder.7z' 'c:\Backups\BackupLog.txt' #> <#Example calling from Run or task Scheduler: NOTE: may need to run "Set-ExecutionPolicy RemoteSigned" in powershell to allow unsigned local scripts to execute powershell.exe -Command "& 'c:\Backups\ps7ZiptoAmazonS3.ps1' 'c:\Backups\DataFolder' 'S3Backups/DataFolder.7z' 'c:\Backups\BackupLog.txt'" #> #Check arguments and set variables if ($args.count -ge 2) { $FolderPath = $args[0] #Local folder to encrypt and send to S3 (No trailing slash) $S3ObjectKey = $args[1] #Destination on S3 where file will be stored. Note: If object exists it will be overwritten. if ($args.count -ge 3) { #start logging transcript Start-Transcript $args[2] -Append | Out-Null } } else { #Default preset arguments used if no values are provided $FolderPath = "c:\Backups\DataFolder" $S3ObjectKey = "S3Backups/DataFolder.7z" } write-host "`r`n`r`n" #Start log file with 2 blank lines $dtStart = [System.DateTime]::Now #Save start time to calculate total time below write-host ($dtStart.ToString() + " - Script started") #Zip and Encrypt settings $EncryptionKey = "secret_password" #Data is encrypted using Strong AES-256 encryption. Recommended password size is 10-16 characters. $TemporaryPath = "c:\Temp" #No trailing slash. Used to store zip file localy before uploading. File will be deleted after upload but not if there is an exception (see below). #Amazon S3 account settings (See http://aws-portal.amazon.com/gp/aws/developer/account/index.html?action=access-key): $S3AccessKeyID = "ABCDEFGHIJKLMNOPQRST" #Your Amazon S3 Access Ky ID $S3SecretKeyID = "0123456789abcdefghijklmnopqrstuvwzyz" #Your Amazon S3 Secret Access Key $S3BucketName = "S3BackupBucket" #S3 Bucket name where the file will be stored Write-Host ($dtStart.ToString() + " - FolderPath:'$FolderPath' S3BucketName:'$S3BucketName' S3ObjectKey:'$S3ObjectKey'") #Load AWS Assembly and Initialize 7Zip path (only runs once when debugging script) if($7ZipPath -eq $null){ $7ZipPath = "C:\Program Files\7-Zip\7z.exe" #Path to 7Zip $AWSDOTNETSDKPath = "C:\Program Files (x86)\AWS SDK for .NET\bin\AWSSDK.dll" #Path to Amazon SDK for x64 system. Remove " (x86)" when running on an x86 machine if (-not [System.IO.File]::Exists($AWSDOTNETSDKPath)) { #Try path for x86 system $AWSDOTNETSDKPath = $AWSDOTNETSDKPath.Replace(" (x86)","") if (-not [System.IO.File]::Exists($AWSDOTNETSDKPath)) { #File not found, throw exception write-error "ERROR: Cannot find Amazon Web Services SDK. Please download .NET SDK from http://aws.amazon.com/sdkfornet/ and update AWSDOTNETSDKPath in script." exit 2 } } #Load AWS Assembly [Reflection.Assembly]::LoadFile($AWSDOTNETSDKPath) | Out-Null } #Function for computing Amazon S3 MD5 digest (128 bit base64 encoded) using System.Security.Cryptography.MD5 function Hash-MD5 ($file) { $cryMD5 = [System.Security.Cryptography.MD5]::Create() $fStream = New-Object System.IO.StreamReader ($file) $bytHash = $cryMD5.ComputeHash($fStream.BaseStream) $fStream.Close() return [Convert]::ToBase64String($bytHash) } #Zip and Encrypt files Write-Host ([System.DateTime]::Now.ToString() + " - Zip and Encrypt Files") $FolderName = $FolderPath.Substring($FolderPath.LastIndexOf("\")+1) #grab the name of the backup folder $TimeStamp = [datetime]::Now.ToString("yyyy-MM-dd_HHmm") #Create unique timestamp string $strFile = [String]::Format("{0}\{1}_{2}.7z", $TemporaryPath,$FolderName,$TimeStamp) #Create filename for the zip #7z options: add, type 7z, Archive filename, Files to add (with wildcard. Change \* to \prefix* or \*.txt to limit files), compression level 9, password, encrypt filenames, Send output to host so it can be logged. #See http://dotnetperls.com/7-zip-examples or the 7zip help file for more info on command line switches. #NOTE: -mx7 and -mx9 get around 10 to 1 compression for text files and take round 5 minutes for 1GB of data. Use -mx3 or -mx5 to increase speed but decrease compression level. & $7ZipPath "a" "-t7z" "$strFile" "$FolderPath\*" "-mx9" "-r" "-p$EncryptionKey" "-mhe" | out-host #Call 7z.exe to create archive file if($LASTEXITCODE -ne 0){ #Detect errors from 7Zip. Note: 7z will crash sometimes if file already exists Write-Error "ERROR: 7Zip terminated with exit code $LASTEXITCODE. Script halted." exit $LASTEXITCODE } #Create Amazon PutObjectRequest. Details at http://docs.amazonwebservices.com/sdkfornet/latest/apidocs/html/T_Amazon_S3_Model_PutObjectRequest.htm Write-Host ([System.DateTime]::Now.ToString()) " - Creating S3 PutObjectRequest" $AmazonS3 = [Amazon.AWSClientFactory]::CreateAmazonS3Client($S3AccessKeyID, $S3SecretKeyID) $S3PutRequest = New-Object Amazon.S3.Model.PutObjectRequest $S3PutRequest.BucketName = $S3BucketName $S3PutRequest.Key = $S3ObjectKey $S3PutRequest.FilePath = $strFile Write-Host ([System.DateTime]::Now.ToString()) " - Compute File MD5 Digest" $strMD5 = Hash-MD5($strFile) #NOTE: may take 5-30 seconds to compute digest for 1GB file. $S3PutRequest.MD5Digest = $strMD5 #Use MD5 digest to ensure data is not corrupted during transport. $dFileSize = (Get-Item $strFile).Length / 1MB #Get archive size in MB Write-Host ([System.DateTime]::Now.ToString()) ([String]::Format(" - Uploading File:'{0}' Size:{1:#,###.#} MB", $strFile, $dFileSize)) $S3Response = $AmazonS3.PutObject($S3PutRequest) #NOTE: upload defaults to 20 minute timeout. #If upload fails it will throw an exception and $S3Response will be $null if($S3Response -eq $null){ Write-Error "ERROR: Amazon S3 put requrest failed. Script halted." exit 1 } Write-Host ([System.DateTime]::Now.ToString()) " - Delete Local 7z File:'$strFile'" Remove-Item $strFile -Force $dtEnd = [datetime]::Now $tsTotal = $dtEnd.Subtract($dtStart) Write-Host ($dtEnd.ToString()) " - Script finished. Total time: " $tsTotal write-host "`r`n`r`n" #End log file with 2 blank lines if ($args.count -ge 3) { #stop transcript Stop-Transcript }

It seems to work pretty well so far, taking about 5-10 minutes to zip and encrypt 1GB of SQL Backups down to 100MB and then upload it to Amazon S3. From there we can use tools like CloudBerry S3 Explorer to browse or download files when needed. The monthly costs to keep data on Amazon S3 is $0.150 per GB, with $0.10 per GB transfer in and $0.150 per GB transfer out. With a 1 week backup retention and minimal data-out transfers we expect to pay around around $10 to $20 a month and should be able to access it much quicker than if we were using off-site tape storage. Cloud computing FTW!

UPDATE 6/14/2010: I just posted the script used to create the Database backup folder.

Parallel ForEach file processing in IronPython: Get some TPL love in IPY!

I recently have started playing around with the Task Parallel Library (TPL) that will be shipping in .NET 4, which lets you easily spread a workload across multiple cores using a simple Parallel.ForEach statement. I have a few IronPython scripts that I want to convert to start using multiple threads, so I thought I would try using the .NET 3.5 version of the TPL from inside of IronPython. It turns out that the TPL works just fine, but writing thread safe code in IronPython can be tricky. First off, the System.Threading.Interlocked class does not work on IronPython integers because python integers are immutable. That really sucks, because it means that the only way to change a global integer value in a thread safe manner is to use locks or the System.Threading.Monitor class. One interesting workaround is to use a python list instead of an integer. Appending a value to a list is an atomic operation, so instead of calling Interlocked.Add you can just append all the values to an empty list an use sum to return the aggregate value or len() to get the number of items in the list (emulating incrementing a variable). It is a bit of a hack, but sometimes easier that using locks. Also, you have to remember that the print statement is not thread safe, so instead you should use something like System.Text.StringBuilder to buffer your print statements and then print them once you are back to a single thread code section.

Below is a small sample script that I wrote to test processing a large number of text files using IronPython. It will search through all files in a given directory that match a given pattern (ie: *.txt) and return the total number of files and lines. It will also search for a given tolken inside each file and return the total number of matches. This is a task that is very easy to run on multiple cores and is a perfect fit for the Parallel.ForEach method. By default it will search your temporary files folder, which on my workstation had 69 .txt files with a total of 469,286 lines. The single threaded version took about 2.3 seconds to run and the multi-threaded version took between 0.8 and 1.0 seconds. The workload was spread across 8 cores, which caused a 2x improvement in speed, even thought this example is still primarily bound by the drive IO speed. Still it shows how using the TPL can greatly increase performance for common tasks.

"""Uses the Task Parallel Libray (TPL) to processes files using multiple threads. Please download and install the Reactive Extensions (Rx) for .Net 3.5 from http://msdn.microsoft.com/en-us/devlabs/ee794896.aspx before running this script Usage: import the file into an existing script or run the following from the IronPython console: execfile("c:\PathToFile\Filename.py") """ import clr from System import Environment from System.IO import File,Directory from System.Diagnostics import Stopwatch from System.Text import StringBuilder from System.Threading import Monitor clr.AddReference('System.Windows.Forms') strAssembly = "C:\Program Files (x86)\Microsoft Reactive Extensions\Redist\DesktopV2\System.Threading.dll"; if not File.Exists(strAssembly): # Try both 32bit and 64bit file path strAssembly = "C:\Program Files\Microsoft Reactive Extensions\Redist\DesktopV2\System.Threading.dll"; if not File.Exists(strAssembly): # Library does not exist, prompt with error message print "TPL assembly not found. Please download and install the Reactive Extensions (Rx) for .Net 3.5 from http://msdn.microsoft.com/en-us/devlabs/ee794896.aspx" raise Exception("Task Parallel Libray not found. Please download and install the Reactive Extensions (Rx) for .Net 3.5"); #Rx and TPL for .NET 3.5 can be downloaded from http://msdn.microsoft.com/en-us/devlabs/ee794896.aspx #Load TPL clr.AddReferenceToFileAndPath(strAssembly) from System.Threading.Tasks import * #Method to process files. See test call at bottom of file def TPLProcessFiles(strTolken, strPath=None, strFilter="*.txt"): """Finds all occurences of strTolken in strPath for files that match strFilter. strTolken = string tolken used for searching. NOTE: search is case-insensitive and includes partial match (cat will match Category). strPath = Path to the directory used for searching. Default is system's temp directory strFilter = file match filter for search. Default is *.txt""" print "Start TPLProcessFiles('%s', '%s', '%s')" % (strTolken, strPath, strFilter) if strPath == None: strPath = Environment.GetEnvironmentVariable("TEMP") print "Using temporary files path: %s" % strPath elif not Directory.Exists(strPath): raise Exception("Invalid path: " + strPath) #Setup swTime = Stopwatch() swTime.Start() LogWriter.Reset() Results.Reset() strFiles = Directory.GetFiles(strPath, strFilter) #Single thread test code. Useful for when you get an AggregateException_ctor_DefaultMessage exception in the Parallel.ForEach version or for benchmarking #for aFile in strFiles: # SearchFile(aFile, strTolken) #TPL version with multiple threads pfeResult = Parallel.ForEach(strFiles, lambda aFile: SearchFile(aFile, strTolken)) print LogWriter.ToString() #Have to use LogWriter class below because print is not a threadsafe function strResults = Results.ToString() #Have to store results in a thread safe class swTime.Stop() print "Finished. Total Files: %d LogCallCount: %d Total Time: %0.3fs Total Cores: %d" % (len(strFiles), LogWriter.GetCallCount(), float(swTime.ElapsedMilliseconds)/1000, Environment.ProcessorCount) print "Results - %s" % strResults #File search method def SearchFile(strFilename, strTolken): """Method used for searching a file. This will be called from the Parallel.ForEach loop. Returns a tuple of (Filename, Total line count, Total tolken count). NOTE: Code must be thread safe, so you cannot use the print statement or increment global variables.""" #LogWriter.WriteLine("Start file: %s searching for %s" % (strFilename, strTolken)) f = open(strFilename, "r") #open file as readonly t = f.read() #get text f.close() Results.Add(1, t.count("\n"),t.count(strTolken)) #Add to global results #Class used to store results class Results: Files = 0 #static members Lines = 0 Tolkens = 0 @staticmethod def Reset(): #NOTE: this is not thread safe, but we will only call this method from a single thread Results.Files = 0 Results.Lines = 0 Results.Tolkens = 0 @staticmethod def Add(intFiles, intLines, intTolkens): Monitor.Enter(Results) #Use the System.Threading.Monitor class to ensure addition is thread safe Results.Files += intFiles Results.Lines += intLines Results.Tolkens += intTolkens Monitor.Exit(Results) #Finish thread safe code @staticmethod def GetValues(): return (Results.Files, Results.Lines, Results.Tolkens) @staticmethod def ToString(): return "Total files: %d Total lines: %d Total tolkens: %d" % Results.GetValues() #Log writer class to buffer print statements class LogWriter: sbLog = StringBuilder() #StringBuilder is thread safe when used as a static member CallCount = [] #Integer operations are not threadsafe, but appending to a list is @staticmethod def WriteLine(strMessage): #NOTE: must be thread safe. String LogWriter.sbLog.Append(strMessage+"\r\n") LogWriter.CallCount.append(1) @staticmethod def ToString(): return LogWriter.sbLog.ToString() @staticmethod def Reset(): LogWriter.sbLog.Length = 0 LogWriter.intCallCount = [] @staticmethod def GetCallCount(): return sum(LogWriter.CallCount) if __name__ == "__main__": TPLProcessFiles("Error") #Run test if this file is loaded from the IronPython Console

TPL and Parallel.ForEach in .Net 3.5 using Reactive Extensions for .NET (Rx)

The next version of the .Net framework and Visual Studio both have some pretty cool features to help programmers work with multiple cores, which is great but doesn’t help the majority of us that are stuck with .Net 3.5 for the foreseeable future. Luckily Erik Meijer and the Cloud Programability Team have back ported the Parallel Extensions Framework (PFX) to .Net 3.5 and Silverlight 3 as part of the Reactive Extensions for .NET (Rx). Rx adds the IObservable<T> and IObserver<T> interfaces, which are the mathematical duality of the IEnumerable<T> and IEnumerator<T> and provide tools for doing Reactive Programming. There are many different ways to use Rx, but internally they all use the Task Parallel Library (TPL) as the “special sauce” to automate processing tasks across multiple threads.

Parallel.ForEach is a part of the TPL that can be used to unroll an outer loop and have it run across multiple threads. Take the following example. This is standard single threaded code that loops through a collection of 2 card Texas Holdem starting hands and evaluates all possible 7 card hands that include those two cards:

//Enumerate all possible 2 card starting hands with an Ace and a King foreach (var aPKAK in HoldemHand.PocketHands.Query("AK")) { //Enumerate and score all possible 7 card hands that include the starting hand foreach (var aHand in HoldemHand.Hand.Hands(aPKAK, 0, 7)) { ulHand = HoldemHand.Hand.Evaluate(aHand); lCount++; } }

There are over 2 million hands in the inner loop, which gets run once for each of the possible starting hands in the outer loop. There are 12 offsuit starting hands and 4 suited starting hands with an Ace and a King, which means that the outer loop would run 16 times, however those 16 executions are separate and could easily be run across multiple threads. That is where Parallel.ForEach comes in. Here is the same code, which will automatically be scheduled across multiple threads:

//Use Parallel.ForEach and a Lambda Expression to get the same results Parallel.ForEach(HoldemHand.PocketHands.Query("AK"), aPKAK => { long plcount = 0; //Local loop count foreach (var aHand in HoldemHand.Hand.Hands(aPKAK, 0, 7)) { ulHand = HoldemHand.Hand.Evaluate(aHand); plcount++; } //Use threadsafe method to increment global count System.Threading.Interlocked.Add(ref lCount, plcount); });

The bulk of the code is the same, with the only changes being that the inner block gets converted into a Lambda Expression (could have also used a delegate) and instead of incrementing lCount in the inner loop we increment a local loop variable and then atomically add it to the global value. We could have used System.Threading.Interlocked.Increment to atomically increment lCount inside the inner loop, but this adds a lot of unneeded locks that slow down all the threads. Keeping a local copy of the values and only locking once at the end provides much better performance.

And with those few small changes we are able to start using multiple threads, which on my local machine with 4 cores and 8 threads ended up decreasing the processing time from 1.55 seconds to 0.67 seconds and more than doubling the number of hands processed per second from 16,420,998 to 37,816,595.

If you want to start using the TPL you can download it here and add a reference to the System.Threading.dll located in the “C:\Program Files (x86)\Microsoft Reactive Extensions\Redist\DesktopV2” folder. And while you are at it you might as well play around with Rx too!

Enjoy!