The first part is the "active" which in some cases may reduce exposure to protect against blown highlights. Because exposure is reduced, this "setting" is obviously present in the raw data and therefore will be part of the output file regardless of what raw converter you use. It seems, however, that this only comes into play with ADL set to high. Medium and low settings do not seem to modify exposure.
The second part is the "d-lighting" which is Nikon's name for their proprietary shadow recovery. This is a feature of raw conversion and therefore only applied if you use a Nikon raw converter. However, you can apply whatever shadow recovery tools exist in your raw converter of choice to get the result you want.
Is this feature necessary?
For serious shooting, I'm always checking the histogram, being sure to expose to the right without clipping highlights. In this case, I'll be able to do the "active" part more accurately than ADL, so in this case, I find it unnecessary and even detrimental, so I keep it turned off. I'll deal with shadows either with a custom curve or HDR during raw conversion.
For casual/snapshooting, or things like street shooting, where the moment might not present itself long enough for you to adjust exposure and pay close attention to highlights, this can be a useful feature. I keep it turned to medium in my snapshooting bank.
Despite what some others have said, this does significantly slow down the cameras I've tried it on (D300, D300s, D7000). Possibly today's cameras are faster at processing it. Because of this I don't use it to shoot birds, and it probably creates a similar problem for sports shooters.