1 / 41

SQL Azure

SQL Azure. Eric Nelson Application Architect, Microsoft http://bit.ly/ericnelson | http://twitter.com/ericnel Eric.nelson@microsoft.com. SQL Azure. Extending SQL Server to the Cloud. Reporting. Sync Service. Database. SQL Azure Database Familiar SQL Server relational database model

theola
Download Presentation

SQL Azure

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. SQL Azure Eric Nelson Application Architect, Microsoft • http://bit.ly/ericnelson | http://twitter.com/ericnel • Eric.nelson@microsoft.com

  2. SQL Azure Extending SQL Server to the Cloud Reporting Sync Service Database • SQL Azure Database • Familiar SQL Server relational database model • Support for existing APIs & tools • Built for the cloud with high availability & fault tolerance • Easily provision and manage databases across multiple datacenters • Data Sync Service (CTP) • Provides two-way sync of SQL Azure Databases across datacenters • Example service available in the SQL Azure Labs environment • SQL Azure Reporting(CTP) • Reporting Services running on the Windows Azure Platform

  3. SQL Azure Database

  4. SQL Azure Database Service • SQL Azure provides logical SQL Server • Looks like SQL Server to TDS Client • Actual data stored on multiple backend data nodes • Logical optimisations supported • Indexes, Query plans etc.. • Physical optimisations not supported • File Groups, Partitions etc… • Transparently manages physical storage

  5. Working with SQL Azure Databases Your App SQL Azure TDS Change Connection String

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

  7. Application Topologies From Windows Azure From Outside Microsoft Datacenter From Windows Azure & Outside Microsoft Datacenter SQL Server App Code / Tools App Code / Tools Application / Browser SQL Azure Data Sync MicrosoftDatacenter Microsoft Datacenter Microsoft Datacenter Windows Azure Windows Azure SQL Azure SQL Azure SQL Azure Code Near Code Far Hybrid

  8. Demo

  9. Connecting to SQL Azure • SQL Azure connection strings follow normal SQL syntax • Applications connect directly to a database • “Initial Catalog = <db>” in connection string • No support for context switching (no USE <db>) • Encryption security • Set Encrypt = True, only SSL connections are supported • TrustServerCertificate = False, avoid Man-In-The-Middle-Attack! • Format of username for authentication: • ADO.Net:Data Source=server.database.windows.net;User ID=user@server;Password=password;... • Setup your firewall rules first!

  10. SQL Azure Compatibility Currently Supported Not Currently Supported Data Types XML, HierarchyId, Sparse Columns, Filestream Partitions Full-text indexes SQL-CLR • Tables, indexes and views • Stored Procedures • Triggers • Constraints • Table variables, session temp tables (#t) • Spatial types Tables require clustered indexes

  11. Database Editions • You specify Web or Business Edition • Web: EDITION = web • Business: EDITION = business • You specify MAXSIZE • Web: MAXSIZE = 1GB | 5GB • Business: MAXSIZE = 10GB | 20GB | 30GB | 40GB | 50GB • This is the maximum size we will not let you grow beyond • You will only be charged for the actual peak size in any one day rounded up • For example, a 3.4 GB Web Edition will be charged 5GB rate. • CREATE DATABASE foo1 (EDITION='business', MAXSIZE=50GB); • CREATE DATABASE foo2 (EDITION='business', MAXSIZE=30GB); • ALTER DATABASE foo2 MODIFY (EDITION='web', MAXSIZE=5GB); Business Edition Up to 50 GB 10 GB increments Web Edition 1 GB or 5 GB

  12. Database Editions • Two SQL Azure Database SKUs: Web & Business • Web Edition: • 1 GB @ £6.055/month • 5 GB @ £30.275/month • Business Edition: • 10 GB @ £60.604 • 20 GB @ £121.208 • 30 GB @ £131.812 • 40 GB @ £242.416 • 50 GB @ £303.20 Business Edition Up to 50 GB 10 GB increments Web Edition 1 GB or 5 GB

  13. Demo

  14. SQL Azure Data Sync

  15. SQL Azure Data Sync • Powers movement of data • Cloud  cloud • On-premises  cloud • Getting data where you need it • Sync SQL Azure instances • Sync SQL Server to SQL Azure • Sync offline apps to SQL Azure • Enable geo-replication of data SQL Azure Sync

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

  17. Top Features • Service scales as resources requirements grow • No-Code Sync Configuration • Schedule Sync • Conflict Handling • Logging and Monitoring • Data sub-setting

  18. Demo

  19. SQL Azure Reporting

  20. SQL Azure Reporting • CTP – you can register • Based on SQL Server Reporting Services 2008 R2 • Exactly same report formats (no custom extensions yet) • Use BI Development Studio • Reports run on Windows Azure Platform • Report Viewer control using remote processing mode • Only reports against SQL Azure Database

  21. Summary • SQL Azure Database: hassle free fault tolerance with little to learn • SQL Azure Data Sync: Helping build geographically disperse or hybrid applications • SQL Azure Reporting: Reporting Services in the Cloud

  22. Appendix

  23. Latency and Timeouts

  24. Latency On premises application Cloud based application App Data Latency Service Request App Consistent and accepted latencies Files App Data Increases in quantity and variability of latencies Blob Latency Service Request App TIP: Expect Web Role to SQL Azure to take about twice as long

  25. Timeout • Connection Successful? • Tried 5 times? Try to connect Continue doing work Yes On premises application Cloud based application No Service Request Timeouts? App Data App Consistent, reliable connectivity and access No Yes Fail Increased likelihood of timeouts Service Request App Data App

  26. Timeout Retry Code Example

  27. Timeout Retry Code Example

  28. Timeout Retry Code Example TIP: There is a new code library for all this!

  29. Data Sensitivity

  30. Segmentation Online Shop Online Shop Product catalogue Purchasing Database Product catalogue Secure Transaction (Service Bus)

  31. Sharding First Name Shipping Information Last Name Contact ID Customer Credit Card # Contact ID Contact ID Shipping Information First Name Last Name Cloud Customer Credit Card # Social Security # Social Security # Billing Data On Premises Other Information Other Information On Premises

  32. Encryption First Name Shipping Information Last Name Contact ID Customer Credit Card # £!”$&$%!&£% Contact ID Shipping Information First Name Last Name Cloud !£$&!%£&%* Social Security # £”$&!%*^(& Billing Data On Premises Other Information ^$”$&%$”& On Premises

  33. Data choices

  34. You have many choices for data Relational Database Structured Storage Unstructured Storage NTFS Drive Service Communication SQL Azure Azure Table Azure Blob Azure Drive Azure Queue

  35. Side by Side Evaluation (*) Scale out across multiple databases using partitioning for better scalability and performance

  36. Azure Table and SQL Azure Table

  37. Azure Table and SQL Azure Table

  38. Example: Registry/Configuration/User Settings • A traditional set of data that is traditionally stored on the local environment is configuration and user settings. Commonly this is stored in the registry, xml or ini files.

  39. Reporting

  40. Server: Network Access Control • Each server defines a set of firewall rules • Determines access policy based on client IP • By default, there is NO ACCESS to server • Controlled using Firewall API (masterDB) • sys.firewall_rules, sys.sp_merge_firewall_rule and sys.sp_delete_firewall_rule

  41. Server: Billing and Reporting • Usage metrics from views: • sys.bandwidth_usage • sys.database_usage • Bandwidth shows ingress/egress/type in KB • Database shows number/type

More Related