If you don't have a CD or DVD handy you can invoke the sound by clicking on System Profiler > Hardware > Serial-ATA.
The solution, according to an Apple knowledge base article, is to reset the optical drive as follows:
The optical drive is now reset and the noise should no longer be present when you start up or wake the computer.
A video of the procedure is also available and it worked like a charm for me.