As you point out they're all part of the same family, and in part are an evolution of Microsoft's mapping offerings. However, some of them do provide things that the others do not. There's no dependency between them however.
To start with the easy one, Bing Maps is basically the new name for Virtual Earth. The technology and product hasn't really changed, just the name. When the Bing brand was introduced Live Search Maps became Bing Maps, and Virtual Earth became Bing Maps for Enterprise. Neat, huh?
Bing Maps is essentially the Google Maps compete product. It provides a JavaScript mapping API with a number of Web Service APIs behind it for geocoding, routing, etc... Of course it does have some unique features, like Bird's Eye oblique view, etc...
MapPoint is a much older product - I believe you can still get bits of it on CD-ROM! It does provide some services that Bing Maps does not though, such as upload of addresses for batch geocoding, and making them available via a spatial (where's my nearest?) SOAP web service. If you have your own datasets that you want to integrate then you may want to look into MapPoint a bit more.
If all you want to do is show maps on a web page, put a few icons on, search for places, and plot routes between places, then Bing Maps is probably the one you want. The thing that Bing Maps doesn't currently do well is giving access to the raw web services behind it. If you need to do multiple calls to a spatial API (eg routing) without going through the JavaScript API then you may have to use the MapPoint Web Service.
Regarding SQL Server's spatial features. It's used in the backend of Bing Maps, but is not exposed directly to end users in any way. You don't need it to use Bing Maps either of course.