HDR (High Dynamic Range) is one of the latest trends in photography but as the post-processing itself (read this article), the art of merging several exposures into one to expand the dynamic range has been around since the 19th century. It re-emerged thanks to the popular work of photographers like Trey Ratclliff and others, as well as the increase of processing power of cameras and computers alike. Like all upcoming trends in any art, is natural to see an often overuse of technique to the point where some images have what I like to call a "Chernobyl Radioactive" look with seldom constructive critique. Personally, HDR was something I used to do once upon a time and stopped using frequently as I learned how to use post-processing techniques make use of one frame only, due to the fact that HDR is quite time consuming when the intention is to develop a natural looking photograph.
Now, if in most cases we can achieve a natural looking exposure that makes use of our camera's entire dynamic range, when is HDR useful?
TOOLS & USES OF HDR
Let's name some situations where IMHO, HDR is useful:
Architectural Photography (Interior): If we want to specialize in doing interior photography for real state companies, HDR could be an incredible tool as interiors have an extremely wide dynamic range of light. Again, is important to take care of not overusing the effect.
Architectural Photography (Exterior): If we would like to take a photograph of a building against a sunset, most probably even the camera with the greatest dynamic range (nowadays the Nikon D810) will fall short with only one frame.
The use of HDR applies as well to anything that we consider short of our required dynamic range for an image. If you wonder what is the limit where the image goes from being a well done job to become radioactive rubbish, is up to the viewer and the photographer. Even though there is a generalized view of how a natural image has to look like, is strongly subjective.
Regarding the tools, there are several options nowadays in the market. We have the one I mentioned, Photomatix Pro, which has a very simple interface and some presets (which personally I never use). It is sold for $119 in its full version. The other two main options that are out there are Nik Software HDR and Topaz HDR. Last but not the least, we can make use of Photoshop and layers and masking to stack and merge all the exposures into one.
No matter what method do we use, it is highly advisable to sharpen the images as the process itself takes away clarity and sharpening, leaving most of times a flat result that lacks of contrast. The method I always preferred is to use is Unsharp Mask in Photoshop, but there are several options available to do so in the Nik Software bundles and Topaz bundles to increase sharpening and microcontrast.
The choice of using HDR or not is yours: like cameras, techniques and software, is just a tool. What matters is your idea, how valid is it to your eyes and the viewers of the photograph and how strong your impact is.