Chapter 6. Scaling Out Azure SQL Database

You can easily scale up or scale down an Azure SQL Database either automatically or manually. There are two types of scaling: vertical and horizontal.

Vertical scaling refers to switching to a higher or lower service tier, or vertically partitioning the data, which is to store different schema on different databases.

Horizontal scaling refers to dividing data from a single table into different individual databases.

This chapter teaches you how to auto scale Azure SQL Databases and shard a database. The chapter further talks about how to run cross-database queries.

By the end of this chapter, you will be able to:

  • Perform vertical and horizontal scaling
  • Run cross-database elastic queries
  • Create and maintain Azure SQL Database shards

Vertical Scaling

Vertical scaling can be of two types: scale up or scale down service tiers, and vertical partitions.

Scale Up or Scale Down Service Tiers

Scaling up a service tier refers to switching to a higher service tier, for example, switching from Basic to Standard S0 or switching from Standard S0 to Standard S1.

Scaling down a service tier refers to switching to a lower service tier, for example, switching from Standard S1 to Standard S0, or switching from Standard S0 to Basic.

Scaling up a service tier allows you to maintain or improve database performance in peak business hours, and scaling down a service tier allows you to save costs during off-peak business hours.

The service tier is changed on the fly with zero downtime. When a service tier change request is sent to the Azure SQL Database, it first creates a copy of the database in the requested service tier and switches to the database in the new service tier once the copy is ready.

You are charged for the new service tier once the service tier is changed, and not from the time the service tier change request is received.

The Azure SQL Database service tier can be changed from the Azure portal, using T-SQL script, Azure PowerShell, or by putting the database in an elastic pool. Except the Azure portal, these methods allow you to automatically change the service tier.

Note

Elastic pool is a group or pool of more than one Azure SQL Database with varying usage. This is covered in detail in Chapter 7 Elastic Pools.

One of the most common vertical scaling use cases is to automatically scale up or scale down a servicer tier based on the DTU (Database Throughput Unit) usage:

Scale Up or Scale Down Service Tiers

Figure 6.1: DTU (Database Throughput Unit) of Service Tiers

For example, you can put a script in place that can automatically scale to a higher database service tier if DTU usage increases past a specified threshold, say, switch to Standard (S0) if DTU usage is greater than 70%, and scale to a lower service tier if the DTU usage percentage is lower than the specified threshold, say, switch to the Basic service tier if DTU usage is less than 30%.

Another use case is to schedule the scale up and scale down based on peak and off-peak business hours. For example, if a business expects higher traffic between 1:00 PM and 3:00 PM, it can scale up to the Premium service tier, and it can scale down to the Standard service tier for the rest of the day.

Using T-SQL to Change the Service Tier

Let’s consider a scenario where Mike faces higher traffic than usual between 1:00 PM and 3:00 PM towards the toystore database; he can use T-SQL to change the service tier as follows:

Note

Refer to C:CodeLesson6ChangeDBServiceTier-TSQL.sql for the queries provided in the section.

  1. Open SQL Server Management Studio on your local machine and connect to the toystore Azure SQL Database.

    Note

    You can refer to Chapter 1, How to connect to an Azure SQL Database from SSMS.

  2. Copy and paste the following query to get the current service tier of the toystore database:
    -- Get the current database service tier
-- Run this in the 
toystore or your database context (and not master database context)
SELECT * FROM sys.database_service_objectives

    You should get the following output from this query:

    Using T-SQLused, for changing service tier T-SQL to Change the Service Tier

    The DMV sys.database_service_objectives returns the current Azure SQL Database edition and the Service Objective or the performance level.

  3. Copy and paste the following query into a new query window. Set the database context to master:
    SELECT * 
    FROM sys.dm_operation_status 
WHERE resource_type_desc='database' 
AND major_resource_id='
toystore'

    This query returns the details of any operation carried out on an Azure SQL Database. You will use this query to monitor the progress of the Alter database operation.

  4. Copy and paste the following code snippet in a new query window in SSMS:
    -- Run Query 1,2 & 3 at once in a single T-SQL Batch.
