How do I set a custom SMS tone for a single contact? If it matters any I have an HTC Desire Z with the original Sense install.

We have this monitoring service at work that keeps an eye on the various servers and starts frantically SMS'ing me whenever things aren't working right. 

I'd like to set a separate notification tone for SMS's from that one contact. How do you do that? Under the contact I have an option for the Ringtone, but there doesn't seem to be an option for the SMS notification tone.
Go into your contacts.  Open the contact you want to personalize the message tone.  Click "edit".  Then scroll down to the bottom and click on "ADD ANOTHER FIELD".  Tap to put a check mark beside "Message tone" then tap "OK".  Then tap on "Message tone" and your options should come up e.g. Media storage, Zedge, etc.  Tap which option you want to choose the message tone for that person.  then choose the ringtone for them and tap "set ringtone".  Make sure you then "save" that contact.
That worked for me with my Samsung Galaxy S5.  Hope this works for you.