Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations
Cihan BiyikogluProgram Manager Microsoft SQL Azure
DBI403
What are Federations in SQL Azure?
Federations are objects that allow scaling-out of data for building data tier applications with unlimited scalability and best price-performance through amplifying backend elasticity and simplified multi-tenancy at the database tier.
Unlimited ScalabilityDynamic and Online ElasticitySimplified Multi-Tenancy
Unlimited Scalability
Scale Applications horizontally to many nodes.Practically never run out of capacity:
Scale beyond scale-up and single database limitations (50GB limit db size limit, computational capacity of a single node)Scale to massive computational capacityUtilize unlimited storage
Rich Elasticity
Fully exploit pay-as-you-go…Expand and Contract without downtime to amplify elasticity
Build apps that grow with its workload over time.Handle bursting workloads, peaks and valleys…
Few Nodes Many Nodes
Efficient Tenancy Models
Classic Tenancy Model On PremiseSingle-Tenant-Per-Database
Cloud Tenancy ModelsSingle-database-per-tenant does not work for long tail and large tenantsUtilize multiple-tenants-per-database and multiple-databases-per-tenant as well for full flexibility
Tenancy Models:Single tenant per database
Multiple-tenants per database Multiple databases per tenant
Tenant_id =55 Tenant_id =55
Tenant_id =55
Tenant_id =56
Tenant_id =57
Few Typical Scenarios - 10Kft
Web Scale DB SolutionsScale requirements that need to break through the scale-up ceilings and scale to workloads to handle populations on the web
Multi Tenant SaaS ISVs ISVs coming from Single Tenant-per-database model that are expanding to flexible tenancy model with multiple-tenants-per-database or multiple-databases-per-tenant.
Workloads with Spikes Applications that has periods of spikes in their workloads with variance in capacity and scale requirements
NoSQL Applications (more on this next…)
NoSQL Gene in SQL Azure Federations
NoSQL: Typically a different compromise on the CAP Theorem. …As do SQL Azure Federations…
Scale-out for massive parallelismFirst class sharding support in the platform
Loosened Consistency ModeEventual Consistency across members – members have independent schemasLocal consistency within members – members are databases with full ACID properties
Lightweight local storage as well as permanent consistent storageWith each federation member, you also get access to the local tempdb
Support for Unstructured and Semi Structured DataXML, Blob types and Key-Value Pairs
Overview - Architecture
Federations: Federation are contained within a user database just like other objects. Above, SalesDB represent a user database with federations. There can be many federations to represent varying scale-out needs within a single database.
Federation Members: Federation use regular SQL Azure databases to achieve scale-out. Databases contain parts of federations data are called federation members and are there to provide the computational capacity for parts of the federations workload. Federation members cover the full range of data for a federation and are managed by the federation dynamically as data is repartitioned.
SalesDB
CustomerFederationerationCustomerFederationerationCustomerFederation
Federation MembersFederation Root
Federations
Overview - Concepts
Federation Distribution KeyThe key used for data distribution in the federations. In the federation definition, the distribution key represented by a label for the distribution key, a data type to specify the valid data domain for the distribution such as uniqueidentifier or bigint, and distribution type to specify the style for distributing the data such as range.
Atomic UnitRepresent a single instance of a federation key. An AU contains all rows in all federated tables with the same federation key value. These collection of rows are guaranteed to stay together and is never SPLIT further into multiple members. With a federation distribution key such as tenant_id, atomic unit refers to all data for tenant_id=55 in federated tables.
Federation RootThe database that houses federation object and is the central repository for information about distribution of scaled-out data.
SalesDB
CustomerFederationerationCustomerFederationerationCustomerFederation
Federation MembersFederation Root
Federations member: Range [1000, 2000)
AUPK=5
AUPK=25
AUPK=35
AUPK=5
AUPK=25
AUPK=35
AUPK=1005
AUPK=1025
AUPK=1035
Atomic Units
Overview - Concepts cont.
Federated TablesRefer to tables that contain data that is distributed by the federation. Federated tables are created in federation members and contain a federation distribution key. Federated tables contain data that is part of the scaled out data.
Reference TablesRefer to tables that contain reference information to optimize lookup queries in federations. Reference tables are created in federation members. Reference tables typically contain small lookup information useful for query processing such as zipcodes that is cloned to each federation member.
Central TableRefer to tables that are created in the federation root for typically low traffic objects such as metadata.
SalesDB
CustomerFederationerationCustomerFederationerationCustomerFederation
Federation membersUser Database and Federation root
Federations
Federated Tables and Reference Tables
Global Tables
Overview – Architecture cont.
Repartitioning Operations: Federation allow online repartitioning of data through T-SQL commands such as SPLIT. By repartitioning CustomerFederation with SPLIT, administrators can move data to new federation members without downtime and expand computational capacity of the system.
SalesDB
Orders_federationCustomerFederationerationCustomerFederation
Range Distribution [min,1000, 2000, 3000 ….. 5000, 10000, Max]
ALTER FEDERATION CustomerFederation SPLIT AT (tenant_id=7500)
Overview – Architecture cont.
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) …
Federations
deep dive
Sample with Multi Tenant App
Plot: Sales application tracking orders from tenants and managing a large product catalog.
Database Name = AdventureWorksFederations =
CustomerFederation with (cid bigint RANGE)Federation distribution key = cidFederated Tables = Customers, CustomerAddress, …Regular Tables = Zipcodes
Products_Fed with (product_id varbinary(64) RANGE)Federation distribution key = product_id
Sample Scenario – Multi-Tenant App
-- Day#1 – DeployCREATE DATABASE SalesDBGOCREATE FEDERATION CustomerFederation(cid BIGINT RANGE)GOCREATE FEDERATION ProductFederation(pid VARBINARY(64) RANGE)GO
SalesDB
member: Range [MIN, NULL)
ProductFederation
CustomerFederation
Sample Scenario – Multi Tenant App-- Connect to the federation memberUSE FEDERATION CustomerFederationeration(cid = 0) WITH FILTERING=OFF, RESETGOCREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] NOT NULL,[Title] [nvarchar](8) NULL,
… CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([CustomerID] ASC) ) FEDERATED ON (cid = CustomerID)GOCREATE TABLE [dbo].[CustomerAddress](
[AddressID] [int] NOT NULL,[CustomerID] [bigint] NOT NULL,…
CONSTRAINT [PK_CustomerAddress] PRIMARY KEY CLUSTERED ([CustomerID],[AddressID] ASC)) FEDERATED ON (cid = CustomerID)GOCREATE TABLE [dbo].[PostalCode](
[PostalCodeID] [int] NOT NULL,[PostalCode] [nvarchar](15) NOT NULL,… CONSTRAINT [PK_PostalCode] PRIMARY KEY CLUSTERED ([PostalCodeID] ASC)
)GO
SalesDB
member: Range [MIN, NULL)
CustomerFederationCustomerFederation Customer(federated)
CustomerAddress
(federated)
PostalCode(reference)
Sample Scenario – Multi Tenant App-- Day#2 business grows!ALTER FEDERATION CustomerFederation SPLIT AT(cid = 1000)GO-- Day#3 black friday! (future)ALTER FEDERATION CustomerFederation SPLIT AT(cid = 100,200,300,…900)GO-- Day#4 recession hits! (future)ALTER FEDERATION CustomerFederation MERGE AT(cid = 100,200,300,…900)GO-- Day#5 oh shoot! it is a double dip. (future)ALTER FEDERATION CustomerFederation MERGE AT(cid = 1000)GO
SalesDB
CustomerFederationerationCustomerFederationerationCustomerFederation
SPLIT in Action
Appmember: Range [100,200)
Customer(federated)
CustomerAddress
(federated)
PostalCode(reference)
Member: Range [100,150) Member: Range [150,200)
demo
Online Repartitioning with FederationsThanks To: Andrew Kimball, Lidan Miao, Santeri (Santtu) Voutilainen, Hans Olav Norheim, Nok Mohprasit, Vishrut Shah, Chu Chen, Gjorgji Gjeorgjievski, Jeff Wight, Sudhir Darbha, Ning Yu, Yurong He, Dedian Guo, Jack Richins, Marc Friedman, Paul Vick, Dayong Gu, Joachim Hammer, Michael Rys, Lev Novik, Jason Clark and Michael Thomassy and more…
Connecting to FederationsFiltering Connections
FILTERING=ONConnect to the Atomic Unit
Reference Data stays fully visible. 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
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=OFFGO
Microsoft ConfidentialMicrosoft Confidential
SalesDB
member: Range [100,200)
orders(federated)
CustomerFederation orderdetails(federated)
Uszipcodes(referece)
CustomerFederation
Customer_id=55
Recap!Federations v1 with SQL Azure
Federations enable building data tiers with unlimited scalability,best price-performance with dynamic and online elasticity,Simplifies multi-tenancy.
Federations Improve Developer ExperienceRobust Connection Management
Federation Directory for connection routingFiltering connections for safe programming model
Deliver Superior Management ExperienceOnline data redistribution through operations such as SPLIT.
Federations – Future Enhancements
Fan-out Queries: Ability to query multiple members with a single querySchema Versioning and Management: Fine grain control over schema rollout and upgrades. Developer Experience on Premise: Full end to end development experience on premise for folks developing with on premise technologies.
Federation Technology Preview Nominations
Now Open! Federations Technology Preview Program NominationsInformation on How to Nominate your Application
http://blogs.msdn.com/cbiyikoglu/ Click on the Survey LinkFill-out the Survey QuestionsWait for communication from the preview team!
Q&A
Send us your feedback email: [email protected]: http://blogs.msdn.com/b/cbiyikoglu/
Other Sessions to Visit
DBI210 | Getting Started with Cloud Business Intelligence Monday, May 16 | 4:45 PM - 6:00 PM | Room: B213
COS310 | Microsoft SQL Azure Overview: Tools, Demos and Walkthroughs of Key Features Tuesday, May 17 | 10:15 AM - 11:30 AM | Room: B313
DBI323 | Using Cloud (Microsoft SQL Azure) and PowerPivot to Deliver Data and Self-Service BI at Microsoft
Tuesday, May 17 | 1:30 PM - 2:45 PM | Room: C208
DBI314 | Microsoft SQL Azure Performance Considerations and Troubleshooting Wednesday, May 18 | 1:30 PM - 2:45 PM | Room: B312
DBI375-INT | Microsoft SQL Azure: Performance and Connectivity Tuning and Troubleshooting Wednesday, May 18 | 3:15 PM - 4:30 PM | Room: B302
COS308 | Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices
Thursday, May 19 | 8:30 AM - 9:45 AM | Room: B213
DBI306 | Using Contained Databases and DACs to Build Applications in Microsoft SQL Server Code-Named "Denali" and SQL Azure
Thursday, May 19 | 8:30 AM - 9:45 AM | Room: B312
Database Platform (DAT) Resources
Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs
Visit the updated website for SQL Server® Code Name “Denali” on www.microsoft.com/sqlserver and sign to be notified when the next CTP is availableFollow the @SQLServer Twitter account to watch for updates
Visit the SQL Server Product Demo Stations in the DBI Track section of the Expo/TLC Hall. Bring your questions, ideas and conversations!
• Microsoft® SQL Server® Security & Management • Microsoft® SQL Server® Optimization and Scalability• Microsoft® SQL Server® Programmability • Microsoft® SQL Server® Data Warehousing• Microsoft® SQL Server® Mission Critical • Microsoft® SQL Server® Data Integration
Resources
www.microsoft.com/teched
Sessions On-Demand & Community Microsoft Certification & Training Resources
Resources for IT Professionals Resources for Developers
www.microsoft.com/learning
http://microsoft.com/technet http://microsoft.com/msdn
Learning
http://northamerica.msteched.com
Connect. Share. Discuss.
Complete an evaluation on CommNet and enter to win!
Scan the Tag to evaluate this session now on myTech•Ed Mobile
© 2011 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.