Azure Federation and Scalability

Views:
 
Category: Entertainment
     
 

Presentation Description

In this presentation we review SQL Azure Federation and Scalability. Best Regards, Dr. Eduardo Castro Martinez Microsoft SQL Server MVP http://ecastrom.blogspot.com http://tinyurl.com/comunidadwindows

Comments

Presentation Transcript

Building Scale-out Database Applications with SQL Azure:

Building Scale-out Database Applications with SQL Azure Ing . Eduardo Castro, PhD ecastro@grupoasesor.net http://ecastrom.blogspot.com http://comunidadwindows.org http://tiny.cc/comwindows

Agenda:

Agenda SQL Azure --- what is it for? A quick recap Why scale-out? Building scale-out solutions today Scaling through databases Futures

Microsoft SQL Azure:

Microsoft SQL Azure Information Platform as a Service Scale on Demand Innovate Faster Managed Service Platform capabilities delivered as a service (Database, Data Sync, Reporting…) Reduced complexity, greater flexibility Easy provisioning and deployment Auto High availability and fault tolerant Database as a utility with pay as you grow scaling Rely on Business-ready SLAs Enable multi-tenant solutions Manage multiple servers Innovate with new data application patterns Build cloud-based data solutions on a familiar relational model Build on existing developer skills and familiar Transact-SQL syntax

What is “SQL Azure”?:

What is “SQL Azure”?

Review – Conceptual model:

Review – Conceptual model Subscription Used to map service usage to the billing instrument Users may have many subscriptions Logical Server Akin to SQL Server Instance Unit of Geo-Location & Billing 1:1 Subscription & server User Database Restricted T-SQL surface area Additional catalog views provided e.g. sys.billing , sys.firewall_rules , etc

SQL Azure A relational DB in the cloud:

SQL Services .NET Services Windows Az ure Live Services Applications Applications Others Windows Mobile Windows Vista/XP Windows Server SQL Azure A relational DB in the cloud SQL Azure Database Others (Future) Data Hub Relational database as a service Highly available, automatically maintained Extension of the SQL Server Data Platform

SQL Azure Database :

SQL Azure Database - Based on SQL Server 2008 R2 engine Use same tools and data access frameworks Six global datacenters High Availability & Redundancy Reads are completed at the primary Writes are replicated to a quorum of secondaries Replica 1 Replica 2 Replica 3 DB Single Logical Database Multiple Physical Replicas Single Primary Multiple Secondaries

Extending SQL Server Data Platform to the Cloud:

Extending SQL Server Data Platform to the Cloud Database Data Sync Reference Data Symmetric Programming Model Data Hub Aggregation Initial services – core RDBMS capabilities with SQL Azure Database, Data Sync Future Offerings Additional data platform capabilities: Reporting, BI New services: Reference Data

The New SQL Data Services:

The New SQL Data Services Familiar SQL Server relational model Uses existing APIs & tools Built for the Cloud with availability and scale Accessible to all from PHP, Ruby, and Java Clear Feedback: “I want a database in the Cloud” Focus on combining the best features of SQL Server running at scale with low friction

The Evolution of SDS:

The Evolution of SDS Evolves SDS Next TDS + TSQL Model Web App SQL Client * Windows Azure Browser Application Application REST Client REST (Astoria) ADO.Net + EF Application SQL Client * Cloud HTTP HTTP+REST TDS * Client access enabled using TDS for ODBC, ADO.Net, OLEDB, PHP-SQL, Ruby, … Data Center ODBC, OLEDB, ADO.Net PHP, Ruby, … SDS Current REST/SOAP + ACE Model Web App REST Client Windows Azure Browser Application Application REST Client HTTP HTTP+REST Data Center Cloud

SQL Azure:

SQL Azure Windows Az ure Applications Applications Others Windows SQL Azure Database Others (Future) SQL Azure Windows Azure AppFabric

SQL Azure Database An Illustration:

SQL Azure Database Others (Future) SQL Azure Database An Illustration Database Database Database SQL Azure Database TDS Application

