I have the Kurt Kinetic fluid trainer. The resistance it provides, subjectively, does feel like a good faith attempt at simulating actual riding resistance. It makes some noise, but it's not as loud as a freight train.

The resistance actually feels like it's a little more than what I'd get in the same gear on the road, but the way it increases with speed is pretty realistic. I have a cheap rubbery mat thing that I put under mine that seemed to make it a bit quieter.