Yeah, it all really depends on the server you are playing on. For the record, Storm Haven now has 2 fully playable SoF leveling zones (from level 1 to 40ish between the 2), and I will be starting the next one very soon. Our SoF zones are fully customized though and do not reflect what is on Live. ChaosSlayer is right that it will probably be a while before either of the official databases have any playable SoF zones, but if the packet collectors ever get running again, it should start filling in content pretty quickly.
I think there may be some other custom servers out their using SoF zones, but the majority of all playable content still remains in Titanium compatible zones. I do know that at least some servers are working on SoF content or have wanted to add it because some have PM'd me asking questions about it.
Even if you are playing on a server that doesn't have SoF content, there are still a few advantages over using the normal Titanium client. Here is a small list of features that SoF includes that Titanium does not:
1. 8 more bank slots for a total of 24 bank slots
2. Group member location markers on your in-game map (if you turn it on)
3. 60 page spell book (Titanium book is only 50 pages)
4. Better game lighting so you can actually see when playing human/barbarians/etc.
5. New Drakkin playable Race
6. 80 new zones
7. 250+ new weapon models
8. New Power Source Slot
9. Heroic Stats and Resists
10. Client handles level 70-75 properly, where Titanium skills do not show up correctly.
11. 600+ New Item Icons
12. 113 new NPC Races
13. 10 Character Slots at Character Select as apposed to 8 in Titanium
And more...
There are still a couple issues with the SoF client to bring it 100% up to the functionality of Titanium, but it isn't anything game-breaking. Here is the thread that tracks SoF development status to catch up with Titanium:
http://www.eqemulator.net/forums/showthread.php?t=27429