Soft gluon resummation of Drell-Yan rapidity distributions: Theory and phenomenology