One of the advantages Azure SQL Database has over on-prem SQL Server is the ease in which it can scale. I’ll discuss the various options for horizontal scaling, vertical scaling, and other similar features.
Horizontal scaling refers to adding or removing databases in order to adjust capacity or overall performance. This is also called “scaling out”. Sharding, in which data is partitioned across a collection of identically structured databases, is a common way to implement horizontal scaling.
Vertical scaling refers to increasing or decreasing the performance level of an individual database—this is also known as “scaling up.”
Elastic Database features enables you to use the virtually unlimited database resources of Azure SQL Database to create solutions for transactional workloads, and especially Software as a Service (SaaS) applications. Elastic Database features are composed of the following:
- Elastic Database Tools (formally called Elastic Scale): These are .NET client libraries to help scale queries across multiple Azure SQL databases (sharding). This is horizontal scaling. See Get started with Elastic Database tools and video Elastic Scale with Azure SQL Database – Getting Started
- Elastic Database Pool: A pool is a collection of databases to which you can add or remove databases at any time. The databases in the pool share a fixed amount of resources (known as database throughput units, or DTUs). You pay a fixed price for the resources, which enables you to easily calculate costs while managing performance. This is vertical scaling. See Tame explosive growth in SQL databases by using elastic database pools to share resources
- Elastic Database Query. The elastic database query feature (in preview) enables you to run a Transact-SQL query that spans multiple databases in Azure SQL Database. See Azure SQL Database elastic database query overview (preview) and Getting started with elastic queries for sharding (horizontal partitioning)
- Elastic Database Jobs. Enables you to reliably execute a Transact-SQL (T-SQL) script or apply a DACPAC (data-tier application) across a group of databases. See Elastic Database jobs overview
You can change the service tier and performance level of your SQL database with the Azure portal, PowerShell (using the Set-AzureSqlDatabase cmdlet), the Service Management REST API (using the Update Database command), or Transact-SQL (via the ALTER DATABASE statement). You can use DMVs to monitor the progress of the upgrade operation for a database. This allows you to easily scale up or down a database, and it will remain online and available during the entire operation with no downtime. This is vertical scaling. See Change the service tier and performance level (pricing tier) of a SQL database.
Another feature is called Stretch Databases, to let your on-prem SQL Server database hold just the core data, with old/cold data that continues to grow sidelined transparently in Azure SQL Database. This is a feature only available in SQL Server 2016. See Stretch Database.
More info:
Elastic Database features overview
Video Azure SQL Database Elastic Scale
Video Elastic for SQL – shards, pools, stretch
SQL Azure Performance Benchmarking
Azure SQL Database DTU Calculator
