It really depends on what you're wanting the music for: general atmospheric background music or crafting a personal soundtrack to your game.
If you're wanting to create a customized soundtrack for your adventures, you're probably going to rely more heavily on movie scores and video game soundtracks, since most of the tracks were already crafted to convey or elicit specific concepts or emotions. So, it's relatively easy to pluck a dozen or so pieces from a wide variety of soundtracks to create theme CDs, like "Battle Mix" or "Ominous Village" or "Festival" that'll fit whatever you need for a given gaming session. And, in my opinion, you almost want at least some of the tracks to be iconic, because you
want the players to get pumped by certain tunes, remembering the iconic scenes they're attached to and the energy/atmosphere they evoke, whether it's "Anvil of Crom" by Basil Poledouris or "Duel of the Fates" from John Williams or "Vampire Hunters" by Wojciech Kilar (okay, that one isn't really attached to any memorable scene, since that entire movie sucked, but still...).
If you just want ambient background music, a lot of the stuff - depending on your game (theme, tone, campaign setting, etc.) - from
Midnight Syndicate and
Nox Arcana makes for excellent background music. Some of the early stuff from
Mortiis (the Era I albums) is good for that, too. Especially the albums with Norwegian titles, since they're pretty much just a couple tracks that run for 20-30 minutes each without too much alteration in overall sound - as opposed to movies soundtracks which can greatly vary in mood/tone from track to track, switching from light, cheery and whimsical to loud and frantic to eerie and ominous in just a 10 minute period. Something I always found a little jarring while playing: being in the middle of combat and having a happy little Hobbit village tune come on. Kinda ruins the tension and dramatics of the moment. Pretty much any artist/group in the ambient or dark ambient vein would suffice.
There's also the staple of classical music options (Bach, Berlioz, Wagner, Mussorgsky, Prokofiev, etc.) and world music (Gregorian chant, Tuvan throat singing, shakuhachi music, etc.) and even non-musical ambient CDs, like thunderstorms or howling wolves or rainforest sounds. (The great thing about those ambient CDs is you can usually pick them up dirt cheap and they're pretty effective for conveying atmosphere when you have the curtains drawn and only the bare minimum of lights on.)
I've used pretty much all those at one time or another.
My current project is trying to put together a database of sound effects so that, at the click of a mouse, I can pipe out the sound of a door being bashed to splinters or a savage, guttural snarl of some monstrosity or the clashing of swords or even the famous "Wilhelm scream" of someone dying.
Just little touches to add that element of cinematic flair to my games.
