SQL Azure Database Under the hood

1. SVC12 SQL Azure DatabaseUnder the hood Jeff Currier Senior Dev Lead Microsoft Corporation

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

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

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

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

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

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

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

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

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

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

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

13. SQL Azure Login Process TDS Gateway 7 1 Front-end Node TDS Session Protocol Parser 6 2 Gateway Logic Master Node Global Partition Map Master Node Components 8 3 4 5 Backend Node 1 Backend Node 2 Backend Node 3 SQL Instance SQL Instance SQL Instance SQL DB SQL DB SQL DB Scalability and Availability: Fabric, Failover, Replication, and Load balancing Scalability and Availability: Fabric, Failover, Replication, and Load balancing

14. 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)

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

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

17. 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?

18. YOUR FEEDBACK IS IMPORTANT TO US! Please fill out session evaluation forms online at MicrosoftPDC.com

19. Learn More On Channel 9 • Expand your PDC experience through Channel 9. • Explore videos, hands-on labs, sample code and demos through the new Channel 9 training courses. channel9.msdn.com/learn Built by Developers for Developers….