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:
- Insert a CD or DVD almost all the way into the drive.
- Remove the disc.
- Insert the disc fully into the optical disc drive.
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.