SQL performance over MPLS GI have a question about performance of SQL over MPLS.  

I have a client with 3 localizations. We want to centralize the server environment and migrate all of SQL databases to headquarter. Our client uses aplication based on SQL Server 2017 Standard. Between locations are MPLS Connection 100/100, latency (ping) between locations and headquarter are 5-7 ms. We anticipate that there will be about 150 connections to the database at one time. We're worried about the performance of this.  

Of course we want to build cluster SQL based on SQL 2017 Standard and Dell storage array with SSD disks for SQL DB. Does anyone have experience in similiar scenario and could tell about it?
Generally, you want the applications as close as practical to the database server. (This advice holds true regardless of the app &amp; database, so even though I'm a SQL Server guy, I'm going to try to write this to help folks looking for similar answers for MySQL, PostgreSQL, etc.)

Network issues boil down to two things: throughput at latency.

Throughput: the more data the app moves, the slower performance will feel. If the app only does small, targeted queries to bring back the minimum number of rows &amp; columns that they need, then it'll feel fast. On the other hand, if the app has been doing SELECT * FROM TABLE with no filtering, and doing the filtering client-side, then the reduced network bandwidth can be a problem.


Good design: only bring back the minimum data you need for a screen
Bad design: bring back the entire table, filter it client-side (MS Access apps do a lot of this)


Latency: if the app manages its own transactions, latency will be a problem. Some applications start a transaction, then give the database server one row at a time to accomplish a goal. They parcel work out in small doses, and they handle failure/retry logic inside their code (rather than a stored proc.) This used to be fine when the database server was within 1-10 milliseconds of the app, but if you start to stretch that to 50-100ms, transactions will feel exponentially slower to end users.


Good design: build a stored procedure, have your app call it with all the parameters necessary to accomplish a business transaction
Bad design: start a transaction, insert one row, wait for the database server to acknowledge it, then send another row in another table, and another row, holding locks open the whole time


Throughput: the more concurrency you have, the slower performance will feel. If everyone at every office has the exact same usage patterns - like they all come back from lunch at exactly 1PM and start back working at the same time - then the throughput of your network pipe can be a problem. Now you can start to see how the above can start to snowball: if you have a chatty app that manages its own transactions, then it'll have an even tougher sending/receiving the data it needs to the SQL Server, and locks will be held open even longer as the shared pipe throughput gets smaller.

To monitor for that, start by looking at each SQL Server's network port utilization today. You can track that via Perfmon counters or Task Manager, looking to see what percentage of its existing network port it uses - and how that relates to the MPLS connection that you're proposing.