Introduction to Windows Azure and Windows Azure SQL Database

• Azure Overview • What is SQL Azure? • Value Proposition • Usage scenarios • Concepts & Architecture • What is there and what is not • Federation and Reporting

Infrastructure Platform As a Software As a As a Service Service Service • Uses VMs • Provides APIs • Web Front End to Software • Consumer manages, patches and • Building Block Services • Multi-Tenant monitors machines • Provider manages VMs and • Logical Separation of data • Good for Legacy Apps patches • Very little customisation • Not great for scalability • Requires a migration of apps • Commodity • Great opportunity for scalability

Infrastructure Platform As a Software As a As a Service Service Service Windows Azure Office 365

At Microsoft:  1 billion: Windows Live ID authentications each day  3 to 4 billion: the # of emails filtered daily from >6 million mail boxes by Forefront  2 billion: the # of queries each month Bing  450 million: Hotmail users (now 16 years old)  100 million: users that get Windows Update worldwide, (now 14 years old)  20 million: Xbox Live users (now 10 years old)  +400,000: square footage of new datacenters

More than 10 and less than 100 DCs worldwide Plus multiple global CDN locations Dublin Amsterdam Quincy Chicago Japan Hong Kong Boydton Des Moines San Antonio Singapore Quincy, Washington: approx 500K sq ft, 27MW, uses entirely hydro-electric power San Antonio, Texas: approx 477K sq ft, 27MW, uses recycled water for cooling Chicago, Illinois: 707K square feet with critical power of 60 MW, uses water side economization, containers Dublin, Ireland: approx 570K sq ft, up to 27MW, uses outside air for cooling.

Defining Windows Azure Architecture Page 12

Compute Services in Windows Azure GOAL: Massive Scalability Page 13

Storage Services in Windows Azure GOAL: Scalable, durable utility-based storage  Windows Azure storage is an application managed by the Fabric Controller  Windows Azure applications can use native storage, Windows Azure SQL Database, or any other mechanism within a Windows Azure VM  Application state is kept in storage services, so worker roles can replicate as needed  Blobs can be stored in global Windows Azure Content Delivery Network (CDN) Page 14

Windows Azure Virtual Machines GOAL: Flexible, No Lock-in  Windows Azure Virtual Machines Microsoft Data Center can run Windows AND Linux  Use standard VHD format, easy to move between Windows Azure and On-Premises Data Centers  Equally easy to directly move to another service provider that supports VHDs  Windows Azure Virtual Machines have persistent drives  Continuous Storage Geo- replication, with Opt-Out discount Your Data Center Page 15

Windows Azure Web Sites GOAL: Build with familiar tools, Deploy in Seconds, Start for free  Build with ASP.NET, Node.js or PHP  Use Windows, Mac or Linux machines for development  Deploy using FTP, Git or TFS  Deploy from Gallery, includes DotNetNuke, Drupal, Joomla, WordPress  Start with free shared instance, Scale up and Scale Out as traffic grows Page 16

Windows Azure Web Sites GOAL: Build with familiar tools, Deploy in Seconds, Start for free  Build with ASP.NET, Node.js or PHP  Use Windows, Mac or Linux machines for development  Deploy using FTP, Git or TFS  Deploy from Gallery, includes DotNetNuke, Drupal, Joomla, WordPress  Start with free shared instance, Scale up and Scale Out as traffic grows  10 Free Shared Instances Page 20

Other Services Windows Azure Service Service Bus Service Application Windows Azure Active Service Directory Access Control Application SDS Authority Page 21

Extending SQL Server to the Cloud Windows Azure SQL Database On-premises SQL Server Business Data Sync Reporting Intelligence Database Symmetric Programming Model and tools Future Offerings Additional data platform capabilities: BI New services: Data and Web Services Page 22

Workload Patterns Optimal for Cloud “On and Off” “Growing Fast” Compute Compute Inactivity Period Average Usage Average Usage Time Time Business need: stay ahead of steady Business need: Periodic batch process but fast growing customer demand Ex. scenario: Credit card co. doing risk Ex. scenario: Digital syndication scoring for portfolio on a monthly basis Evidence: RiskMetrics financial analysis, Evidence: Associated Press Breaking monte carlo simulations News API 23

Workload Patterns Optimal for Cloud “Unpredictable Bursting” “Predictable Bursting” Compute Compute Average Usage Average Usage Time Time Business need: safely handle highly Business need: respond to periodic unpredictable spikes in customer demand peaks in customer usage and business cycle Ex. scenario: Marketing campaigns, event Ex. scenario: Ecommerce system, ticketing, retail fads, disaster internal payroll portal, corporate website response/recovery Evidence: TicketDirect ticketing system Evidence: Kelley Blue Book website 24

