I would say weather will be a minimal factor between May or September. The one advantage May will have is a longer time of daylight.
For the Sierra’s, as others have said, the views change so a nice seat in the SSL is best. After that there is the isolated section of the Colorado River just west of Grand Junction for which the south side of the train is by far the best. If you come from the west and get off in Glenwood Springs, you won’t go through Glenwood Canyon as it starts just east of the station. If you are riding coach, much of the time the crew has been assigning seats, which makes picking a side of the train much more difficult. For the best selection you might want to consider backtracking to Emeryville and boarding at the origination. If you don’t get the seat you want, consult the car attendant or conductor after getting underway.
Hotel wise, the Hotel Glenwood Springs is about a mile walk from the station as it’s located on the north side of town. Walking is doable, but a hike with luggage. I haven’t stayed there but it looks fun as it has a water park and is next to the gondola to the adventure park on the mountain. I’ve also heard good things about Iron Mountain Hot Springs, which isn’t far from that hotel. There is also two Marriott properties, a Courtyard and Residence Inn if those are attractive to you.
There are many good restaurants in town. Juicy Lucy’s, The Pullman, Colorado Ranch House, The Italian Underground, Smoke Modern BBQ, Slope and Hatch, Rocky Mountain Pizza, The Daily Bread, and Rosi’s Little Bavarian all have very good food and there are many more. Google map reviews hold pretty true and are reliable.
If you are up for seeing something different than just Glenwood, the RFTA busses go up to Aspen with the possibility of stops along the way (Carbondale and Basalt both have interesting towns to walk around) or riding an express bus.
