Outside of rrigging a pump and pipe system and shooting water through it the way SINGIN' IN THE RAIN is done usually, I have also seen it done very effectively with rotating gobos and glitter sprinkle. The gobos are slats of light that create a moving light as if rain is falling when they rotate. Add to that either people in the flies or cans with sifters that sprinkle glitter and the light from the gobos will reflect off the glitter and create a rather magical rain. When this is done in SINGIN' IN THE RAIN it is the end of the first act, so you can easily sweep up the glitter during intermission.