I can only give you an overview of my own personal experience with the process and hardware I used. Alot of this is copy pasted from my posts on the thread related to this on macrumors.com. Here goes:
I bought the ebay optical bay caddy linked here. Works great. Depending on your model, I would recommend using the "Optical Drive Replacement" guide on ifixit.com (here is the link to my particular model, the mid-2010 15in). Once you remove the drive, you have to do a bit of ad-lib in order to install the caddy.
The only issue with this caddy is that two screw holes on the outside facing part of the macbook case do not have a matching hole on the caddy. So the caddy is held in place by one screw on the innermost side. The screw hole that DOES exist on the caddy is actually not part of the caddy at all. You remove a little bracket from the superdrive and use the 2 screws it was secured with to attach it to the caddy in the same position.
It seemed pretty wobbly to me so I just took 3 pieces of electrical tape, folded each one on top of itself once, and stuffed them into the spaces where the caddy had wiggle room between the other components and parts of the case. Problem solved... the enclosure doesn't move one bit inside the case. I think it might even dampen the noise a bit since it keeps the metal from contacting other metal bits.
So, the important bits about the superdrive replacement:
BACKUP YOUR DATA: probably the most important thing you can do before attempting this procedure
If possible move your User's directory to a separate volume before doing the upgrade: this will make it easier to clone your data later on... for specifics see my question here
Follow an ifixit guide step by step and label your screws: i used one of those 2 week pill boxes and put labels on the days that corresponded the step number
use good screwdrivers: I highly recommend the ifixit bit driver set as they all fit correctly and lessen the chance you will cause damage or strip your screws
put your boot drive in the original HDD spot: otherwise you may have issues with sleep and hibernation
make a bootable copy of your installation discs: put the OSX install DVD on a thumb drive or a partition so that you'll be able to boot from it in an emergency without reinstalling the SuperDrive (it may be possible to boot from the superdrive externally but I haven't tested this)
for info regarding installing your superdrive in an external enclosure, check out my answer to this question
PROS
You have the option of drastically increasing your storage space or using a SSD to dramatically speed up your OS X experience.
My system boots from the SSD in about 15 seconds including 3 programs launching and about 7 or 8 menubar apps
All of my data is located on my original 500GB HDD and is separated from my boot volume in case of OS or disk failure
did i mention it's lightning fast?? :P
CONS
have to cart around an external superdrive enclosure if you want to use cds or dvds
a HDD in the caddy is definitely noisier than a HDD in the original slot. I have a feeling this could be mitigated by some noise dampening but I haven't really worried about it too much
you're pulling apart your MacBook so there is always some risk that you'll break something through user error