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.
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:
- How transparent will it be to the application code? How much change should I expect?
- Inbound data to OLAP amounted in 50k-100k rows, 200-500MB payload per EOD. How much performance drop should I expect?
- What other big limitations should I expect?
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.