We use fusetalk at the school I work for, we actually have several forums that get used for differant things.
It's kind of been mentioned before but I'll help to clarify.
All forums are generally the same thing, a database in which all these posts + membership + polls + etc, are mearly entries within the database. And a server-side scripting language which takes that information from the database and displays it on a webpage.
FuseTalk uses Cold Fusion (CFM) for the server side scripting language and works with a number of differant databases such as MS-SQL, MySQL and even Access.
The downside to Fusetalk is you have to pay quite a few fees to use it, you have to pay for a license of Windows Server than on top of it you have to run ColdFusion server than you have to buy Fusetalk. Once more if you want to be able to use all of it's features or edit the source (like the folks here at anandtech do) you have to pay for even more expensive licences.
I use Snitz forums (asp) along with a MySQL server because I didnt have to pay for either, the only thing I had to pay for was the server to run it on which I already had. I also like Snitz because there is a ton of 3-rd party code free floating around that gives additional features/functionality (because it's open source). Feel free to visit my forums (link is below) if you want to see what it looks like.
Another really common forum software is PHPBB which is also very powerful and expandable.
-Spy