There is no charge for the Starwood.
An upgrade using the card is not guaranteed, except, on occasion, you might get a card good for certain dates, and the card will say something to the effect of presenting it on your next stay for a one-level upgrade.
But, I can say that in my experience, if there was availability at the S/D, I have always gotten an upgrade when I show my card.
There are many other perks, too--I will be staying at the Dolphin next week...if I pay with my AmEx and stay for 5 nights, I will earn 2 free Friday night stays.Of course, you can always earn points to use toward an upgrade.
I have enough Starwood Points accumulated (from previous vacations) that I have earned an upgrade when I choose to use it for a few nights.