SQL Azure Database Using one or multiple databases:

Database Database Database Application Application Database SQL Azure Database SQL Azure Database Using one or multiple databases Max database size is 50 GB

SQL Azure Network Topology:

SQL Azure Network Topology Application Internet Azure Cloud LB TDS (tcp) TDS (tcp) TDS (tcp) Applications use standard SQL client libraries: ODBC, ADO.Net , PHP, … Load balancer forwards ‘sticky’ sessions to TDS protocol tier Security Boundary SQL SQL SQL SQL SQL SQL Gateway Gateway Gateway Gateway Gateway Gateway Gateway: TDS protocol gateway, enforces AUTHN/AUTHZ policy; proxy to CloudDB Scalability and Availability: Fabric, Failover, Replication, and Load balancing

TDS Gateway:

TDS Gateway TDS Listener Capability negotiation TDS Packet inspection Security Logical->Physical mapping via metadata catalog Enabler for multi-tenet capabilities Isolation layer

TDS Gateway Layering:

TDS Gateway Layering Gateway Process TDS Endpoint AdminSvc Endpoint Protocol Parser Provisioning Endpoint Business Logic Services Connection Mgmt SQL SQL SQL SQL SQL SQL Scalability and Availability: Fabric, Failover, Replication, and Load balancing

Provisioning :

Provisioning Subscription Coordinated across all Azure services Executed in parallel w/retries Server May occur between data centers Point where Geo-location is established Database Always occurs within a single data center Cross node operations executed during this process e.g. add new db to sys.databases on the master

Server Provisioning:

Server Provisioning Driven by administrator Portal Provision request is sent to Gateway Metadata catalog entry created DNS record (CNAME) created within LiveDNS service Master DB created On completion metadata catalog updated

SQL Azure Server Provisioning:

Datacenter (Sub-Region) SQL Azure Server Provisioning Customer Browser Portal LB 1 Front-end Node Live DNS Cluster Live DNS Svc 2 4 Gateway Backend Node Mgmt. Services SQL Server Fabric Backend Node Backend Node Mgmt. Services SQL Server Fabric Mgmt. Services SQL Server Fabric Front-end Node Gateway Front-end Node Admin Portal Front-end Node Admin Portal 3 Gateway LB 5 6 7

Database Provisioning:

Database Provisioning Gateway performs stateful TDS packet inspection Picks out subset of messages Parses out args for create database Makes entry into Gateway metadata catalog Unused replica set located and reserved Replica set ( UserDB ) is prepped for use Metadata catalog is updated

SQL Azure Database provisioning:

SQL Azure Database provisioning TDS Gateway Front-end Node Protocol Parser Gateway Logic Master Cluster Master Node Master Node Components 1 2 3 5 6 7 Scalability and Availability: Fabric, Failover, Replication, and Load balancing Scalability and Availability: Fabric, Failover, Replication, and Load balancing 4 8 TDS Session Backend Node 1 SQL Instance SQL DB Backend Node 2 SQL Instance SQL DB Backend Node 3 SQL Instance SQL DB

Provisioning Model:

Provisioning Model Account Server Database Each account has zero or more servers Establishes a billing instrument Each server has one or more databases Logical concept equal to a master DB Unit of authentication , geo-location , billing, reporting Generated DNS-based name Each database has standard SQL objects Users, Tables , Views, Indices, etc Unit of consistency

Scaling database applications:

Scaling database applications Scale up Buy large-enough server for the job But big servers are expensive! Try to load it as much as you can But what if the load changes? Provisioning for peaks is expensive! Scale-out Partition data and load across many servers Small servers are cheap! Scale linearly Bring computational resources of many to bear 800 little servers is very fast Load spikes don’t upset us Load balancing across the entire data center

Scale-out with SQL Azure Today:

Scale-out with SQL Azure Today Elastic Provisioning of Databases CREATE DATABASE and go No VMs, no servers Pay-as-you-go business model Don’t need it --- DROP it Zero Physical Administration Built-in High Availability, patching, maintenance Database Copy, SQL Azure Data Sync

