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?