Workload Patterns not for the Cloud today Compute Business need: extreme uptime Business need: specific latency requirements, extreme transaction requirements, single transaction scope processing speeds across large database Ex. scenario: Credit Card Authorization, Ex. scenario: Betting exchanges, Stock Exchanges Commodity exchanges, Global Airline Reservation Distribution Systems 25

Presenter should insert her / his own case study 26

Windows Azure SQL Database Network Topology Applications use standard SQL client libraries: Application ODBC, ADO.Net, … TDS (tcp:1433) Load balancer forwards ‘sticky’ sessions to Load Balancer TDS protocol tier TDS (tcp: 1433) Gateway Gateway Gateway Gateway Gateway Gateway TDS (tcp: 1433) Data Node Data Node Data Node Data Node Data Node Data Node Scalability and Availability: Fabric, Failover, Replication and Load balancing 29

Performance Considerations The distance your application travels to perform data access will affect performance Redesign your application for fewer trips to the database Use the same Data Center for all components of your application 30

Application Topologies Windows Azure SQL Database access from within MS Datacenter Windows Azure SQL Database access from outside MS (Azure compute – ADO.NET) Datacenter (On-premises – ADO.NET) Application/ Browser App Code / Tools SOAP/REST ADO.NET Data Svcs/REST - EF HTTP/S HTTP/S App Code (ASP.NET) Windows Azure T-SQL (TDS) T-SQL (TDS) SQL Data Services MS SQL Data Services MS Datacenter Datacenter Code Near Code Far 31

Data Hub “An aggregation of Enterprise, Partner, Desktop, and Device data within Windows Azure SQL Database” Windows Azure Enterprise Azure App On-premises App Windows Azure SQL Database Enterprise User Management Device Management Desktop Device Client App Client App Business Logic / Sync Gateway Rules Sync Client Sync Client 32

Service Provisioning Model Each account has zero or more servers Account Azure wide, provisioned via a common portal Establishes a billing instrument The servers are logical, not tied to physical machines Each server has one or more databases Server Logical concept equal to a master DB Contains metadata about database & usage Unit of authentication, geo-location, billing, reporting Generated DNS-based name Database Each database has standard SQL objects Users, Tables, Views, Indices, etc Unit of consistency 33

Database Replicas and Failover !Replica 1 Replica 2 DB Replica 3 Replica 4 34

Windows Azure SQL Database Deployment DB Script SQL Azure TDS Gateway 35

Windows Azure SQL Database Accessing databases Change Connection String Windows Azure SQL Your App Database TDS Gateway 36

Connecting to Windows Azure SQL Database Connect via Entity Framework, ADO.NET, ODBC, etc. OLE DB provider is NOT supported May need to include <login>@<server> Attaching a database is NOT supported Use familiar tools (sqlcmd, osql, SSMS, etc) Use SQL Server Management Studio 2008 R2 / 2012 37

Windows Azure SQL Database Security Supports SQL Server Security On-premise SQL Server security concepts still apply Server-level: sds_dbcreator, sds_securityadmin roles Database-level: same as on-premise SQL Server Administrative user is equivalent to sa 38

T-SQL Support (full or partial) Constants Tables, joins, and table variables Constraints Transact-SQL language elements Cursors such as Index management and rebuilding Create/drop databases indexes Create/alter/drop tables Create/alter/drop users and logins Local temporary tables … Reserved keywords User-defined functions Stored procedures Views Statistics management Transactions Triggers 39

T-SQL Not Supported Common Language Runtime (CLR) SQL Server configuration options Database file placement SQL Server Service Broker Database mirroring System tables Distributed queries Trace Flags Distributed transactions Filegroup management Full Text Search Global temporary tables 40

Database Editions Two SQL Database SKUs: Web & Business 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 | 100GB | 150GB This is the size we will not let you grow beyond You will be charged for the actual (peak) size (in any one day) CREATE DATABASE foo1 (EDITION='business', MAXSIZE=50GB); CREATE DATABASE foo2 (EDITION='business', MAXSIZE=30GB); CREATE DATABASE bar1 (EDITION='web', MAXSIZE=5GB); CREATE DATABASE bar2 (EDITION='web'); -- Defaults to 1GB

