I'm quite keen on this, as an option which can be disabled by users.
Maybe two admin defined sounds, one for an inbox notification, one for a normal alert.
They don't have to be intrusive and annoying, like some people automatically imagine they will be. Perhaps even volume adjustable, so it can be adjusted relative to the OS volume.
Could have some default sounds, which can be replaced by the admin if so wished, so @BassMan can hear his funky base line
For smaller sites, or sites with a core group of members that doesn't fluctuate a lot this can be incredibly useful.
I don't use facebook much anymore, but when I do leave it open in the background and someone in our facebook group of approx 50 people starts a conversation, the post gets a good response very quickly. I feel this is definitely in part due to the sound alert.
I'm shutting one of the facebook groups I'm apart of to stop the dilution of content between there and the forum. This sound feature will be missed.
A user option to restrict sound alerts to a specific forum would help it to scale better with large sites where a user may receive lots of alerts, but want to be notified immediately if something happens in an important forum they want the latest news from. For example classifieds or news forums.
It could also only trigger when the window isn't active perhaps, so it doesn't bother you when you're attention is already focussed on the website.
If it's implemented in the correct way, this could be in incredibly useful feature.