Dwarves prefer booze, so a well isn't strictly speaking necessary to keep them alive.
However, a water source is nice for cleaning and happy thoughts. A few items of note, though..
The well should be more than one Z level deep. That is, if it's built right on top of a water source, without a Z level in between the well and the water, it apparently generates unhappy thoughts.
Aquifers make the best water sources for wells. They are inherently secure, and trivial to channel from the aquifer into wherever you want it to be in your fort. If you do it right, you don't have to drain it off the map, no matter where it comes from.
So, my initial reaction is... find your aquifer, channel from it to your fort, put in a diagonal pressure reducer if necessary, and voila, there's your water source. If the water is on Z 98, build your well on Z 100. (two up) and you're golden. Also, if you can use all masterwork parts of the well, with a mechanism/block made of native gold rock or native platinum rock, a well can be a great source of value as well as happy thoughts.
Finally, it's not necessary to designate the well a water source, nor make it a meeting place, nor any of that. Dwarves will know instantly that it's a water source. YMMV, but those are my comments on the subject.