Good Rain Dance support is easy enough and a valid choice for Toxicroak who recovers more than from Leftovers with it.
All-rounders, Poliwrath and Politoed are both incredibly bulky and immune to water making them ideal Rain Dance setups as they don't risk your opponent taking advantage of it.
Poliwrath especially since Tyrannitar gets raped two ways to sunday by it and if you go with a mixed version even Hippowdon won't like STAB Hydro Pump's breathing down on it. Politoed is a fine semi-special tank capable of Counter as well.
Problem with Wrath will be that being also fighting type you gain an extra flying/psychic weakness but its very powerful in its own right and is an actual direct counter to the two big Sandstreamers. But hey you might like having a Frog duo.
Lapras is a fine RD setup too being able to even Thunderdance/STAB Ice Beam and Surf to boot. Same goes for Lanturn. But one has Stone Edge weakness while the other has a major Ground weakness and very low defense.
Offensive Rain Dance support comes easy in the form of Kabutops and Omastar. Both pack Swift Swim and are lethal physical/special sweepers respectively. Omastar in actual fact has the most powerful non-00ber water attack in the entire game. However they both have that nasty rock typing making them weak to a crapload of stuff.
For more defensive stuff. Gastrodon, Blastoise and Quagsire both setup Rain Dance quite well but problem is it doesn't benefit themselves that much. Although Blastoise is one of the most incredible special and physical tanks in UU. Mantine is an incredible special wall as long its kept away from Electric attacks and Rain Dance makes up for its average special attack shortcomings and can Water Absorb or Swift Swim.
If you want to extend your search to BL's. Kingdra also falls into the offensive category but without adding any weakness. Ludicolo is one of the biggest Rain Dance abusers out there falling into both offensive and defensive.
Theres also non-convetional Rain Dance users out there.
http://www.serebii.net/attackdex-dp/raindance.shtml
A few more non-conventional decent ones that I can point out are Mr.Mime who learns just about every support move in the game and Thunderdance. Qwilfish who can Dance, Spike and suicide with Explosion/Destiny Bond. Rotom who has all the tricks of a Ghost plus STAB Thunder but without opening you up to Ground weakness.
EDIT:
Reason I haven't taken Hail into account is because just about anyone who can setup Rain Dance doesn't mind Ice moves to begin with. Plus Toxicroak is a pretty decent Abomasnow counter anyway.