Ticket Direct:

Ticket Direct Solution Elastic scale – database as a service Pay as you grow and shrink Easy to provision and manage database No hardware, no manual database administration required Promotions, events, ticket selling businesses are “bursts – bound” by nature Capacity constraints limit business agility High costs of entry into new business Difficult to roll out extra capacity quickly Idle capacity “off-bursts” is cost prohibitive Capacity Time “ Capacity Bursting“ Average Usage Average Usage Compute # of Hr’s Challenges Today

Scale-out for Multi-tenant applications:

Scale-out for Multi-tenant applications Put everything into one DB? Too big… Create a database per tenant? Not bad… Sharding Pattern: better Application is already prepared for it! T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 T13 T14 T15 T16 T17 T18 T19 T20 T1 T2 T3 T4 T5 T6 T7 T8 T9 T10 T11 T12 T13 T14 T15 T16 T17 T18 T19 T20 All my data i s handled by one DB on one server

Sharding Pattern:

Sharding Pattern Linear scaling through database independence No need for distributed transactions in common cases Application-influenced partitioning Rather than complete transparency Local access for most Connection routing Query, transaction scoping Distributed access for some Fan-out expensive computation App

DIY Sharding: Problems:

DIY Sharding : Problems Provisioning Growing and shrinking capacity Managing Upgrading, patching, HA for lots of databases Routing Where is the directory? How to scale it and use it? Partition Management Splitting and Merging, without loss of availability Fan-out Covered by SQL Azure today Coming up in SQL Azure: Federations 2011

Distribution of Data:

Distribution of Data Partitioned Spread across member machines Each piece is on one machine (+HA) Most of the data! Centralized Only available in one place Read and write, but not too much Replicated Copied to all member machines Can be read anywhere (reference) Should not be written too much Data1 ref Data2 ref Data3 ref Data4 ref Data5 ref Con-fig

SQL Azure Federations: Concepts:

SQL Azure Federations: Concepts Federation Represents the data being sharded Federation Key The value that determines the routing of a piece of data Atomic Unit All rows with the same federation key value: always together! Federation Member (aka Shard) A physical container for a range of atomic units Federation Root The database that houses federation directory Root Federation “ CustData ” Member: [min, 100) AU PK=5 AU PK=25 AU PK=35 Member: [100, 488) AU PK=105 AU PK=235 AU PK=365 Member: [488, max) AU PK=555 AU PK=2545 AU PK=3565 (Federation Key : CustID )

Creating a Federation:

Creating a Federation Create a root database CREATE DATABASE SalesDB Location of partition map Houses centralized data Create the federation in root CREATE FEDERATION Orders_Fed (RANGE BIGINT) Specify name, federation key type Start with integral, guid types Creates the first member, covering the entire range SalesDB Federation “ Orders_Fed ” (Federation Key: CustID ) Member: [min, max)

Create Database & Federation:

Create Database & Federation CREATE DATABASE SalesDB ( EDITION=' business',MAXSIZE =50GB ) connect to SalesDB … CREATE FEDERATION Orders_Federation (RANGE BIGINT ) Create a Federation use a BIGINT as the Federation Key COS222 What’s New in Microsoft SQL Azure, David Robinson

Create Partitioned Table:

Establish the customerid as the Federation Key Create Partitioned Table USE FEDERATION Orders_Federation (0) WITH RESET CREATE TABLE orders( customerid bigint , orderid bigint , odate datetime , primary key ( orderid , customerid )) FEDERATE ON ( customerid ) CREATE TABLE orderdetails ( customerid bigint , orderdetailid bigint , orderid bigint , partid bigint , primary key ( orderdetailid , customerid )) FEDERATE ON ( customerid ) Data with the same customerid in these two tables should be treated as an Atomic Unit

Insert Some Data:

