Let's see... settings... good, bad, useful...
Midnight
The Good: You like Tolkien, kiddies? This setting has the feel of Middle Earth with the serial numbers filed off. Great flavor. The mechanics work to make a low-magic system that lets the PC's perform heroic and extraordinary feats without turning magic into just another commodity or tool. It's well-supported for a 3rd party setting, and the production values are mostly solid, other than the binding problem on the first run of the core book. FFG's editing is solid, and the artwork is much more "classic" than "dungeonpunk".
The Bad: Imagine if Aleksandr Solzhenitsyn and H.P. Lovecraft got together to write an "alternate universe" sequel to the Lord of the Rings in which Sauron triumphed. That's about how depressing this setting is. There isn't much hope that the PC's can make a difference- and if they do, quite a bit of the theme, and most of the setting's "long term" potential is lost. Low magic ain't everyone's cup of tea... and you know, it's nice to get rewarded with a nice haul of treasure, magic items, royal titles and popular recognition from time to time. You'll seldom see any of these in Midnight- the more you triumph, the more likely it is that you'll be ostracized, not to mention hunted down like a dog by Izrador (AKA Sauron)'s minions. The setting was enjoyable for about two months... after that, meh.
The Useful: Channelers- a great low-magic and general "utility belt" class that I've found even works in normal D&D games. Heroic bloodlines- nice if you'd rather give focused, inherent "superpowers" than gobs of magic items. Lots of good ideas and plot hooks. Interesting magic system that modifies the core system in simple but flavor-changing ways.
Arcana Evolved/Diamond Throne
Good: It's new but familiar- all new classes, new spells, new races, modified feats... but the same d20 system under the hood. The setting provides lots of twists- no alignment system, so "good and evil" isn't "cut and dried", giving a greater moral ambiguity. Assumptions about divinities and the source of magic are out the window, in favor of a new pseudo-platonic metaphysics of runes, rituals, ceremonies and sigils that program reality. The humans, for the most part, aren't in charge- Giants, Dragons, and Harrid rule the continent. The Giant/Dragon struggle provides lots of intriguing possibilities for both millitary and philosophical conflict. Religion takes on a different role in this setting- the nature of the gods is highly ambiguous, and nobody really knows for sure what happens after death. In many ways, it's a "mindscrew" setting, not unlike Planescape or Rifts. If that sort of thing is your cup of tea- you'll love DT.
Bad: It's just too weird for some players, who would rather have elves, dwarves, wizards and fighters than verrik, sibeccai, runethanes and warmains. The lack of any system of morality in the rules (unlike core D&D, World of Darkness, or Star Wars) can be both liberating- and difficult for the gamemaster. Also, it's the sort of game where the group is going to need more than one copy of the core book, since it stands apart from core D&D, and rules references (especially for spellcasters) will be frequent. The setting is in many ways sparsely detailed- though this could, again, be more of a feature than a bug, as it doesn't waste time on irrelevant detail and provides just about everything a DM would need "out of the gate".
Useful: Where to begin? From the classes, the magic system, the races, the redesigned feats, all the easily "rippable" setting concepts... AE is one extremely juicy product, great for spicing up many games...
Forgotten Realms
Just as an aside, I thought I'd mention my peeves about the Realms- and where I think the setting's greatest strength lies. I've never had an issue with the powerful NPC's, the metaplot, the magic level... my issue is that the way most DM's I've played with present the Realms is positively BORING. They don't do there homework! If you actually RTDB, you discover that the Realms- and each individual country and region- has unique quirks that should be taken advantage of. Rather, most DM's I've played with just pick a place somewhere along the Sword Coast to drop in a module, with little attention to the setting itself. On top of that, if you throw the gates open for characters from anywhere and everywhere, coming up with coherent party goals and flavor can be difficult.
On the other hand, I like the Realms. But if you're going to play there, you have to focus- pick a single region, research it until you know it like your hometown, and run the game there. For instance, take the "East"- Thay, Aglarond, Rasheman, Narfell, The Great Dale, Thesk, The Hordelands. What an interesting place! A totalitarian empire run by evil wizards endlessly scheming against each other... a heroic realm of men and elves ruled by a mad queen who also happens to be the only thing standing between Thay and world domination... a country ruled by matriarchal masked witches who commune with nature spirits and keep armies of fearsome berserkers... a merchant federation under the thumb of a shadowy cult, and filled with immigrants from mystical lands to the far east... a fallen empire filled with demon-haunted ruins... a woodland realm of xenophobic druids and lost treasures... plane-shifting elves who live in a pocket dimension... great magic, fabulous wealth, mighty conflicts, and hardly a drow in sight...
Doesn't that sound like a fun place to play? It does to me.
If you're going to do the realms right, pick a corner, and center the game there. That's the way to play... think of the realms as a collection of little interconnected settings...