Is it possible to scale-out with Microsoft SQL Server?
You've kinda got a bunch of questions in here:
It is possible to scale out READS in Azure SQL DB? Yes.
Depending on the service you use, like Azure SQL DB's Active Secondary Replicas, your read workload can automatically be scaled out across multiple servers without big changes to your application code. However, you do still need to add a separate connection string in your application specifying ApplicationIntent=ReadOnly so that they know it's safe to move your read query over to a readable replica.
Is it possible to scale out WRITES in Azure SQL DB? Yes, if you change code.
The document you linked to, Data Partitioning, is a set of design guidelines that you can use when you code your application. However, those are indeed code changes, not a feature of Azure SQL DB that you just flip on the same way you flip on secondary replicas.
It's up to you to design a layer of code so that when your app needs to run a query, it connects to the appropriate location.
Think of it like the World Wide Web: at Stack Overflow, write workloads are separated out across StackOverflow.com, DBA.StackExchange.com, ServerFault.com, and other sites. However you, the user, have to know which site has the data you want to write to - you can't post an answer to this question over at ServerFault.com. That logic is built into your brain - and the same logic needs to be built into your app if you want to scale out writes across multiple servers.
There was a brief moment in time where Microsoft thought they'd do this work for you with Azure SQL DB Federations, but that was quickly deprecated.
Are the same things possible with SQL Server? Yes.
Scaling out reads is as easy as:
- Buying more SQL Servers and building them into an Availability Group
- Adding another connection string in your app specifying ApplicationIntent=ReadOnly
- Profit!
Scaling out writes isn't that easy, and requires code changes just like it does in Azure SQL DB.
Does either of those do true load balancing? No.
Neither Azure SQL DB nor SQL Server Always On Availability Groups truly balance load across multiple secondaries. You can get into a situation where one replica is running a handful of awful queries, and the rest are sitting around idle. And in that situation, you can still end up getting new queries assigned equally to both the underworked and overworked servers.
Doing true load balancing - keeping the work requirements similar across multiple replicas - is left as an exercise for the reader.