Hi all,

I'm not 100% sure where is the correct place to ask this question, so please forgive/enlighten me if you has seen this question repeated some where.

Let me first start this, by give a clear context on where our system currently is and what are the issue:

1. The system purpose: We developing a platform for multi-tenant user to use our platform to manage their business processing. (Mostly doing ERP tasks...)

2. The system design: We have a monolithic design web site/app with:

• One SQL Server database (to store all system wide data)
• One web server (to provide web APIs)
• One website (light front-end)
• Several workers/services implemented on C# .NET

1. The implementation:

• All back-end APIs and worker are access database via Entity Framework (6.0) - Database first.
• Each worker service run in cycle 1 minutes, and once it run, it can process a long-period transaction with requires saving multiple times on multiple entities.

1. The issues:

• Poor query performance (nearly 10 sec for a query listing on a table with join 2 tables, each table has about several thousands records). I know Entity Framework is slow, but is it really this bad? :(
• We constantly run in to time out issue.
• We got concurrency exception (Although, logically we can assure that there's no change was made while a worker service is processing an entity object)

Giving the issue, I would like to ask that:

1. Giving our system design, what is the main cause of the poor performance: SQL Server or Entity Framework?
2. I don't have experience in DevOps and SQL Scaling. Given our situation, what is the "TODAY" strategy to scale SQL Server (horizontal) to handle much more query demand, remove the outage and get highest availability for our system?
3. In the mean time, we planned to improve the performance first by indexing, review and remove logic which use long-holding transaction. But giving the dynamic of Entity framework, we not sure we should take this part, or move completely to other ORM (like Dapper)?
4. I also researching and plan to redesign our system to use micro service architecture, but one thing that I still not completely understand is that: Although, we break the database into multiple domain-driven instance. But then, it will can also be run into the same problem again if the domain is big enough. I know some system use No-SQL, in-memory db like Redis, and other db like MongoDb when I come to high scalability system/ micro service. This make me wonder: Does SQL Server Database really suitable for micro services? Will I run into the same issue again when the domain for each service is big enough? Or are there any solution/strategy that modern system apply to make SQL Server really suitable for micro service architecture.

TL;DR: We have a monolithic system which recently run into poor performance issue. We tried to fix it with indexing, logic etc...and one thing we know for sure that we have to apply micro-service architecture. But one question still remain: The bottle neck still remain at our database server (SQL Server), how could we actually scale SQL Server so our system really becomes high available and suitable for high demand, high availability system?