460 likes | 621 Vues
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
E N D
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 • 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
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
Working with SQL Azure Databases Your App SQL Azure TDS Change Connection String
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
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
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!
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
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
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
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
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)
Top Features • Service scales as resources requirements grow • No-Code Sync Configuration • Schedule Sync • Conflict Handling • Logging and Monitoring • Data sub-setting
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
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
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
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
Timeout Retry Code Example TIP: There is a new code library for all this!
Segmentation Online Shop Online Shop Product catalogue Purchasing Database Product catalogue Secure Transaction (Service Bus)
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
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
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
Side by Side Evaluation (*) Scale out across multiple databases using partitioning for better scalability and performance
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.
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
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