First of all, understand that support requires a completely different mindset that any other role in the game. Jungle is the closest, but even there you'll see some drastic differences. You want to feed your team every advantage possible, and this often comes at the cost of sacrificing things you would normally hoard in any other position. Your goal is not to "hard carry" a game, but to create situations for your team that will lead to a win. That sounds like the same thing, but it really isn't.
There are four sub-roles for support: tank, peel, harass, and sustain. All supports are usually some combination of these, and often the roles you most need to play will vary as the game plays out. For instance, a Nautilus support will at first be a hard initiator-tank, but may very well be needed more for peel later. Obviously some excel at a particular role more than the other roles and will carry that specialty throughout the game (hello Soraka), but the idea is to remember that very likely at some point through the game you may have to do all of these, regardless of your choice of champion. I tanked a tower dive last night as Nami, for example, where we got a double kill.
Specifically for Sona, things are a bit different than you'll see from some other supports. She is a powerful harass-style support with some decent sustain as well. Her weakest role is tank for obvious reasons, but her peel is relatively weak as well. She is a fairly safe pick and good in a composition with high burst and at least one decent frontliner. I also like to take her when I have more than one auto attack based teammate, because Ardent Censer's strength is multiplied when you apply it's buff to auto-attackers (and I ALWAYS buy censer on Sona, mostly because it helps shred neutral objectives faster where everyone is auto-attacking anyway).
If your team is relying solely on your ult for initiation, then your team has drafted poorly. In an older age where supports were basically just throwaways anyway this was fine, but today the auras and buffs Sona provides in an extended fight are very powerful... meaning it's much better for Sona to backline and peel/follow-up with her R rather than to dive and ult as she would normally do in lane.
As an additional note for support in general, don't necessarily feel tied entirely to your lane partner. You can roam and help other lanes just as if you were any other role, and in some cases I find it more beneficial to assist mid or the jungle for a few minutes to build/extend an advantage there.