We live in Fayetteville and happen to like it. I've lived in Memphis, Atlanta, DC, and Hawaii in addition to various small towns, so I have a bit of experience with other areas.
After my wife and I started a small rental property business we discovered that there are a lot of nice neighborhoods in the city and most are quite affordable by big city standards.
The city is large enough to have a good number of quality amenities but small enough that traffic is not much of an issue. A lot of the population have traveled to many different places in the world and quite a few of those married someone from their travels and brought them back to Fayetteville. (And their family, too.) This makes the city much more cosmopolitan than one would otherwise expect. It also accounts for the large number of quality ethnic restaurants and groceries in the area.
It's hard to make a difference in your community if it's got millions of people in it. Small towns often let you know that you (and your descendants for 3 generations!) are strangers in town. One of the things that I **really** like about Fayetteville is that people here don't care where you're from. If you show up, are nice, and want to participate, they are glad you're there and will welcome you. It's pretty easy to get involved in the city or county government and have a real impact on what the local government (or other organizations) do.
There are some great festivals, talented and friendly artists, good libraries and other amenities as well. I read in the newspaper that the government is building a $30+ million dollar stadium so folks can watch grown men play with their balls in public if that's of interest. You can probably guess I couldn't care less about that last topic but a lot of people do, so I thought I should mention it.
Next time you'll be in town, send me a PM. I'll be glad to show you some good neighborhoods and some of the sights. A lot of people who move here for the military spend so much time bad-mouthing the town they never actually learn anything about it...
Because of the rental property business we run on the side, we know good realtors and tradesmen if you want recommendations.
Best of luck!