Insert Some Data INSERT INTO orders VALUES(10,1,getdate()), (10,2,getdate()), (11,3,getdate()) INSERT INTO orders VALUES(110,11,getdate()), (110,12,getdate()), (111,13,getdate()) INSERT INTO orders VALUES(210,21,getdate()), (210,22,getdate()), (211,23,getdate()) INSERT INTO orders VALUES(310,31,getdate()), (310,32,getdate()), (311,33,getdate()) INSERT INTO orderdetails VALUES(10,1,1,1), (10,2,1,2), ( 10,3,1,10),(10,4,2,100),(10,5,2,1000), (11,6,3,101) INSERT INTO orderdetails VALUES(110,11,11,1), (110,12,11,2), ( 110,13,11,10),(110,14,12,100),(110,15,12,1000), (111,16,13,101) INSERT INTO orderdetails VALUES(210,21,21,1), (210,22,22,2), ( 210,23,21,10),(210,24,22,100),(210,25,22,1000), (211,26,23,101) INSERT INTO orderdetails VALUES(310,31,31,1), (310,32,31,2), ( 310,33,31,10),(310,34,32,100),(310,35,32,1000), (311,36,33,101 ) COS222 What’s New in Microsoft SQL Azure, David Robinson

Simple SELECT:

Simple SELECT SELECT * FROM ORDERS RESULTS -------- ORDER 10 ORDER 110 ORDER 210 ORDER 310 COS222 What’s New in Microsoft SQL Azure, David Robinson

Creating the schema:

Creating the schema Federated tables CREATE TABLE orders (…) FEDERATE ON ( customerId ) Federation key must be in all unique indices Part of the primary key Value of federation key will determine the member Reference tables CREATE TABLE zipcodes (…) Absence of FEDERATE ON indicates reference Centralized tables Create in root database Federation “ Orders_Fed ” (Federation Key: CustID ) Member: [min, max) SalesDB orders Products zipcode

Splitting and Merging:

Splitting and Merging Splitting a member When too big or too hot ALTER FEDERATION Orders_Fed SPLIT (100) Creates two new members Splits (filtered copy) federated data Copies reference data to both Online! Merging members When too small ALTER FEDERATION Orders_Fed MERGE (200) Creates new member, drops old ones Federation “ Orders_Fed ” (Federation Key: CustID ) Member: [min, max) SalesDB orders Products zipcode Member: [min, 100) orders zipcode Member: [100, max) zipcode orders

Split Database:

Split Database USE FEDERATION ROOT WITH RESET ALTER FEDERATION orders_federation SPLIT AT 100 Federation Root houses the Directory Split the Federation Member into two at the customerid 100 boundary COS222 What’s New in Microsoft SQL Azure, David Robinson

SELECT with Federation Key:

SELECT with Federation Key USE FEDERATION Orders_Federation (0) WITH RESET SELECT * FROM ORDERS RESULTS -------- ORDER 10 USE FEDERATION Orders_Federation (100 ) WITH RESET SELECT * FROM ORDERS RESULTS -------- ORDER 110 ORDER 210 ORDER 310 Use Federation Member that contains customerids < 100 Use Federation Member that contains customerids > 100

Connecting and Operating:

Connecting and Operating Connect to atomic unit USE FEDERATION Orders_Fed (56) WITH FILTERING=ON Connection routed to member containing 56 Only data with federation key value 56 is visible Plus reference data Safe: atomic unit can never be split Connect to entire federation member USE FEDERATION Orders_Fed (56) WITH FILTERING=OFF Connection routed to member containing 56 All data within the member database is visible Dangerous: federation member can be split Member: [min, 100) AU PK=5 AU PK=25 AU PK=56 App zipcode

Schema Distribution:

Schema Distribution Federation members can have different schemas at a point in time: Temporary, while schemas are being upgraded Temporary, while customer is testing new schema on some shards Permanently, because shards are different To alter schema: M anually Connect to each federation member (USE FEDERATION Orders(56) WITH FILTER=OFF) Alter it (ALTER TABLE Customers …) Future: schema-distribution service Connect to root Manage and apply schemas asynchronously

DEMO:

DEMO Federation Split

Sharding in SQL Azure: Beyond v1:

