We created an iOS application just for that, called DMDJ. DMDJ features a sophisticated audio engine, as well as a number of environmental presets and music themes, aimed at improving the experience of a pen&paper session, by allowing DMs to create audio environments for their players. In short, main features include:
An audio engine, with complete randomization to minimize repetition. Therefore, the final result is extremely close to reality.
A large number of high quality environmental presets, music themes and SFX - the number increases with every update, according to users' demands.
Each environmental setting offers 3 intensity levels and day-night transitions.
Now, regarding your questions:
Has anyone tried this before, how did it turn out?
I've tried it both as a DM and as a player. As a DM, I was not the most organized person in the world, so it did not turn out well, as I had to stop the story to control my sounds (it was back in the CD era!) and the whole feeling of immersion was lost. However, I tried this as a player, 2 years ago, with a more organized DM (at least more organized than me!). The feeling was better, he used a combination of music tracks and some sound effects - however, it was still difficult to control the effects and they were quite repetitive, which brings us to 1 year ago, when we released DMDJ.
Did this add to the overall immersion without detracting too much from narration or what the players are actually doing?
In my case as a DM, the result was not good, at all, but this was mainly due to the lack of automation. In my case as a player it was better, but still not perfect. Since I haven't had the chance to play a session during the last year (still in search for a party nearby), I'm relying on user's reviews for DMDJ's ability to add to the overall immersion and, without wanting to do any shameless promotion, their views have been very positive.
Where did you obtain good sounds to use?
We used sounds from some of the major content providers for DMDJ, since we wanted content to be of perfect quality.
What other sounds did you use that added value?
I believe that the ideal combination is:
a) Environmental presets: these act like the overall sound effect of the scene i.e. a forest under heavy rain.
b) Music: this adds drama to the scene, when needed.
c) Situational sound effects, for specific important actions.
Teamed up with intensity levels, these 3 can definitely increase the immersion of your players.