Is it possible to set up Microsoft Sql Server in a scalable way?
Not impossible, very easy in fact with read-only routing. Clients intending to be read-only specify it in their connect string, so this is per-session rather than per-statement.  You can have up to 9 replicas in an availability group. With modern hardware and storage, that goes a long way to being “scalable” - 10’s of thousands of concurrent connections easily.