-- Query 1: Change the databaseEdition or Service tier to Standard S0
ALTER DATABASE 
ToyStore MODIFY (Edition='Standard', Service_objective='S0')
GO
-- Query 2: Get the current service objective
SELECT * FROM sys.database_service_objectives
GO
-- Query 3: Wait for the changes to be applied
Waitfor Delay '00:01:00'
GO
-- Don't run it along with Query 1, 2 & 3
-- Query 4: Get the current service objective
SELECT * FROM sys.database_service_objectives

    The preceding T-SQL snippet consists of four queries:

    • Query 1: This runs an Alter Database command to change the SQL Database service tier to Standard and Service Objective to S0.
    • Query 2: This gets the database edition and service objective for the toystore database.
    • Query 3: This adds a delay of one minute.
    • Query 4: This is the same as Query 2. It gets the current database edition and service objective.

    Select Query 1, Query 2, and Query 3, and press F5 to execute. You should get the following output:

    Using T-SQLused, for changing service tier T-SQL to Change the Service Tier
  5. While the queries are executing, quickly switch to the second query window that has the query from step 3 and press F5 to execute the query. You should see the following output:
    Using T-SQLused, for changing service tier T-SQL to Change the Service Tier

    Observe that the Alter database operation for the toystore database is in progress. Wait for the Alter database query in the first query window to complete.

    Once it is complete, rerun the query in the second query window. Observe that the value in state_desc column changed to COMPLETED from IN_PROGRESS.

    Switch to the first query window (if you aren't already in it), select Query 4, and press F5 to execute. Observe that the service tier has been changed to Standard S0.

    Note

    The connection in the first query window will be disconnected when the switch from basic to standard service tier is made. You'll receive an error message, as shown here:

    Msg 0, Level 11, State 0, Line 11

    A severe error occurred on the current command. The results, if any, should be discarded:

    Msg 0, Level 20, State 0, Line 11

    A severe error occurred on the current command. The results, if any, should be discarded:

    Using T-SQLused, for changing service tier T-SQL to Change the Service Tier
  6. Press Ctrl + N to open a new query window in SSMS. Change the query context to master database. Copy and paste the following query in the query window:
    --Query 5: Automate the process
-- Execute in Master database
PRINT 'database update in progress...'
DECLARE
    @databaseName sysname='
ToyStore', 
    @databaseEdition varchar(100)='Basic',
    @PerformanceTier varchar(10)='Basic'

Declare @dsql Varchar(MAX) = 
    'ALTER DATABASE [' + @databaseName + '] MODIFY (Edition=''' + @databaseEdition + ''', Service_objective=''' + @PerformanceTier + ''')';

SET @dsql = @dsql + ' 
WHILE(
    exists (SELECT TOP 1 * FROM sys.dm_operation_status WHERE resource_type_desc=''database'' AND major_resource_id=''' + 
    @databaseName + ''' AND STATE=1 ORDER BY start_time DESC))
    BEGIN 
    WAITFOR DELAY ''00:00:05''
    END'
EXEC(@dsql)

    The query automates the process of switching between service tiers. It takes three parameters:

    • databaseName: The name of the database whose service tier is to be changed
    • databaseEdition: The new service tier name; Basic, Standard, Premium, or PremiumRS
    • PerformanceTier: The service objective, such as S0, S1, or S2

    The Alter database command is used to change the service tier as specified by the parameters.

    The query then checks whether or not the service tier has been changed using the sys.dm_operation_stats DMV. If the database operation is in progress, it waits for five seconds and then checks for the progress again.

    Press F5 to execute the query. Once the query completes, the database service will be changed to Basic.

Vertical Partitioning

In vertical partitioning, the data is partitioned in such a way that different sets of tables reside in different individual databases:

Vertical Partitioning

Figure 6.2: Vertical Partitioning

For example, if there are four different schemas in a database, say Finance, HR, CRM, and Inventory, then each one of these is stored in one independent database, as shown in the previous diagram.

The vertical partitioning requires cross-database queries in order to generate reports, which require data from different tables in different databases.

Azure SQL Databases currently don't support three- or four-part object names, such as databaseName.SchemaName.TableName (excluding tempdb). Therefore, the cross-database queries are made using Elastic Queries.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
52.14.82.217