# 5 SQLPS Tips and Tricks

Powershell and SQL Server have been natural buddies from day one. SQL Server administrators need to automate file operations and other Windows tasks in conjunction with their database duties all the time, and xp_cmdshell only gets you so far. Fortunately, since the introduction of the SQLPS Powershell module in SQL Server 2008R2, you no longer have to load tiresome SMO assemblies to interface with SQL Server through Powershell. SQLPS uses a suite of cmdlets to provide a pretty decent user experience for the Powershell/SQL developer, although it’s not without its quirks. Let’s take a look at a few quick tips and tricks I’ve picked up while using this module in the wild. I hope they save you some of the time it took me to learn them!

5 Tips and Tricks for using SQLPS

# CloudPleasers: Cloud Architect

Check forrestbrazeal.com each Friday for a new “CloudPleasers” cartoon: a light-hearted look at life in the cloud.

# My one weird rule for a stumble-free sprint

So I’ve been thinking a lot about the scrum methodology lately. I’ve worked at a couple of places (including my current job) where it’s heavily utilized, and naturally I’d like to get the most out of it that I can.

To sum up the link above in my own words, if you need a quick refresher: The scrum methodology is an agile development model involving short burts of work (sprints) toward a well-defined goal, punctuated by regular “standup” meetings where sprint participants give brief updates about their progress on their assigned tasks or “stories”. Each sprint ends with a review and retrospective period containing demonstrations of finished work and analysis of the metrics generated during the sprint. The idea behind the model is to increase productivity across a larger development team while quickly identifying and eliminating any stumbling blocks.

Sprints can be a great motivational and management tool, but they can also cause a lot of problems if they start to drift from their intended purpose. In order to help me keep my eye on that purpose, I’ve developed the following rule.

# CloudPleasers: CI

Check forrestbrazeal.com each Friday for a new “CloudPleasers” cartoon: a light-hearted look at life in the cloud.

# Adventures in AWS: A Very Scary Error

In this series, I explore some of the everyday challenges facing an AWS developer/sysadmin. Today: I grapple with a gruesome bug in the AWS Tools for Windows Powershell.

THE PROBLEM
Let’s say you have a Powershell script that makes a call to the S3 API:

Write-S3Object -BucketName mybucket -File C:\path\to\file -Key my/key


This call works great when you run it yourself from the Powershell console or ISE. However, when it runs from a scheduled task on your machine under a service account, let’s suppose that to your surprise, this seemingly straightforward call not only fails to upload your file to S3, but also produces the following gnarly-looking exception:

# CloudPleasers: Source Control

Check forrestbrazeal.com each Friday for a new “CloudPleasers” cartoon: a light-hearted look at life in the cloud.

# Adventures in AWS: How to break the EBS Snapshot feature

In this series, I explore some of the everyday challenges facing an AWS developer/sysadmin. This week: how does the EBS Snapshot API stand up under heavy load?

UPDATE 6/9: Turns out my original fix wasn’t quite enough to overcome the bug 🙂 See the revised solution below.

The Background
If you keep important data on EBS volumes, you’ll want to back them up using EBS’s nice Snapshot feature, which allows you to grab a point-in-time backup of a volume and dump it in S3. It’s an incremental backup, meaning you are only snapping–and being charged for–the blocks changed since the last snap. If you are using EBS for something fast-changing and critical like transaction logs, it’s not a bad idea to snap as often as possible. If you’re snapping many volumes many times a day, you’ll also want to tag them with an identifier or two for sanity’s sake. AWS allows you to place up to ten tags on a resource, so what happens if you have, say, fifty thousand snapshots in an account, each with four to six tags?