SLI is amazing. If you thought the GeForce 6800 Ultra was a powerful card, look at the results of the SLI versions. It literally doubles the performance in some applications. SLI is awesome, but you're right, not many games support it. It'd be a great investment if more games supported it. As of now, it's basically out there to brag about your 3D Mark score.
Oh, and SLI is just two NVIDIA videocards running together. They must be PCI-E videocards, not AGP. A bridge connects them both sort of like the old Voodoo 2 series (except the connector is internal, not external). After being bridged, it's like having two videocards combined into a single powerhouse -- as long as the application you're using it for supports it.
-Lasereth