Transcript Title Slide No more than 2 lines

Scaling out and in with
Azure SQL DB Elastic Scale
DBA-203
Warner Chaves, MCM/MVP,
SQLTurbo.com, Pythian.com
About me
• SQL Server DBA for 10 years.
• Previously an L3 DBA at HP in Costa Rica, now a Principal
Consultant at Pythian in Ottawa Ontario.
• SQL Server MCM and MVP.
• Twitter: @warchav
• Email: [email protected]
• Blog: SQLTurbo.com
• Company: Pythian.com
2
Agenda
Goal of today: provide an overview of the elastic
capabilities of the Azure SQL Database platform.
•
•
•
•
•
3
Database elasticity intro
Database Elastic Tools
Database Elastic Query
Database Elastic Pools
Database Elastic Jobs
Database layer elasticity
• We’re going to focus on DbaaS, Azure SQL Db.
• Building and shrinking database capacity on
demand.
• Elastic scale focuses on scale OUT (“Sharding”),
as opposed to just scaling UP.
• Assume one application, common schema on all
dbs.
4
Why scale out?
1. Hitting resource limits on a scale up solution
(CPU-RAM-size).
2. Geographic considerations.
3. Security concerns require separate data.
4. Easier chargeback mechanism for client.
5. Easier to manage individually for a particular
client.
5
Common challenges to scale out
•
•
•
•
•
6
Provisioning speed.
Controlling cost.
Shard data movement.
Routing the application to the proper database.
Querying the databases individually as well as
easily querying the global set.
A Db elastic solution architecture
7
Creating and
working with an
elastic data tier
Elastic db tools: .NET client library
• The purpose of the library is to facilitate building
applications with an elastic db layer on Azure SQL db.
• Main concepts:
•
•
•
•
•
9
Shard ~ Database
Shard Set
Shardlet
Shard Map Manager
Reference tables
Db Elastic Client Library
10
Workflow for the client library
1. Create a database called a “Shard Map Manager”.
2. Register databases as shards in the SMM.
3. Setup the shard distribution method (lists of keys or
ranges).
4. Request a connection from the library based on the
sharding key (data dependent routing).
5. Use that connection for the db interaction of the shard.
6. Multi-shard queries are possible too.
11
Elastic db tools: Split/Merge tool
• The Split/Merge tool helps with rebalancing data in the
shard set.
• You can SPLIT a db into multiple.
• You can MERGE multiple dbs into one.
• These operations allow to respond to changes in capacity
needs.
• It’s delivered as a customer-hosted Azure service.
12
Elastic db tools: Split/Merge tool
SplitMergeDb
ShardMap
Split/Merge
Service
SPLIT
Db1
13
MERGE
DbN
Elastic db tools: Split/Merge interface
14
Elastic db tools: Split/Merge interface
15
Elastic Database Query
•
•
16
Allows querying a set of databases as a single entity
through one connection string.
This is useful for 3rd party apps where we can’t insert the
.NET elastic scale library (Excel, SSRS, etc).
Configuring Elastic Database Queries
1. A set of Azure SQL Dbs with a common schema (any tier).
2. A shard map and Standard or Premium tier db to be the
query connection endpoint.
3. Creating a credential for the elastic query:
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
'password';
CREATE DATABASE SCOPED CREDENTIAL
<credential_name>
WITH IDENTITY = '<shard_map_username>',
SECRET = '<shard_map_password>';
17
Configuring Elastic Database Queries
4. Create an external data source on the endpoint db:
CREATE EXTERNAL DATA SOURCE <data_source_name> WITH
(TYPE = SHARD_MAP_MANAGER,
LOCATION =
'<fully_qualified_server_name>',
DATABASE_NAME =
'<shardmap_database_name>',
CREDENTIAL = <credential_name>,
SHARD_MAP_NAME = '<shardmapname>');
18
Configuring Elastic Database Queries
5. Create an external table:
CREATE EXTERNAL TABLE [ database_name . [ dbo ] . | dbo. ]
table_name
( { <column_definition> } [ ,...n ])
{ WITH ( <sharded_external_table_options> ) }
)[;]
<sharded_external_table_options> ::=
DATA_SOURCE = <External_Data_Source>,
DISTRIBUTION = SHARDED(<sharding_column_name>) |
REPLICATED | ROUND_ROBIN
19
Other flavors of Elastic Db Queries
• SP_EXECUTE_FANOUT: can consume a shard map
to run a fanout query over the set of sharded
databases and concatenate the results.
• TARGET=RDBMS: cross database queries are
supported now by creating a external source that
targets a single database.
20
Sample App
Demo
Managing elastic
resources
Elastic Database Pools
• Allows pooling Azure SQL resources for a set of dbs for a
more efficient performance/cost ratio.
• The resources are quantified as eDTUs (elastic DTUs).
Storage size is also tied to the eDTUs and depend on tier.
• Different pools per Service Tier.
23
Database pool specifications
Tier
Max Pool
Size
Max Dbs
eDTU pool
range
Max
eDTU/db
Max Size
/ db
Basic
120GB
400
100-1200
edtu
5
2GB
Standard
1200GB
400
100-1200
edtu
100
250GB
Premium
750GB
50
125-1500
edtu
1000
500GB
24
Elastic Database Pools
One db pattern
25
The pooled pattern
Elastic Database Pools: pricing
• Fixed cost per eDTU (min 100, max 1500).
• Example:
• 16 S3 databases: 16x$150 per month = $2400, each
db has static 100 DTU. 250GBx16 storage.
• One Standard pool: (800 eDTUx$2.23) per
month=$1784, each db can go 10-100 DTU. 800GB
storage.
26
Create a
db pool
Demo
Running jobs on
an elastic data
tier
Elastic Database Jobs
• Allows running jobs against a set of databases.
• Either a T-SQL script or a DACPAC.
• Can be a custom set, a shard set or a db pool.
• Scheduling and retry similar to the SQL Agent.
29
Setting up Elastic Database Jobs
• It’s a customer-hosted Azure solution.
• Cost is per component.
• Requires the following components setup during install:
• Cloud service (worker role to run tasks).
• SQL Db (control db to store metadata, S0 default).
• Service bus (used for work coordination).
• Azure storage (for diagnostic output).
30
How do Elastic Database Jobs work?
Portal/P$
Control
DB
Job info
Controller
Job tasks
A database set
31
Job Task
Exec
Execution and results
Using Elastic
db jobs
Demo
Recap
• If you want easy scale out of a .NET app:
elastic db tools.
• If you want to be flexible on resource
consumption: elastic database pools.
33
Recap
• If you want queries that span multiple dbs:
elastic db query.
• If you want to run jobs on 1 to N dbs:
elastic database jobs.
34
Q/A
Thank You!