So far I've installed Apple's new operating system on three computers (a MacBook, an iBook, and an iMac), and in each case it's gone quite smoothly. The most awkward part was clearing space on my MacBook and my wife's iBook—we're both prone to stuffing the hard disk almost to the brim.
I followed John Gruber's advice, creating a bootable backup first (something I've always done for previous upgrades), then performing a simple upgrade installation, without my usual overcautious "install and migrate" procedure.
Overall it's an unremarkable transition, and I mean that positively. There are cosmetic changes, some good, some bad, but everything operates pretty much as before. My unexpected favourite new features are Spaces (I never liked virtual desktops before, but Spaces is so seamless it really works for once), Quick Look, and screen sharing (sure to be a godsend for remote tech support with my in-laws). Oh, and Spotlight search actually works now.
Like many others, I dislike the new translucent menu bar, which doesn't mesh with the desktop pictures I prefer. But there's an easy solution: I just edited my desktop picture to have a white strip across the top the same height as the menu bar, and now it's back to readable again.