We've been on the Pride out of Baltimore and the one thing is that anytime you're cruising in the Atlantic, it can be a bit rougher than sailing out of Florida. However, with that said, April is usually not a rough time of the year, so you should be okay. And the aft part of a ship will feel less motion than the front, so it's a good location.
Having been in the Navy for 6 years myself, I can relate to your husband. But as they say, once you've gotten really seasick, like most sailors do, you don't get seasick again. So I know he'll be fine.
As for you, I agree with Trip; ginger pills seem to work really well for most people with no ill side affects. You can get them cheap at any drug store - just make sure you start taking them about 3 days before your cruise.
The only thing is that if it does start to get rough, Carnival is notorious for getting those stabilizers out not as soon as other cruise lines. They create drag, so it costs them more in fuel when they're deployed and Carnival likes to keep it as cheap as possible.
As for the Atlantis tour, we've not done that, so I can't help with your questions concerning the tour.
As for the ship itself, not one of our favorites, but it is better than the Miracle, which is its sister ship and was in Baltimore for a while. It's not bad and the food was okay, but it's nice for a first cruise. Spend the extra money for a dinner at the steakhouse - we thought it was worth it.