BLannom... it is a sports car, so road noise will be part of the equation. But, in my opinion, it is also about you. How much can you tolerate? When I test drove the Z, I'm surprised how quiet it was in the cabin; both on backroads and highway.
In comparison to my fiance's Scion tC, Z is much quieter. Now, to some, it may seem that I don't know what I'm talking about, but that's how I perceive things between the two cars.
I suggest you go out and test drive the Z. You should then know how it compares to other cars you've driven previously.
In terms of auto or manual transmission... there's really nothing anyone can say about that. Your call. Lastly, the Z is a well-balanced car, so I wouldn't look at it as having "raw power"... it doesn't. But from what you're describing of the use you need the Z for, it is more than you'll ever need.
And read along with this ongoing thread about people's personal experience with their Z --
370z Complaints so far?