In addition to the cards mentioned by NaNaKat above, this feature is also available on the Matrox Parhelia, ATi Radeon series and all Nvidia GPUs newer than the GeForce2 GTS. On Radeon 9000-series, GeForce FX series and newer, this feature can be enabled at moderate resolution (even as high as 1280x1024 on very new cards) without murdering framerate. The result, especially on older games, is a marked improvement in visual quality though some things, especially very small text may look a little indistinct.
Using FSAA really improved Final Fantasy 8 for me, at its maximum 800x600 resolution. I also use it with Neverwinter Nights and EverQuest, with nice, fast results. I'm using a GeForce 6600GT at 1600x1200, though prior to that I was using a GeForce FX 5600 at 1280x1024 with similarly high-quality results.