The first 3D graphics cards (Voodoo I think) were a seperate card that had to be added on to an existing 2D card and required a passthrough connector. The 3D rendering calculations were offloaded to the graphics chip, away from the main processor. Since then, the trend has been to offload as much of the work away from the main processor, freeing it up for other things, by adding new instructions to graphics chips. The Central Processing Unit, is not really very central anymore, and many components can now speak to each other without having to go through the CPU at all.
Its not so much the second graphics card and SLI technology, but rather, the implementation of PCI Express. PCI-Express has risen to be the industry supported successor to the PCI bus which has essentially remained unchanged since 1994. The AGP port extended the life of PCI was relieving the bottleneck of video data, but even that now has reached its limits. PCI-Express sounds good on paper, ir replaces the parallel PCI bus with a host of serial busses that each connect individually to one device. Each device is guaranteed of having its own bandwidth, since it no longer needs to share with other devices on the PCI bus. PCI-Express also has much greater bandwidth.
However... you cannot have a PCI-Express bus with an AGP port. Furthermore, the motherboard chipsets that support PCI-Express are currently only available for the Prescott Intel processors, which have been dubbed as lemons. The Athlon64 is really the only option currently if you want PCI-Express. I think this is a very bad move on Intel's part, as they deliberatly limit supplies of the 865 and 875 Intel chipsets forcing manufacturers to switch to the 915 and 925 chipsets for the Prescott processor. It also makes redundant that brand new AGP8x graphics card you just bought and forces everyone to buy not just a new graphics card, but new RAM as well, since these chipsets utilise DDR2 RAM.
If you are thinking of upgrading, I would advise against it until the market settles down again. You could be buying junk.