My husband and I moved to the area about 2 years ago from near Boston--we really, really love the Triangle and our lives have changed in so many positive ways since coming here.
So far, we have lived in Carrboro, Chapel Hill, and now Durham, where we just bought a house in Duke Forest. We like all of them. They all have a certain laid-back energy, if you know what I mean. We really liked being in Chapel Hill because we enjoy going out to see bands, and there's a lot of opportunity for that there. We loved Carrboro because of the villagey, activist feel, the presence of Weaver Street and the Farmer's Market. It's super family friendly (in fact, watching families in Carrboro is probably what convinced us to finally have a kid).
Now, we're in Durham, and we suspected when we moved down here that we would end up buying in Durham. Chapel Hill/Carrboro was too expensive for us, Raleigh and other towns in Wake County were too sprawly (except for inside the beltline, which seems fantastic, but was also out of reach).
We really love Durham--it has a great vibe, and a supportive community of people who also love Durham passionately. We recognize the city has its share of problems, but there's a lot of soul here that makes us want to see it succeed. There's also great dining, outdoor music, hiking, baseball, etc.
As far as neighborhoods, I'm really only familiar with Durham neighborhoods since I spent so much time researching them when we were house hunting. I really love Watts-Hillandale--it's very neighborhoody, with people out walking with their families, neighborhood events, block parties, etc. You'll also find that same kind of vibe in Duke Park and Trinity Park. These are all organic, old, established neighborhoods--your neighbors are likely to be artists, professors, Duke staff, etc. Hope Valley Farms is a popular neighborhood, but we didn't like it because it's a cookie cutter development--the houses are too close together, and all look the same. Woodcroft is another development--we liked it better since it's older and the landscaping has had some years to mature.
We ended up in Duke Forest, which is I guess a pretty desirable neighborhood. We like it, but it's not very neighborhoody--we've only met one neighbor so far, and we've been there since February.
Two drawbacks to Durham are crime and the schools. We haven't experienced either of these so far, and I kind of think that we'll be able to avoid crime if we don't go looking for it. Schools are a different matter. I'm not sure what we'll do when we have a school-age kid--maybe they'll have improved in five years? here's hoping...