You have to deal with these things anyway in any kind of distributed setting. What this kind of location-independence via SSI really buys you is the ability to scale the exact same workloads down to a single cluster or even a single node when feasible, while keeping an efficient shared-memory programming model instead of doing slow explicit message passing. It seems like a pretty big simplification.
The marketing for SSI was that it was simple because programmers would not have to learn explicit message passing. Unfortunately, people that buy supercomputers tend to care about performance, so designing a supercomputer that is difficult to optimize misses the point. In real code, the only way to make them perform well was to layer topology-aware message passing on top of the shared memory model. At which point you should've just bought a message passing architecture.
There is only one type of large-scale SSI architecture that is able to somewhat maintain the illusion of uniform shared memory -- hardware latency-hiding e.g. barrel processors. If programmers have difficulty writing scalable code with message passing, then they definitely are going to struggle with this. These systems use a completely foreign programming paradigm that looks deceptively like vanilla C++. Exceptional efficient, and companies design new ones every few years, but without programmers that grok how to write optimal code they aren't much use.
Caring about where in memory your data lives is different from dealing with cache or paging. Programmers have to plan ahead to keep frequently accessed data in fast RAM, and infrequently accessed data in "slow" RAM. You'll probably need special APIs to allocate and manage memory in the different pools, not unlike the Address Windowing Extensions API in Microsoft Windows.
And once you extend "memory" outside the chassis, you'll have to design your application with the expectation that any memory access could fail because a network failure means the memory is no longer accessible.
If you only plan to deploy in a data center then maybe you can ignore pointer faults, but that is still a risk, especially if you decide to deploy something like Chaos Monkey to test your fault tolerance.