Windows Azure SQL DatabaseDeep DiveChris AuldCTO – Intergen

AZR432

Agenda

OverviewTips and Tricks

Design and DevelopmentPicking Federation Model & KeyPicking Reference TablesGenerating Keys without bottlenecksCoding Fan-out Queries

Tips and Tricks Cont.Administration

Configuring Federation Layout Where and When to Split?Deploying & Upgrading SchemaMonitoring Federation MembersDealing with Scale-Down

vNext – What’s Next Recent ImprovementsComing in the next quarterComing in the next few quarters

Nomenclature

SQL Database = SQL Azure

OVERVIEW

Scalability Model for the Cloud

Cloud Apps Allow Massive Scale Orders of magnitude more than burst: 100s of nodes available for use

Cloud Apps Demand the Best EconomicsBest Price/Performance

Commodity hardware + high availability

Elasticity + Pay-as-you-goReduce overcapacity: take advantage of cloud provisioning model!

On Prem Provisioning Model Cloud Provisioning Model

Data Scale Challenges

For small scenarios scale up is cheaper & easierFor larger scenarios scale out only solution

Massive diseconomies of scale1 x 64 Way Server >>>$$$ 64 x 1 Way Servers.

We will reach a limitAt some point we just can’t buy a big enough box

Shared resource contention becomes a problemTransactions do not scale very well

Only scale out offers promise of linear, infinite scaleIf we want to build ‘Web scale’ apps we need to scale out

Federations in SQL Database

Canonical 3 tier app scales by Adding and removing nodes in the Web and Middle TiersBuying a “big-ass” database server

Federations extend the model to the DB Tier.Add and Remove SQL Azure nodes with federations. Scale on demand to your traffic without any downtime!

SQL AzureLarger User

Traffic

FrontTierFrontTierFrontTierFrontTier

FrontTierFrontTierFrontTierMiddle

Tier

Why use Federations? cont.

Go Beyond Single DB to Practically Unlimited Scale: Harness 100s of SQL Azure nodes

Best Economics Elastic database tier that can repartition with your applications workload. No downtime required!

Simplified Multi-tenancy:

Efficient management of tenant placement and re-placement

Simplified Development and Administration: Robust programming & connectivity model with native tooling

Single tenant per databaseMultiple-tenants per database Multiple databases per tenant

ARCHITECTUREFederations in SQL Azure

Overview - Concepts

CREATE FEDERATION fed_name(fed_key_label fed_key_type distribution_type)

SalesDB

Orders_federationOrders_federationfed_name

Federation Membersjust SQL Database instances

Federation Rootjust a SQL Database instance

Federations

member: Range [1000, 2000)

AUPK=5

AUPK=25

AUPK=35

AUPK=5

AUPK=25

AUPK=35

AUPK=1005

AUPK=1025

AUPK=1035

Atomic UnitsSmallest granule of federation key

Range Partitioning

Overview – Architecture cont.

Repartitioning Operations without Downtime!SPLIT members to spread workloads over to more nodesDROP members to shrink back to fewer nodesMERGE not supported yet. Discussion on How-2 later

SalesDB

Orders_federationOrders_federationOrders_Fed

[5000, 10000)

ALTER FEDERATION Orders_Fed SPLIT AT (tenant_id=7500)

[5000, 7500) & [7500, 10000)

Overview – Architecture cont.

Built-in Data-Dependent RoutingDDR ensure app can discover where the data is just-in-time

Apps no longer has to cache ‘shard map’ No cache coherency issues even with repartitioning

Prevents connection pool fragmentation issues

SalesDB

Orders_federationOrders_federationOrders_Fed

USE FEDERATION Orders_Fed(tenant_id=7500)

A Quick Lap of Federations

Scale Out with SQL Database

Database Design

Concepts – 3 ‘Styles’ of Table

Federated TablesTables that contain data that is distributed by the federation.A slice of data in each databaseOptimized for read+write at scaleOperation scope is at federation member level (i.e. transaction bounded by member boundary)

Reference TablesDuplicate copy of data in each Federation memberMust manually modify data in each federation member; thus eventually consistentOptimized for read operations at scale

Central TableRefer to tables that are created in the federation root for typically low traffic objects such as metadata.

SalesDB

CustomerFederationerationCustomerFederationerationCustomerFederation

Federated Tables and Reference Tables

Central Tables

Picking Federations

Normalize your data model to 3NF (or beyond) and then…Apply the Scale-First db design principles

