Where to start?
well...
Mods:
Diplomod v3.4
Dale's Opus Magnus (big work) is really good at making the AI actually usea ll the features available to them, and vastly improves on the war-war-war attitude of CTP2
MedMod2 - A Call to Arms:
Anticipated as the saving grace of CTP2, it just went public, it is the "sequel" to the CTP MedMod, that greatly enhances gameplay, adding loads of SLIC features, elite units, militia units, and a better AI (Oh yeah, and also includes Diplomod). See the related thread.
Get it
here
Cradle of Civilisation
A mod by Hexagonia, designed (much like the original MedMod) to enhance the game in one particular age - in this case the dawn of you civilisation. The game begins in 7000BC, and the tech tree is vastly extended. Also features militia units, the wonder-units code, which allows single-build units that have the Alexander-style General effect.
See the related forum thread (the version 1.07 thread has more detail than v1.1)
Get it
here :
Username: Hexagonia
Password: Hextapul
Alto-SciFi Mod
Slightly weird mod by BlueO. It includes: A new tech tree, to incorporate two "Amazon" governents, and nine new "Amazon" units, like the legendary female warriors. Also, three war walker units, a city expansion mod, so cities grow out from their single tile in the centre, and use up the terrain around the city. And more! - A frenzy AI, that winds up going berserk, attacking everything on the war to your capital. Sounds good, but is easy to conter attack, as it leaves cities defenceless

. Lastly, includes an alien influence thing that I never really understood, but means that civilisations can be taken over by aliens, and turn barbarian.
The bits of it are all available separately from the URL=http://apolyton.net/ctp2/files]Apolyton CTP2 database[/URL]. Just search for everything by BlueO, and bung it into one scenario structure. Its a very different game, but worth a try a few times.
Scenarios:
Three types so far: Realistic historic, broadly historic and set-ups.
Realistic:
SPQR:
Actually two scenarios by Hannibal Ad Portas. the first is the Roman Dacian war, and the second is the Second Punic War, where you play as Carthagnia, trying to survive the onslaught of the Roman attacks.
These are probably the best scenarios around at the moment.
WW2
Sadly the only one around is the Activision one which isn't great, but may be worth a try.
General:
Various:
Im sure there are quite a few Dawn of Civ scenarios on the Apolyton database (
http://apolyton.net/ctp2/files)
These are nowhere near as well crafted as SPQR or a game played with Cradle.
Set-up:
What I really mean by this is scenarios that are designed to make the game play differently, rather than acordnig to a storyline or history.
OCC
One City Challenge. A scenario where it is not possible to get more than one city throughout the game, but ever enemy city you conquer is destroyed. It is very difficult. The version on Apolyton leaves three ways of getting an extra city, but for a harder game, download it
here.
TO COME:
Mars 2020
A scenario I started a long time ago, I plan to completely redo it, so wait around for that.
Lord of the Rings
Two people are working on LOTR scenarios, but these might be a long time coming to fruition.
Dinotopia
Based on a map that is available on Apolyton, some people were talking about a scenario based on the picture-book. It seems unlikely that it will ever appear.
As this is almost a complete list of mods and scenarios, its not like Civ2 where you have to choose the best ones, try them all. Better still, make your own scenario or mod
Ben