SQL Azure Under the Hood

SQL Azure DatabaseUnder the hood : 

SQL Azure DatabaseUnder the hood Ing. Eduardo Castro, PhD Comunidad Windows ecastro@grupoasesor.net http://ecastrom.blogspot.com

Agenda : 

Agenda Service Review SQL Azure Architecture & Workflows Service Resilience Service Monitoring Attack Vectors/Security considerations Wrap up

What is “SQL Azure”? : 

What is “SQL Azure”?

The Azure Services PlaformAn illustration : 

The Azure Services PlaformAn illustration

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 AzureA relational DB in the cloud : 

SQL Services .NET Services Windows Azure Live Services Applications Applications Others Windows Mobile Windows Vista/XP Windows Server SQL AzureA relational DB in the cloud Relational database as a service Highly available, automatically maintained Extension of the SQL Server Data Platform

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

SQL Azure Network Topology : 

SQL Azure Network Topology Application Internet Azure Cloud LB 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 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 Connection Mgmt 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 1 2 3 5 6 7 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

SQL Azure Login Process : 

SQL Azure Login Process Login request arrives at the Gateway Gateway locates MasterDb & UserDb replica sets Credentials are validated against MasterDb TDS session is opened to UserDB and requests are forwarded

SQL Azure Login Process : 

SQL Azure Login Process TDS Gateway Front-end Node Protocol Parser Gateway Logic 1 2 4 5 6 3 7 TDS Session Backend Node 1 SQL Instance SQL DB Backend Node 2 SQL Instance SQL DB Backend Node 3 SQL Instance SQL DB 8

Service Resilience : 

Service Resilience Provisioning State machines used to coordinate activities across node (and datacenter) boundaries Failed provisioning attempts cleaned automatically after 10 minutes Login Failovers during the login will be transparent (<30 seconds) Metadata catalog refresh occurs automatically Active Session Surface as connection drops (due to state)

Monitoring Service Health : 

Monitoring Service Health Metrics Cluster wide performance counters gather key metrics on the service Used to alert Operations to issues before they become a problem Early warning system Code issues Capacity warnings Health Exercises the service routinely looking for problems When issues are encountered runs deep diagnostics Network connectivity at the node level Validate all dependent services (Live DNS, Live ID, etc) Monitoring from other MSFT DC’s Validates accessibility from multiple geographic locations Alerts fired automatically when test jobs fail

Security/Attack Considerations : 

Security/Attack Considerations Service Secure channel required (SSL) Denial Of Service trend tracking Packet Inspection Server IP allow list (Firewall) Idle connection culling Generated server names Database Disallow the most commonly attacked user id’s (SA, Admin, root, guest, etc) Standard SQL Authn/Authz mode

Wrap Up : 

Wrap Up Reviewed SQL Azure Architecture & Workflows Provisioning (Server & DB) Login Service Resilience & Health Failure detection and correction How we determine service health Security considerations Attack vectors and mitigations Questions?

Links : 

Links http://comunidadwindows.org http://ecastrom.blogspot.com http://www.sqlazurelabs.com http://www.microsoft.com/windowsazure/ http://sql.azure.com/

Q&A : 

Q&A