I THINK I NEED to replace my 5 year old ASUS dual CPU AMD motherboard.
Any recommendations?
Can the process be RELATIVELY pain free?
It's up to you. Do you think you need a new computer? If yes, then get one. It if you want to update it just remember it won't be cheap. Depending on your knowledge and skill you can buy a bare bones system and build your own. If not then purchase an assembled unit with preloaded software.
Can the process be RELATIVELY pain free?
No...
You are going to get a ton of opinions here but remember it's your money and get what you want. If anyone pushes something on you that you don't want, make them pay for it.

Go to http://www.newegg.com/
Great web site, great prices, no tax (unless you live in California) great review sections.
Did you mean dual CPU or dual core CPU?
I’ve got an AMD dual core and it’s still very good and fast, but I’ve read the Intel Core 2 Duo’s are better.
Both Intel and AMD now have Quad cores. May as well upgrade to one of those.
And as usual, both ASUS, Gigabyte and MSI seem to be the most popular mobo’s.
What will you be using your computer for mostly?
The 12 volt power requirements of more recent processors have gone way up, requiring new power connectors on the motherboards and new power supplies with higher amperage on the 12 volt rail(s), going to these new motherboard connectors. To the best of my knowledge there is not a single power supply made five years ago that I would put on a new motherboard today.
The memory interfaces have changed, requiring new DIMMs.
The hard drives have mostly gone from IDE (PATA - the wide cumbersome ribbon cables) to SATA (the narrow cables.) You can still work with the older IDE drives, but your old drives will be slower and smaller. Any future disk drives you purchase should be SATA.
If you really like the old case, you can keep that. But that's the lowest cost part of an upgrade. Cooling is likely to be a problem with an older case. The number one way to keep equipment running long and stable is keeping it cool.
The main keys to performance: