Pros:
1. Looks really nice (professional; almost holographic at certain angles)
2. Remembers where it left off so you can re-burn later or add more text/graphics
3. Text and vector-style graphics (like logos) look great
Cons:
1. You have to do 2 burns to get it dark
2. It takes a long time (20 to 30 minutes per burn)
3. Photos look like crap
4. You have to buy special LightScribe discs (expensive compared to normal discs)
5. Monochrome (like black and white, but more of a sepia tint)
I only use LightScribe for special projects. When I do client's computers, I make a restore CD and create a personalized LightScribe recovery disc. Adds that extra "touch". I also use it for movie projects, like when doing people's family videos. They love seeing their name with a little graphic on the disc. Regarding software, SureThing is the best software to go with imo, like corkyg said. The CD/DVD Labeler Deluxe software is $25:
http://www.surething.com/ST/
If you're on a Mac, disclabel from SmileonmyMac is the way to go. It runs $30:
http://www.smileonmymac.com/disclabel/index.html
The alternative to LightScribe is printable discs. There are a couple inkjet printers out there that double as CD/DVD printers. You have to buy special printable discs, available in white or silver, but they cost a lot less than the LightScribe discs. Basically the white ones have a paper-like surface that you print on, which makes them look as good as photos would on matte photo paper. You can do any color you want instead of just monochrome like LightScribe is. I backup all of my software discs this way and print the keys on them for easy reference (it's so much nicer flipping through your software collection and having easy-to-read titles with the CD keys readily available). You can pick up a printer at Staples - the Epson R220 is the cheapest model at $99. It's your basic inkjet printer with the added capability of being able to print on printable discs (not normal discs).
I use both LightScribe and printable discs. Both have their uses. Normally I prefer printables, but when I want a disc to be really "special" I typically use LightScribe. The nice thing about having a LightScribe drive is that it doubles as a regular DVD burner. BenQ sells a great drive (currently the DW-1655, I think, for about $40 on Newegg). The nice thing about having a disc printer is that it doubles as a normal inkjet, so you can print out documents and photos and whatnot. Be warned that Epson doesn't have the best text quality on normal paper, so if you're planning on using it for polished school reports you may want to consider a different model like the Canon Pixma printers with the CD tray hack.