It really depends on how far away you sit from your TV, what the screen size is, and how good your vision is. When I'm sitting three feet from my 28" HD monitor, you bet I can tell the difference between HD and SD. My vision's not quite 20:20, though, so on the couch 15' away from a 100" projector screen, I'd have a hard time telling 720p from 1080p just based on sharpness, but the larger size of any macroblock artifacts would give it away.
In the right conditions, I'm positive you'd see the difference, too. Try renting both a DVD and Blu-ray of the same movie from Redbox, and watching them a little closer to the screen. The Blu-ray will let you see more individual hairs, individual distant bricks, etc. But even SD on an HD screen can be better than SD on an SD screen, due to increased pixel density and reduced flicker.
It requires another player. (In case of BluRay)
It's harder to stream through the internet. (In case of digital formats)
For those people, like me, who in 95% of practical uses do not see the difference, the above disadvantages make it a no brainer.
I guess I am asking, is it really worth it all the trouble?
If you front-load the inconvenience by buying the discs, ripping them all to a hard drive, and using something like XBMC or Plex on an HTPC, then for any serious watching where you sit down for the whole show, yes, it absolutely is worth it. I wish more people had access to the convenience a media server gives; it's what content should be like in the digital age.
Edit: I'm usually about 1.5 screen lengths away, so that probably makes a difference.
You would have to get a "recent" movie mastered specifically for BluRay to see the difference. I've done this several times myself (specifically "The Princess Bride" - which appears to be an upscale, and "How to Train your Dragon" which is significantly clearer).