Synthehol is a chemical variant of alcohol. It appears to have the same taste and smell as "real" alcohol to most individuals, but none of the intoxicating effects associated with alcohol. This being the case, why drink it?
Gene Roddenberry spoke about the benefits of Synthehol in an interview with CBS. Essentially, you get all the positives of alcohol consumption with none of the negatives:
"We have invented, for the bar-room, something called Synthenol [sic], which acts just the same as alcohol, makes you feel that you can be a lover, or wise, or all the things that alcohol does, but with the...it's only temporary. You, with a force of will, you can shove it aside and you're as sober as you ever were"
- It is intoxicating but these effects can be 'easily dismissed' (by adrenalin, see below) if you need to return to duty.
- It's non-toxic and non-addictive.
- It looks, smells and tastes similar to alcohol, to all but a connoisseur.
SCOTT : Synthehol?
DATA : Yes. It is an alcohol substitute which is now normally served aboard starships. It simulates the appearance, smell, and taste of alcohol, but the intoxicating affects can be easily dismissed.
The Star Trek Encyclopedia entry on Synthehol seems to draw heavily from the same quote;
The semi-canon "Star Trek : The Starfleet Survival Guide" mentions that the effects of synthehol are 'neutralised' by adrenalin, therefore anyone thrust into a dangerous or life-threatening situation would become instantly sober:
"Synthehol, because it is easily neutralized by low volumes of natural adrenaline, poses far less risk to a patient."
Many people -- myself included -- enjoy the taste of alcoholic beverages, but don't like to become intoxicated. Synthehol would allow us to drink more of them without suffering those consequences (or rather, without losing the ability to just shake them off at a whim).
Further, even when "off-duty" we spend the vast majority of our time watching Starfleet personnel on board active starships/space stations. How many times have you seen off-duty personnel being called to duty due to one emergency or another? Just like real world naval vessels, it's quite likely that Starfleet personnel are prohibited from drinking alcohol even off-duty while serving aboard an active ship, since even off-duty personnel can be called to duty in an emergency at any time (and yet even when awoken in the dead of night, they still seem to find the time to get into perfectly pristine uniforms, no matter how urgent the crisis...). Synthehol allows starship/space station personnel the enjoyment of drink, but still allow them to readily dismiss the effects and return to duty at a moment's notice.
That being said, for those who do want to get truly intoxicated there still are options, including Romulan ale (which more than once has been mentioned as being genuinely intoxicating -- not sure if it can't be made with synthehol, or if it just isn't). And while I can't cite specific examples right now, there have been times when we've visited planets and observed seemingly intoxicated patrons at bar establishments (pretty sure there's more than one example in the background in the episode where Picard is kidnapped by the artifact thieves, for example). The point being that while Starfleet personnel on board starships might be drinking synthehol, it seems the "real deal" is still readily available to those who want it (at least while they're not serving on a starship, anyway).
They do have real booze in TNG and DS9, as Miles O'Brien can attest, usually for off-duty. Picard shared a bottle of the real stuff with Scotty ("who do you think gave it to Guinan?"). On the original series Bones had his own recipe that he would "prescribe" to Kirk. There is also a black market for Romulan ale. I would imagine that in the Star Trek universe the negative health side effects of alcohol would be more easily treatable, so alcoholism isn't as much of a problem.
From my memory of the Second Edition Star Trek Encyclopedia: Synthehol is an alcohol substitute invented by the Ferengi. It is functionally and flavorfully identical to alcohol with one difference: the effects of intoxication can be literally willed away. Although the exact mechanism for this drunkness-dismissal is never directly revealed, it was implied to merely require an internal act of willpower equivalent to making the decision to no longer be impaired.
Synthehol-based beverages were originally served by Ferengi prior to and during negotiations so as to allow them to drink heavily, thereby encouraging their soon-to-be opponents to do the same. When the time came to commence with deal-making, the Ferengi would suddenly be mysteriously sharp and unimpaired, while their opponents would be sloppy drunk and at a distinct disadvantage when it came to negotiating. Eventually this fact became widely known, nobody was willing to drink with Ferengi prior to striking a deal, and the Ferengi realized they could make more profit selling an alcohol you can choose whether to be intoxicated by than they ever made using it to tip negotiations in their favor.
Although I recall at least one non-canon Star Trek novel (Balance of Power, by Dafydd ab Hugh) making reference to a synthehol still in a student dorm room at Starfleet Academy, my impression from canon sources was that all synthehol-based drinks were produced by Ferengi or their agents and the secret of its production was closely guarded.
The reasons for synthehol's adoption over alcohol by Starfleet are obvious, however, as the question of allowing alcohol on-board sea-going vessels has presented a problem for terrestrial navies as long as they have existed. Allow alcohol, and morale increases dramatically, but so do the problems associated with intoxicated sailors on duty. Morale is a serious concern for the commander of any vessel, as Synthehol allows Starfleet to sidestep this problem neatly.