What big limitations should I expect from Linked SQL Servers?
How transparent will it be to the application code? How much change should I expect?


Not transparent at all. Expect major changes.

You should be prepared for very substantial performance degradation.

Distributed Query (the framework for linked servers) uses a general OLEDB model whatever the server on the other end happens to be. It is true that a SQL Server target may be able to offer more complete information (metadata, statistics etc.), but the result is still nowhere near as tightly integrated or capable as a native cross-database operation.

Remote queries have a deserved reputation for slow performance, and poor plan choices by the optimizer. Statements that change data (delete, insert, update, merge) are particularly prone since the basic model is often that of a cursor.



If you never need to perform ad-hoc cross-instance queries, you might be able to hand-tune each stored query for acceptable performance, but this is a lot of work, and success is by no means guaranteed.

For cross-instance bulk operations, you would be much better off using real bulk operations (bcp, BULK INSERT, SSIS...etc.) between instances than using linked servers.



All that said, the basic idea seems much more trouble than it is worth to me. Specify hardware that will work within the constraints of Standard Edition; or, if the client requires higher performance, get a bigger server and use Enterprise Edition.