Sharding in SQL Azure: Beyond v1 Schema Management Allow multi version schema deployment and management across federation members. Fan-out Queries Allow single query that can process results across large number of federation members. Auto Repartitioning SQL Azure manages the federated databases for you through splits/merges based on some policy (query response time, db size etc ) Multi Column Federation Keys Federate on enterprise_customer_id+account_id

Summary:

Summary Scale-out is the way to build cloud solutions You can build scale-out solutions with SQL Azure today Provisioning, auto-management, pay-as-you-go are your friends Sync and Database Copy help Implement sharding pattern today --- slide into federation support tomorrow! Federation support (2011) will make it a lot easier Partition management Routing On-line splitting and merging We appreciate your feedback!

Preguntas? Q&A:

Preguntas? Q&A

PowerPoint Presentation:

© 2009 Microsoft Corporation. All rights reserved. Microsoft, MSDN, the MSDN logo, and [list other trademarks referenced] are trademarks of the Microsoft group of companies. 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.

Announcement Title:

Announcement Title Walk-through: setup CREATE FEDERATION Orders_Fed (RANGE BIGINT) USE FEDERATION Orders_Fed (0) WITH FILTERING=OFF CREATE TABLE orders( orderid bigint , odate datetime , customerid bigint , primary key ( orderid , customerid )) FEDERATE ON ( customerid ) CREATE UNIQUE INDEX o_idx1 on orders( customerid , odate ) CREATE INDEX o_idx2 on orders( odate ) CREATE TABLE orderdetails ( orderdetailid bigint , orderid bigint , partid bigint , customerid bigint , primary key ( orderdetailid , customerid )) FEDERATE ON ( customerid ) ALTER TABLE orderdetails add constraint orderdetails_fk1 foreign key( orderid,customerid ) references orders( orderid,customerid ) CREATE TABLE uszipcodes ( zipcode nvarchar (128) primary key, state nvarchar (128))

Announcement Title:

Announcement Title Walk-through: real work Connect to: InitialCatalog =‘ SalesDB ’ – get some regular work done (within customer 239) USE FEDERATION Orders_fed (239) WITH FILTERING=ON SELECT * FROM Orders JOIN OrderDetails ON … INSERT INTO Orders ( customerid , orderid , odate ) VALUES (239, 2, ‘5/7/2010’) – get some cross-customer work done USE FEDERATION Orders_fed (0) WITH FILTERING=OFF DELETE from Orders WHERE odate < ‘1/1/2000’ - Repeat for other members… -- go back to root USE FEDERATION ROOT UPDATE CleanupSchedule set LastCleanupDate = GETSYSTIME()

Announcement Title:

Announcement Title Walk-through: ups and downs! --Day#2 business grows! ALTER FEDERATION Orders_Fed SPLIT AT(1000) --Day#3 black friday! ALTER FEDERATION Orders_Fed SPLIT AT(100) ALTER FEDERATION Orders_Fed SPLIT AT(200,300,400…) --Day#4 recession hits! ALTER FEDERATION Orders_Fed MERGE AT(100) --Day#5 oh boy… double dip. ALTER FEDERATION Orders_Fed MERGE AT(200,300,400…)

Database Copy:

South Central US abc.database.windows.net Dev1 Master xyz.database.windows.net prod1 Master CREATE DATABASE xyz .prod2clone AS COPY OF xyz.prod2 CREATE DATABASE efg .prod2clone AS COPY OF xyz.prod2 prod2 clone prod2 copy prod2 clone prod2 copy North Central US efg.database.windows.net DR1 Master prod2 clone prod2 copy CREATE DATABASE abc .prod2clone AS COPY OF xyz.prod2 Database Copy Prod2

SQL Azure Data Sync – V1 Overview:

SQL Azure Data Sync – V1 Overview On-Premises (Headquarters ) Sync Sync Remote Offices Data Sync Service For SQL Azure Retail Stores Sync Sync Sync SQL Azure Database Sync Sync CTP1 CTP2

authorStream Live Help