Although Mac I have to say that your friends are probably showing you the edited highlights of HK. At least 50% of trailing spouses I know work, and many of those that don't would probably have been SAHP in the UK anyway. I think in terms of how much being an expat impacts the life of the trailing spouse depends on a lot of things, such as length of "posting" and autonomy over how long you stay- we could well end up staying in HK for the best part of a decade, so definitely time to put roots down- it's much harder for trailing spouses if you're doing 2 yrs here, 18 month there, especially with children in tow.
Also, if there is a defined expat population who are socially segregated from the local population by some factor (cultural or language) that helps, as then expats basically have to be friends with each other - makes for an inclusive and friendly environment as no-one has long standing friendship groups to rely on. Weirdly, I think Australia is quite a tough gig because that's not the case.
I'm divided on package. The old expat packages are dying out in HK (not so much in Singapore) and I think it creates a more diverse expat population, and because more people are on local contracts vs home country secondments, they have more autonomy over how long they stay. It also produces a better working environment because everyone's on an equal footing.
Anyway, I guess what I'm saying is that there are tough expat experiences, and really positive ones. It can work out, but it's not for everybody.