I have ripped over 400 DVD movies in Handbrake using the default settings and they look surprisingly good on my 46" Sony and Apple TV. There are a few scenes - mostly really dark ones - that don't always come across so well. I may rip those DVD's again and try higher quality settings. But for the most part, I have been very impressed and the typical movie only takes between 1 and 2 gb.
I also have a blu-ray player and a bunch of disks. They definitely look better, but it depends on the subject material. I'd be wiling to bet that many people wouldn't even notice the difference during casual viewing.
I've never owned one of those upscaling players, but would guess one advantage they have over regular DVD players would be an HDMI interface that preserves the full digital quality of the disk instead of converting to/from analog (such as component video or s-video). Of course, the Apple TV also uses an HDMI interface.