1 / 27

Intro to SQL Azure

Intro to SQL Azure. Name Title Microsoft Corporation. Session Objectives and Takeaways. Session Objective(s): Get up to speed on SQL Azure Learn about the architecture and cloud specific features of SQL Azure Demonstration of some core features in action Takeaway

jasia
Download Presentation

Intro to 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. Intro to SQL Azure Name Title Microsoft Corporation

  2. Session Objectives and Takeaways • Session Objective(s): • Get up to speed on SQL Azure • Learn about the architecture and cloud specific features of SQL Azure • Demonstration of some core features in action • Takeaway • SQL Azure is uniquely positioned as an ideal data platform for Windows Azure applications

  3. SQL Azure DatabaseThe first and only true relational database as a service Subtitle color Self-managed Elastic Scale Developer Agility • Build cloud-based database solutions on consistent relational model • Leverage existing skills through existing ecosystem of developer and management tools • Database utility; pay as you grow • Business-ready SLAs • Enable multi-tenant solutions • World-wide presence • Easy provisioning and deployment • Auto high-availability and fault tolerance • No need for server or VM administration

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

  5. Architecture • Shared infrastructure at SQL database and below • Request routing, security and isolation • Scalable HA technology provides the glue • Automatic replication and failover • Provisioning, metering and billing infrastructure

  6. Database Replicas ! Single Database Multiple Replicas Replica 1 Single Primary Replica 2 DB Replica 3 Replica 4

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

  8. Service Provisioning Model Account • Each account has zero or more logical servers • Provisioned via a common portal • Establishes a billing instrument • Each logical server has one or more databases • Contains metadata about database & usage • 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 Server Database

  9. Working With SQL Azure

  10. Connection Model • SQL Azure exposes native SQL Server TDS protocol • Use existing client libraries • ADO.NET, ODBC, PHP • Client libraries pre-installed in Windows Azure roles • Support for ASP.NET controls • Clients connect directly to a database • Cannot hop across DBs (no USE)

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

  12. Logical vs. Physical Administration • SQL Azure focus on logical administration • Schema creation and management • Query optimization • Security management (Logins, Users, Roles) • Service handles physical management • Automatically provides HA “out of box” • Transparent failover in case of failure • Load balancing of data to ensure SLA DBA role places more focus on logical management

  13. Deployment • Deploy via T-SQL scripts • Support for SQL Server Data-Tier Applications (DAC) feature • DACPAC is unit of deployment • Cloud or on-premise is a deployment time choice • Create Logical Server in same region as Windows Azure Affinity Group for code-near architecture Support existing and new forms of deployment

  14. Security Model • Uses regular SQL security model • Authenticate logins, map to users and roles • Authorize users and roles to SQL objects • Support for standard SQL Auth logins • Username + password Security model is 100% compatible with on-premise SQL

  15. demo Lap Around SQL Azure Name Title Company

  16. Sample of SQL Compatibility Currently Supported Not Currently Supported Distributed Transactions Distributed Query CLR Service Broker Physical server or catalog DDL and views • Tables, indexes and views • Stored Procedures • Triggers • Constraints • Table variables, session temp tables (#t) • Spatial

  17. Spatial Support • 2D Vector Data: • Geography – Round-Earth data • Geometry – Flat-Earth data • Support for Point, Line, Area (Polygon) objects • Over 70 T-SQL spatial methods • High-performance spatial indices • Spatial industry standard support • Symmetry w/SQL Server 2008

  18. Database Editions • Two SQL Azure Database SKUs: Web & Business • Web Edition: 1 GB @ $9.99/month | 5 GB @ $49.95/month • Business Edition: Up to 50 GB @ $99.99/10 GB/month10 GB @ $99.99 | 20 GB @ $199.98 | 30 GB @ $299.97 | 40 GB @ $399.96 | 50 GB @ $499.95 • 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

  19. Scale Out with SQL Azure

  20. Scale Out SQL Azure • Large Data Sets and/or Massive Throughput • Partition data across many databases • Use parallel fan-out queries to fetch the data • Application code must be partition aware • Just-In-Time Partitioning • Partition just for peak load periods • Run partitioned databases in cloud;consolidate into single database on-premise

  21. Sharding Databases • 1 x 20 GB database = 1 Machine • 20 x 1 GB databases = 20 Machines

  22. SQL Azure Advanced Features

  23. OData Support Open Data Protocol JSON ATOM PUB HTTP https://www.sqlazurelabs.com Sign up and send us your feedback!

  24. Project “Houston”

  25. SQL Azure Data Sync Service • Benefits • Scale-out read or read/write • Geo replication of data • Edge network data distribution SQL Azure Sync Group SQL Azure SQL Azure

  26. Summary SQL Azure provides highly available RDBMS in the cloud. • Broad SQL Server feature equivalence • Partition for Size and Load • Spatial features

More Related