A simple summary of my question is: What is the current best practice for scaling out a MS SQL server enterprise wide OLTP web application to cope with potentially ten thousand users around the globe?

Much of the info online and similar questions date back several years, and I'm not sure what is current right now.

Disclaimer: We don't have any dedicated SQL database developer/architect internally, and I am not suggesting that we try and wing this ourselves. I'm looking for advise on current best practice so I can put forward the case for what we need to do to avoid future problems, and why we need to get someone in that knows what they are doing. :-)

The Details: As a business, we develop a large enterprise wide OLTP application that manages data from across most business departments. This has its roots back as a MS SQL 2000 client-server based application for mid-sized businesses, and the database structure has barely changed since.

The current architecture is a single database on a single instance of SQL server, and this can process transactions within a reasonable response time as long as the queries are sensibly written. However, we do run into the common forms of blocking with the current architecture which I expect to get very much worse when we multiply the number of concurrent users and transactions.

We are in the process of developing a web based version of the product to target much larger global customers, potentially going from a few hundred concurrent users to ten thousand or more.

The current problems affecting/impacting concurrency I believe we have are:

Single database, usually hosted on customer site.

1 single file group.

No table partitions

ORM generated queries

Read Committed isolation level

No ETL, replication or separate reporting/analytical DB

I have read up on the following and have some questions/observations:

Availability groups for serving read only queries. This could be quickly implemented and remove long running queries from reports blocking user write transactions. I'm aware that without clustering this would not be DR. Would this be OK for a quick win to offload many of the reporting and read only OLTP type queries to reduce blocking?

RCSI and query level Snapshot isolation. I don't think we could easily implement RCSI due to race conditions that I know would exist in how we currently intentionally use record locking to ensure uniqueness. However, implementing snapshot isolation for our read only queries should be possible. This would avoid reads being blocked by writes. I'm not a c# developer, so can this be done within the connection string itself if we use OData?

Horizontal partitioning - sharding. This sounds like this would help where the users are geographically spread, allowing for separation of data around the globe. Having static or slowly updated data replicated across databases, each of which contain its own transactional data would help reduce performance bottlenecks across the whole business. Is sharding still a recommended practice? Am I correct in assuming we need to write all our own application logic for accessing the shards and maintaining database integrity/consistency? What happens if a shard in a remote location goes offline, how should the consistency checks deal with this?

A few summary questions:

Have I fundamentally misunderstood something? :-)

Are there any other concepts you know that we could consider?

Any experience/tips with taking a single file database and scaling it out to global scale?

closed as too broad by LowlyDBA, Erik Darling, jadarnel27, mustaccio, Brent Ozar Jan 11 at 18:11

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    The good news is you seem to have a pretty good handle on your options. The bad news is that this question will get closed as too broad. It's just not the kind of question you can post here. MSDN forums welcomes open-ended, broad and opinion-based questions. social.msdn.microsoft.com/Forums/en-US/home – David Browne - Microsoft Jan 11 at 16:02
  • SQL 2016 introduced a read Replica as part of the always on availability. Scaling out is a limitation as even with 2016+ only one replica is allowed. 2019 preview seems to have some additional features related to read Replicas. [sql 2019 preview ](docs.microsoft.com/en-us/sql/database-engine/…) Add merge replication to your list, it can be a pain to maintain but it might help with trying to support read/write in a global manner. – Aaron Jan 11 at 16:18