From my experience working with it: in short, it is just a fancy name for shared memory between machines, with software libraries on top to manage synchronization, distributed transactions, et cetera.

From a sysadmin perspective it behaves less like a "single system image" and more like multiple images with shared resources.