Propriatory Nvidia drivers have a feature called 'twinview' were you can have 3d acceleration for both monitors on cards that have dual outputs. (edit: for DVI connections be carefull, you need to have a card and video cable that supports 'dual bandwidth dvi' or some such thing for high resolutions. Cheaper cards don't support this and thus only high resolution is aviable thru the analog vga connector.)
Otherwise any card with multiple outputs should work fine. Matrox for instance has dual and quad output cards aviable.
Personally, until I recently got a fairly new card, I've run dual monitors successfully for years by simply using multiple cards. Like having a AGP card and a PCI card and such. There is a X windows feature called 'xinerama' that allows you to have a desktop span multiple monitors... otherwise the default is to have seperate desktops that you can move between with the mouse. As long as you don't worry to much about 3d acceleration any combination of cards can work fine if your using regular X drivers.
edit:
I've kinda gotten tired of dual monitors now, though. Thru dealing with my laptop (small screen, 1024x768 resolution ) I've learned lots of different tricks and have customized key combos (gnome desktop) to deal with window handling more efficiently. When going back to my regular desktop I've found that I've grown accustomed to a single screen.