What big limitations should I expect from Linked SQL Servers?

Question

Our product is based on Microsoft SQL Server. Currently, we are using three databases, and have always deployed them on one SQL Server instance.

The three database are OLTP, OLAP, and audit. The OLAP database has massive inbound data on EOD from both OLTP and audit, using cross database queries.

Questions

If we were to deploy these three databases onto three separate Standard Edition instances inside a single physical server, and bind them together using the Linked Server feature of SQL Server:

1. How transparent will it be to the application code? How much change should I expect?
2. Inbound data to OLAP amounted in 50k-100k rows, 200-500MB payload per EOD. How much performance drop should I expect?
3. What other big limitations should I expect?

Background

Currently we are pitching our potentially first client with 500+ concurrent users.

We are drafting a server spec, which includes 64 cores and 256GB RAM. For SQL Server to utilize all those abundant resources, the client would have to buy Enterprise Edition, which for SQL Server 2016 is only available in per-core based licensing.

We are afraid that the licensing cost alone (64 x $7400) will put them down. So I am thinking of splitting the database into three instances of Standard Edition, and have them linked together, hoping that the linkage feature will be transparent from the application code.

Answer 1

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.