Active D-Lighting makes adjustments to the exposure in order to reduce blown highlights and eliminate blocked-up shadows. In a scene with very bright areas, very dark areas or both, it will help balance out the scene to be closer to what your eye perceives. The various levels apply it to a greater degree for more contrasty scenes.
If you shoot JPGs, the the results are "baked" into the image. Reversing it requires manual adjustments in post processing that may not be 100% effective.
If you shoot RAW (NEF) images, the effects are applied if you use Capture NX2 as your RAW processing software. You can adjust or remove ADL in CNX. If ADL is off in your camera, then you cannot manipulate it in CNX., the option won't even appear.
If you use other software to process your RAW files, ADL is ignored. So you can turn it off.
Some like ADL, others do not. It is a personal preference.
I generally leave it on Low as a default setting. This maintains the ability to adjust it further in CNX and for most of my images I like the look. This also seems to be the one setting that allows complete reversing of the effect when you turn ADL off in CNX. (Other setting seem to have a subtle impact on the "base" image.)
I generally turn it off when shooting flash, especially portraits or studio set-ups.
If you shoot JPGs, do some experimenting to see if you like your results better with or without it. There is no way to just turn it on or off after you've taken the shot.
If you shoot RAW and use Capture NX2 to process those images, leave it on Low for maximum flexibility, except in specific circumstances.
If you shoot RAW and process you images with something other than CNX, turn it off.
One last note, ADL only works in Matrix metering mode. It does not do anything if you are shooting with Center-weighted or Spot metering mode.
In View NX and Capture NX, you can apply D-Lighting, which is a bit different than ADL. This can be applied to both JPG's or NEFs (or ever TIFs in CNX). It also can be applied selectively, as well as globally. However, the effect is not the same as ADL applied in-camera.
ADL is an instruction in the NEF, like most other camera settings. Only NIkon software reads it.
There is some evidence that using ADL does have an effect on the exposure itself. Eric Bowles did some testing and determined that with ADL set to low, the effect on the exposure is virtually undetectable and therefore reversible in Capture NX. My experience is the same, so that's generally where I leave it by default.
Again, if you aren't using CNX to process your RAW files, you might as well turn ADL off.