If you choose to play as a celeb, you'll gain the most in terms of free drinks and entrance into the highest-end clubs and lounges. Becoming a celebrity is somewhat of a challenge, however, and involves a lot of schmoozing with other celebs (perhaps even becoming involved with one) and doing damage control when the paparazzi catches you in an unfortunate situation. Celebrity status is measured by little stars (out of five) that appear above your Sim's head (and also above other celebrity Sims' heads).
Meeting and talking with other celebrity Sims (before you're a celeb yourself) is especially hard--they're generally unimpressed unless you're rich, famous, or particularly charismatic. At the lower levels of celeb status (less than three stars) your Sim will still be mostly ignored by higher-level celebrities and be unable to get into the most exclusive venues. Once you reach the higher levels (four or five stars) of fame, however, you'll not only be able to get into clubs and drink for free, you may even be able to wrangle some extra cash from the club owner--a professional partier, if you will--just for showing up.
The only annoying thing about being a celeb, of course, is all of the publicity and the following you'll have. If your Sim does anything worthy of paparazzi photos, you'll probably end up spending several days doing damage control. If you become too much of a laughingstock (think Lindsay Lohan), then club owners and bouncers will rescind your open-arm invitation and other celebs will snub you. Yeah, being famous is just so hard!
If you'd rather not be a paparazzi-followed socialite, you can also gain some fame and notoriety by starting your own band and playing gigs at various venues. Many of the clubs and bars feature stages where your Sim's band will be able to perform. Your Sim can play anything from electric guitars to piano, bass, or drums.
Being in a band is a little harder to do than just, you know, being paid to be famous--your Sim will need to socialize with industry members, practice with your band members, and play gigs. It appears that you can only get gigs through the Sims 3's "opportunity system"--in other words, they pop up at random and there's no way for you to really hustle for jobs.