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.