1 / 32

Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

DBI403. Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations. Cihan Biyikoglu Program Manager Microsoft SQL Azure. What are Federations in SQL Azure?.

starr
Download Presentation

Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBI403 Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations Cihan Biyikoglu Program Manager Microsoft SQL Azure

  2. 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 Scalability • Dynamic and Online Elasticity • Simplified Multi-Tenancy

  3. 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 capacity • Utilize unlimited storage

  4. 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

  5. Efficient Tenancy Models • Classic Tenancy Model On Premise • Single-Tenant-Per-Database • Cloud Tenancy Models • Single-database-per-tenant does not work for long tail and large tenants • Utilize 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 =57 Tenant_id =55 Tenant_id =56

  6. Few Typical Scenarios - 10Kft • Web Scale DB Solutions • Scale 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…)

  7. NoSQL Gene in SQL Azure Federations • NoSQL: Typically a different compromise on the CAP Theorem. • …As do SQL Azure Federations… • Scale-out for massive parallelism • First class sharding support in the platform • Loosened Consistency Mode • Eventual Consistency across members – members have independent schemas • Local consistency within members – members are databases with full ACID properties • Lightweight local storage as well as permanent consistent storage • With each federation member, you also get access to the local tempdb • Support for Unstructured and Semi Structured Data • XML, Blob types and Key-Value Pairs

  8. 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. Federations SalesDB CustomerFederationeration CustomerFederationeration CustomerFederation Federation Root Federation Members

  9. Overview - Concepts • Federation Distribution Key • The 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 Unit • Represent 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 Root • The database that houses federation object and is the central repository for information about distribution of scaled-out data. Federations member: Range [1000, 2000) SalesDB CustomerFederationeration AUPK=5 AUPK=25 AUPK=35 CustomerFederationeration CustomerFederation AUPK=5 AUPK=25 AUPK=35 AUPK=1005 AUPK=1025 AUPK=1035 Federation Root Atomic Units Federation Members

  10. Overview - Concepts cont. • Federated Tables • Refer 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 Tables • Refer 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 Table • Refer to tables that are created in the federation root for typically low traffic objects such as metadata. Global Tables Federations SalesDB Federated Tables and Reference Tables CustomerFederationeration CustomerFederationeration CustomerFederation User Database and Federation root Federation members

  11. Overview – Architecture cont. ALTER FEDERATION CustomerFederation SPLIT AT (tenant_id=7500) • 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_federation CustomerFederationeration CustomerFederation Range Distribution [min,1000, 2000, 3000 ….. 5000, 10000, Max]

  12. Overview – Architecture cont. -- Connect to Root Database USE FEDERATION CustomerFederation(customer_id = 5075) … • 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 CustomerFederationeration CustomerFederationeration CustomerFederation Range Distribution [min,1000, 2000, 3000 ….. 5000, 10000, Max]

  13. Federations deep dive

  14. Sample with Multi Tenant App • Plot: Sales application tracking orders from tenants and managing a large product catalog. • Database Name = AdventureWorks • Federations = • CustomerFederationwith (cidbigint RANGE) • Federation distribution key = cid • Federated Tables = Customers, CustomerAddress, … • Regular Tables = Zipcodes • Products_Fed with (product_idvarbinary(64) RANGE) • Federation distribution key = product_id

  15. Sample Scenario – Multi-Tenant App -- Day#1 – Deploy CREATE DATABASE SalesDB GO CREATE FEDERATION CustomerFederation(cidBIGINT RANGE) GO CREATE FEDERATION ProductFederation(pidVARBINARY(64) RANGE) GO member: Range [MIN, NULL) SalesDB CustomerFederation ProductFederation

  16. Sample Scenario – Multi Tenant App -- Connect to the federation member USEFEDERATIONCustomerFederationeration(cid= 0) WITH FILTERING=OFF, RESET GO CREATETABLE [dbo].[Customer]( [CustomerID] [bigint] NOTNULL, [Title] [nvarchar](8)NULL, … CONSTRAINT [PK_Customer] PRIMARYKEYCLUSTERED([CustomerID] ASC) ) FEDERATED ON (cid=CustomerID) GO CREATETABLE [dbo].[CustomerAddress]( [AddressID] [int] NOTNULL, [CustomerID] [bigint] NOTNULL, … CONSTRAINT[PK_CustomerAddress] PRIMARYKEYCLUSTERED ([CustomerID],[AddressID] ASC) ) FEDERATED ON (cid=CustomerID) GO CREATETABLE [dbo].[PostalCode]( [PostalCodeID] [int] NOTNULL, [PostalCode] [nvarchar](15)NOTNULL, … CONSTRAINT [PK_PostalCode] PRIMARYKEYCLUSTERED([PostalCodeID] ASC) ) GO member: Range [MIN, NULL) SalesDB PostalCode (reference) CustomerFederation CustomerFederation Customer (federated) CustomerAddress (federated)

  17. Sample Scenario – Multi Tenant App -- Day#2 business grows! ALTERFEDERATION CustomerFederationSPLIT AT(cid = 1000) GO -- Day#3 black friday! (future) ALTERFEDERATION CustomerFederationSPLIT AT(cid = 100,200,300,…900) GO -- Day#4 recession hits! (future) ALTERFEDERATION CustomerFederationMERGE AT(cid = 100,200,300,…900) GO -- Day#5 oh shoot! it is a double dip. (future) ALTERFEDERATION CustomerFederationMERGE AT(cid= 1000) GO SalesDB CustomerFederationeration CustomerFederationeration CustomerFederation

  18. SPLIT in Action App member: Range [100,200) Member: Range [100,150) Member: Range [150,200) PostalCode (reference) Customer (federated) CustomerAddress (federated)

  19. Online Repartitioning with Federations Thanks 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… demo

  20. Connecting to FederationsFiltering Connections • FILTERING=ON • Connect 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 model • Leakage 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=ON GO member: Range [100,200) SalesDB Customer_id=55 Uszipcodes (referece) CustomerFederation CustomerFederation orders (federated) orderdetails (federated) Customer_id=55 Microsoft Confidential

  21. Microsoft Confidential Connecting to FederationsMember Connections • FILTERING=OFF • Connects to the full member • Unrestricted Access to the member: Same as connecting to the database name • DDL, DML and Access to All Atomic Units within the members allowed • Good for… • Management Tasks: Schema Deployment • Fan-out Querying – queries spanning multiple atomic units Connection to Root - SqlClient, ODBC “Server=tcp:servername.db.windows.net;Db=salesdb;UserID=uname;Password=pword;Encrypt=True” -- THEN USE FEDERATION TO CONNECT OT THE MEMBER CONTAINING THE KEY. USE FEDERATION CustomerFederation(cid = 55)WITH RESET, FILTERING=OFF GO Customer_id=55 member: Range [100,200) SalesDB Uszipcodes (referece) CustomerFederation CustomerFederation orders (federated) orderdetails (federated) Microsoft Confidential

  22. 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 Experience • Robust Connection Management • Federation Directory for connection routing • Filtering connections for safe programming model • Deliver Superior Management Experience • Online data redistribution through operations such as SPLIT.

  23. Federations – Future Enhancements • Fan-out Queries: Ability to query multiple members with a single query • Schema 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.

  24. Federation Technology Preview Nominations • Now Open! Federations Technology Preview Program Nominations • Information on How to Nominate your Application • http://blogs.msdn.com/cbiyikoglu/ • Click on the Survey Link • Fill-out the Survey Questions • Wait for communication from the preview team!

  25. Q&A • Send us your feedback email: cihangib@microsoft.com blog: http://blogs.msdn.com/b/cbiyikoglu/

  26. 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

  27. Database Platform (DAT) Resources • Visit the updated website for SQL Server® Code Name “Denali” on www.microsoft.com/sqlserverand sign to be notified when the next CTP is available • Follow the @SQLServer Twitter account to watch for updates Try the new SQL Server Mission Critical BareMetal Hand’s on-Labs • Visit the SQL Server Product Demo Stations in the DBI Track section of the Expo/TLC Hall. Bring your questions, ideas and conversations!

  28. Resources • Connect. Share. Discuss. http://northamerica.msteched.com Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

  29. Complete an evaluation on CommNet and enter to win!

  30. © 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.

More Related