With Carnival the "Fountain Fun" card is a separate card you need to carry in addition to your Sign And Sail card. You can buy one at most of the bars on embarkation day. I don't recall the prices but adult cards are more expensive than childrens cards.
Personally I think it is not a good deal for adults unless you drink a lot of soda. The card is good for the person it was purchased for, and it is for glasses of soda not full cans - although the soda in the glass is poured from a can.