Database Editions – Pricing as on June 7, 2012 Windows Azure SQL Database Database is billed based on a graduated rate based on the size of the database, available as Web and Business Editions. The Web Edition supports up to a 5 GB maximum T-SQL based relational database. The Business Edition supports up to a 150 GB maximum size T-SQL-based relational database. Database Size Price Per Database Per Month 0 to 100 MB Flat $4.995 Greater than 100 MB to 1 GB Flat $9.99 Greater than 1 GB to 10 GB $9.99 for first GB, $3.996 for each additional GB Greater than 10 GB to 50 GB $45.954 for first 10 GB, $1.998 for each additional GB Great than 50 GB to 150 GB $125.874 for first 50 GB, $0.999 for each additional GB Billing Details Calculated on a daily basis, charged at a monthly rate for each SQL Azure database you utilize For databases greater than 1 GB, you will be billed in the next whole gigabyte increment Example: If you utilized two Business Edition databases, one that was 4.4 GB and one that was 14.4 GB for 1 day during a billing month, you would be charged for a 5 GB and 15 GB database for that day, which would total $2.643. Below are the calculations: 5 GB: ($9.99 for the first GB + $3.996 per GB for the next 4 GB) / 31 days = $0.838 15 GB: ($45.954 for the first 10 GB + $1.998 per GB for the next 5 GB) / 31 days = $1.805

Migrating Databases “Just change the connection string” * once database is migrated SQL Server Management Studio 2012 has increased support for Windows Azure SQL Database Migrating Databases Hands On Lab in the Windows Azure Platform Training Kit 43

Moving data Scripted INSERT statements SQL Server Integration Services BCP (bulk copy) is supported SQL Data Sync 44

Special Considerations: Database Size Maximum single database size is currently 150GB Database size calculation Includes: primary replica data, objects and indexes Does NOT include: logs, master database, system tables, server catalogs or additional replicas Must handle partitioning logic within the application 45

Special Considerations: Throttling MSDN • Use traditional SQL Server best practices • Build in retry logic especially if you expect very high throughput demands • Consider scaling out for high throughput scenarios 46

Scaling Up v Scaling Out 1 x 10GB database 10 x 1GB databases 47

Why scale out? Scale out on low cost commodity hardware Increased throughput for massive load Increased relational database storage volume 48

Sharding basics Several databases are used to store a portion of the application’s data The same schema is used across all databases Data is horizontally partitioned among databases (shards) based on certain criteria (i.e.: geographical info, customer) 49

Analyzing the sharding model Primary Shard Global Shard Child Global Country, OrderDate ContactID could 50 be the partition field

Scale Out Considerations Database sharding causes complexity Can you separate your database per tenant? Partitioning strategy is highly dependant on scenario Is there natural partitioning that facilitates use cases? Uniform partitioning via round-robin (modulo) can be highest performance Apps may need to be changed/re-architected to take advantage SQL Federation provides support you can do scale-out manually Effective scale/load testing is important to understand how to federate 51

SQL Federation Provides Scale-Out Support in SQL Database Partition data and load across many servers Bring computational resources of many to bear Take advantage of elastic provisioning of databases Pay as you go benefits Zero physical administration Federation includes Robust Connection Management Online repartition operations Split & Merge Databases

SQL Federation: Concepts Federation Root Represents the data being partitioned Federation “CustData” Federation Key (Federation Key: CustID) The value that determines the routing of a piece of data Member: PK [min, 100) Atomic Unit AU AU AU PK=5 PK=25 PK=35 All rows with the same federation key value: always together! Federation Member (aka Shard) Member: PK [100, 488) A physical container for a range of atomic units AU AU AU PK=105 PK=235 PK=365 Federation Root The database that houses federation directory Member: PK [488, max) AU AU AU PK=555 PK=2545 PK=3565 53

Introducing SQL Reporting Extend Reporting to the Cloud

SQL Reporting Developer Agility & Choice Build reports using familiar design tools Publish reports to the cloud or embed directly within applications Use consistent API’s to view, execute and manage reports Extended Reach & Accessibility Secure and reliable access to reports Access reports within an application or via a web browser Render and export to the format desired Elastic Scale & Reliability Off-premises reporting infrastructure lowers TCO Highly available Windows Azure environment Scales to meet the demands of the business as needed 55

SQL Reporting Scenarios Operational reports over SQL Database data Customers can report over their SQL Database data, not necessarily with the intent to embed them into an application. Embedding reports into my Windows or Azure application Developers can use same patterns and tools they use today to embed reports into their applications in connected mode against SQL Reporting Service.

Learning Windows Azure SQL Database www.azure.com Hands on Labs in Windows Azure Platform Training Kit Follow the team bloggers 57

Want to Know More? Windows Azure http://windowsazure.com/ Windows Azure Training Kit http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8396 MSDN Development Center http://msdn.microsoft.com/en-us/library/windowsazure/gg619386.aspx 58

vikas@softedge-systems.com @sahnivi 59

Introduction to Windows Azure and Windows Azure SQL Database

More Related Content

What's hot

Similar to Introduction to Windows Azure and Windows Azure SQL Database

Recently uploaded

Introduction to Windows Azure and Windows Azure SQL Database

Editor's Notes