Pick Federations – “Table Groups” that need Scale-out“Table Groups” are properties of the same entity - tied with fk relationships or access patterns“Table Groups” may need scale out if they have high storage needs or computational capacity needs.

Table Groups

Orders_federationOrders_federationfed_name

Picking Federation Keys

Pick Federation Keys and Define the Atomic Units (AU)AU instance is the target of in all (or most) latency and scale sensitive queriesAU Instance is target of all (or most) transaction boundariesAUs distribute the app workload equally to all membersLargest AU instance fits in the scale-up limit of SQL Database

Federation Columns

Orders_federationOrders_federationfed_name

Picking Reference Tables

What are Reference Tables?Look up tables that are cloned to all membersProvide easier programmability

Easier queries & transactionsLocal joins in DB vs ‘joining’ in code

How to pick ref tables?Set of tables use for lookups in in latency & scale sensitive workload of the appTables that don’t need strong consistency – (o.k with eventual consistency)

Generating Unique Keys

How to generate unique keys without identity propertyIdentity Property is not allowed on Federated Tables… Why?

Identity Generation can be expensive for large scale appsProvides linearly increasing valuesProvides no gaps id generation guarantees Can only be generated at the db tierCreates a bottle-neck; must have some sort of ‘shared’ counter

Benefits of Uniqueidentifier (GUID)Does not require centralized id generationCan be generate at any tier of the appProvides a nice random distribution over an enormous address space

Scale Out with SQL Database

Application Programming

Concepts – Data Dependent Routing

Data Dependent Routing: Applications always connect to the root and issue the USE FEDERATION routing statement to connect to atomic units (for example customer_id = 5075). As data is repartitioned, atomic units move around but address to the atomic unit guarantees routing to the correct federation member.

SalesDB

CustomerFederationerationCustomerFederationerationCustomerFederation

Range Distribution [min,1000, 2000, 3000 ….. 5000, 10000, Max]

-- Connect to Root DatabaseUSE FEDERATION CustomerFederation(customer_id = 5075) …

Connecting to FederationsFiltering Connections

FILTERING=ONConnect to an Atomic Unit

Reference Data stays fully visible. Safe; No Changes allowed to the global state of the federation member:

No DDL, No DML on Reference Data

Good for…Most of the workload of the application: safe programming modelLeakage Errors: Engine level predicate injection prevents data leakage

Connection to Root - SqlClient, ODBC“Server=tcp:servername.db.windows.net;Db=salesdb;User ID=uname;Password=pword;Encrypt=True”

-- THEN USE FEDERATION TO CONNECT OT THE MEMBER CONTAINING THE KEY.USE FEDERATION CustomerFederation(cid = 55) WITH RESET, FILTERING=ONGO

Microsoft Confidential

SalesDB

member: Range [100,200)

orders(federated)

CustomerFederation orderdetails(federated)

Uszipcodes(referece)

CustomerFederationCustomer_id=55

Customer_id=55

Connecting to FederationsMember Connections

FILTERING=OFFConnects to the full member

Unrestricted Access to the member: Same as connecting to the database nameDDL, DML and Access to All Atomic Units within the members allowed

Good for…Management Tasks: Schema DeploymentFan-out Querying – queries spanning multiple atomic units‘Unsafe’ – i.e. can break consistency

-- THEN USE FEDERATION TO CONNECT OT THE MEMBER CONTAINING THE KEY.USE FEDERATION CustomerFederation(cid = 55) WITH RESET, FILTERING=OFFGO

Microsoft ConfidentialMicrosoft Confidential

SalesDB

member: Range [100,200)

orders(federated)

CustomerFederation orderdetails(federated)

Uszipcodes(referece)

CustomerFederation

Customer_id=55

Writing Fan-out Queries

What is a Fan-out Query?Queries that can process data across fed members.

Fan-outRequirementsReporting Queries

Union or Aggregate Data Across Members

Unaligned QueriesQuery customer_name on fed with key customer_id

Types of Fan-out Queries:Union All: SimpleAligned vs Unaligned Fan-out Queries

Additive vs None-additive Aggregations

//MEMBER QUERY//start from the first member – min valueUSE FED f(id=min)WHILE (nextvalue != NULL)

//get results into dataset DataAdapter.Fill(ds);//get the value to navigate to the next member nextvalue = SELECT range_high FROM sys.federation_member_distributions

//SUMMARY QUERYLINQ2DataSet(ds)

Writing Fan-out Queries Cont.

Breaking It DownMember Query: the part you send to each memberSummary Query: the post processing query for member query results

Fan-out Pseudo-Code

Fan-out Queries

