Caffeinated Coder

I’ve been impressed by the attention paid to discoverabiltiy in powershell, which has allowed me to quite a bit of learning without ever leaving the command line. Here are a few of my favorite features:

Guessing - This may seem strange to list as the number one discoverability feature, but I think the Powershell design team did an excellent job of exposing functionality through well scoped cmdLets that are logically and consistently named. With cmd.exe, the majority of the heavy lifting is done by executables with terse names that have become bloated with too many responsibilities so that you have to constantly rely on references to figure things out. In powershell, the verb-noun pattern is strictly enforced and the names are always consistent so that I can write Get-Command *service and be assured that I will get all the cmdLets related to services and then figure out what they do through the verb part of their name.

Built-In Help - If you’re not sure what a cmdLet does, then use the Get-Help cmdLet to see the documentation. DOS exes usually have a ? switch for help, but what really makes this powershell feature shine are the -full and -example switches that bring back detailed examples and additional notes about usage and each of the parameters. The powershell team also embedded plenty of conceptual help outside of cmdlets that follow the about_ pattern. You can see them through Get-Help about_*.

Trusting Operations - Are you afraid to execute a script because you’re not sure what it will do? Try the -whatif parameter that is ubiquitous to all commands. For example, del C:\f*.txt -whatif will just tell you which files would have been deleted rather than actually deleting them.

Get-Member - I find this cmdLet most useful when I’m not sure what object I am getting back from the pipeline. I simply add | Get-member to the end of the command and I can see what additional methods I have to work with or what properties I can perform filtering and sorting on.

Get-Alias- I love all the transitional and convenience aliases provided by powershell to maximize productivity for throw-away, administrative tasks, but sometimes it makes it hard to decipher examples that you see on the web. Calling Get-Alias gps will return the full, logical name of the cmdLet and will usually allow you to figure out its function. Better yet, use this command with no parameters to return all the aliases so you can learn them and increase your own command line speed.

Other resources - Despite all these great features, it would be silly to not also seek outside resources. Here are a few that I’ve found helpful

WebCasts - I’ve made my way through 2 of the 5 Powershell Week web casts. They are a little long (usually over an hour), but are full of good information and perfect for the visual learners.

Blogs - The best is probably the one put out by the Powershell Team. I also recommend Keith Hill and The Powershell Guy.

PodCasts - I highly recommend the all 3 shows that Scott Hanselman did on the subject. The Powerscripting Podcast is a little too slow paced for my taste, but it could be good if you’re brand new and want something to listen to while mowing the lawn.

Tutorials - The Powershell Owner’s Manual on the technet script center isn’t bad, although the authors went a little overboard at times to make the writing more interesting than the average tech spec.

Books - I agree with Chris Sells and Don Box that books are still the best way to grok the zen of a technology. I’m finishing up with an excellent book by the Powershell Developer Lead, Bruce Payette, called Powershell in Action that I would highly recommend.

There was some discussion about SQL Server 2008 at the KC .NET user group meeting last night and I involuntarily winced at the thought of another upgrade because we just finished an intensive migration process a few months ago.

In hindsight, the migration went fairly smoothly but it was definitely not a trivial process, nor was it something that I would want to repeat any time soon. It definitely requires some advanced planning and organized regression testing. I thought I would jot down a few lessons that we learned before I completely block this experience out of my memory.

Here are some quick background stats (mid-sized bank):

1. Apps Tested: 40
2. Technologies supported: .NET 2.0, 1.1, 1.0, Classic ASP, VB6, PowerBuilder, Access 97
3. Databases: 25
4. Total Tables: 2400
5. Total DB Objects (sprocs & udf’s): 3500
6. DTS packages: 100
7. Server implementation: Clustered Quadzilla running on 32 bit Standard Edition, separate Reporting Server
8. Total required object changes: 40-50

Lessons Learned:

1. The upgrade wizard was helpful but not full-proof. It caught about 60% of the changes we had to make. If you have separate reports or application logic that runs on specific days (i.e. end of month, start of month, end of quarter, etc), make sure you run profile traces on those days and then use the trace files as input for the upgrade adviser.
2. Script out all the sprocs and udf’s and then run the ddl scripts against an upgraded test server. We caught another 20% of the problems through the syntax errors we got when trying to recreate these objects.
3. Set up an organized regression test of your critical apps, especially if your business has a low tolerance to risk. The last 20% of our changes were only caught through testing because of dynamically generated SQL or SQL that was embedded within the applications rather than stored as objects within SQL Server.
4. Carefully review all proposed sproc changes and when possible compare before and after result sets with a tool like RedGate BeyondCompare. Most of the changes we had were syntax related and easily fixed, however we had to redo a few changed sprocs because of some misunderstandings of how the old *= join syntax worked.
5. Be on guard for a few hard to spot errors that might show up later in production. For example, this excerpt from one of our udf’s actually concatenated multiple rows together in SQL 2000, but only returned the first row in 2005. The only way we figured this out was that someone reported that a few of our app generated pdf documents were missing several paragraphs.

 

 

   1: select @para = @para + cast(dgp.Paragraph as varchar(7000)) + ‘

   2:

   3:             from DocumentParagraphs dgp

   4:

   5:             where dgp.ReportID = @ReportID

   6:

   7:                         and dgp.ActiveFlag = 1

   8:

   9:             order by dgp.ParagraphOrder

6. Do not rely on 8.0 compatibility mode as a safety net. Dropping back into 8.0 compatibility mode did not make the problems we found magically work.
7. If you have to move many DTS packages to a new machine, be prepared for some mind-numbing, tedious work. Each package had to be moved manually with lots of GUI clicks and all inner packages had to be re-selected due to GUID issues. Despite this headache, the DTS process was much smoother than expected and we didn’t have to make any code changes within the packages. We did experience some additional flakiness with SQLMail, but we’re not sure if that is related to SQL 2005 or moving from a single server to a cluster.
8. Beware Legacy Apps - If you have to support an old version of a defunct language like PowerBuilder, make sure that you are nice to the only guy who can support it. It turns out that we really didn’t have one of those guys anymore so a couple of us were forced to stumble around in the IDE for the first time ever in order to search for the offending embedded SQL while under deadline pressure. NOT FUN!
9. Don’t call undocumented functions! An old C++ module used by a couple of our legacy apps uses pwdcompare(). We finally got it to work but only by seriously elevating the application’s account permissions.
10. Access wasn’t so bad - Contrary to all expectations, we really didn’t have any problems with linked tables within our Access databases even though we were stuck on a really old version (Access 97). I expected this to be a nightmare, but it wasn’t.

Despite these hassles, the migration was definitely worth the effort for us. We were running up against a memory limit imposed by SQL Server Standard edition with our old server and were starting to experience some painful performance problems. SQL 2005 allowed us to upgrade to a much more powerful machine without having to pay the additional $180,000 in licensing fees that it would have cost us to upgrade all of our servers to the SQL 2000 Enterprise Edition which had support for additional memory.