Submitting Fan-out Queries with Sample Fan-out Query Utility

Demo

Scale Out with SQL Database

Administration

Deploying and Upgrading Schema

With Federations Root and Each Member owns its own schema

Great for online upgrades – partial upgrade & rollbacksGreat if you want to differentiate schemaNot so great if you need to manually run schema updates

Schema Deployment with Sample Fan-out UtilityEnsure to write idempotent scripts

Idempotency: multiple runs produce the same outcome - pattern: if (not exists) then doBenefits: avoids long transactions, idempotent scripts are retry-able

Configuring Federation Layout - First Time?

Deciding You Initial LayoutHow many members? Which split-points?Volume or Transaction bound?

Cold StartScale characteristics in the cloud vs on premise are very different

Test your load: Load you expect the first week, months and year?Favor smaller and more members

Take advantage of better throughputBetter Tx/secFaster split times for rebalancing and redistributing the load

Take advantage of pay as you go- not quite linear cost, but close10GB/month = $45.95 2x5GB/month = 25.97*2 = $51.94

DMVs for Where & When to Split?

Online elasticity is great but today you still need to manage repartitioning

When you need to repartition a member?What the best split point?

When to SPLIT?Decide Threshold for ‘good’ vs ‘bad’ behaving appIdentify early indicators of resource contention for the member

#user connections, large blocking, #concurrent requests, throttling eventsDon’t overshoot

Where to SPLIT?Decide the KPI that will give you the equal redistribution of load

Managing Members

Managing members and schemas with fan out tool

Demo

Recent Changes Available TodayChanges Coming in the Next Quarter

Roadmap Federations

Recent Changes Available Today

Performance ImprovementsLatency of USE FEDERATION Improved

Improved Caching at the GatewayConnection pooling at the gatewayFederation map pooling at the gateway

USE FED for curing Connection Pool Fragmentation#ConcurrentUsers per Server = CU (ex:10)#AppServer = N (ex:50)#Databases = M (ex:50)Sharding Total App Connections = CU*N*M – (ex:25K)Federations Total App Connections = CU*N – (ex:500)

Gateway Nodes

DB Nodes

App Server

App Server

App Server

App Server

#Users

Coming in the Next Quarter

Identity and Timestamp:Supported on Reference TablesStill restricted on Federated Tables

Data Sync ServiceManual Setup with Data Sync

Reference Data Replication Between MembersSync on-prem databases with federated databasesSync between federated databases in the cloudPotential to use this for eventually consistent MERGE behaviour

Coming in the Next Few Quarters

Local DRSQL Azure Provides built in HA with 99.9% SLAHow about?

App upgrades and rollbacks, Admin and User errors – dropped tables or rows

Geo-DRHow about?

Protect against planned or unplanned Data-Center failuresBacking up DBs to another Data-centerTake an on-prem snapshot of your data in the cloud

DR simplified with Federations

Federation DR Enhanced

Partially Present Federations and Member Switch Operation

Switch members in and out of federations

*ALTER FEDERATION fed_name SWITCH IN|OUT AT (HIGH id=100) WITH db1(*preview – final syntax may look different)

SalesDB

Orders_federationOrders_federationOrders_Fed

Unavailable Federation MembersFederation Root

Federations

db1

Federations DR Enhanced!

Scenarios:Copy Databases for easy application rollout and rollback

Rollback upgrade on member db with snapshotsPoint in Time Restore for easy “oops” recovery:

Restore the member db for customerID “55” at “5/28/2012 12:00:00”

SalesDB

Orders_federationOrders_federationOrders_Fed

Federations

DBCopy member2 @ now

PITR member2 @ 6/11/2012 12:00:00

Federations Merge Options!

Scenarios:Eventually Consistent Merge

Swap out members containing dataDROP members to collapse federationCopy data back into single/fewer federation members

SalesDB

Orders_federationOrders_federationOrders_Fed

Federations

Swap Out Swap Out

DropDropCopy Data In

Federations DR Enhanced!

Scenarios:Geo DR for Failover for each Geo Availability

Initially at the member levelEventually for the federated db

P’

S’

S’ P

S

S

Further Information

SQL Azure Online Documentationhttp://msdn.microsoft.com/en-us/library/windowsazure/ee336279.aspx

My Blog + Twitter – Code + SQL later todaywww.syringe.net.nz http://twitter.com/cauld

Cihan Biyikoglu – Federations PMLots of stuff on Federationshttp://blogs.msdn.com/b/cbiyikoglu/

Up to date information on twitter

© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the

part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.