SQL Azure SSWUG
Getting Started with SQL Azure
Glenn Berry, MVP Database Architect NewsGator Technologies, Inc.
Agenda • • • •
Introduction to SQL Azure Discuss SQL Azure Provisioning/Pricing Cover Architecture and Client Tools Demonstrate SQL Azure
2
What Is SQL Azure? • Limited version of SQL Server “in the cloud” • Based on SQL Server 2008
• Hosted in one of four Microsoft data centers • Relational engine only in v.1 • 1GB or 10GB size limit
3
SQL Azure Benefits • • • • •
Familiar SQL Server relational model Supports existing APIs & tools Zero hardware management overhead No SQL Server license expense Built-in high availability
Current SQL Azure Pricing • Web Edition: 1GB, $9.99 / month
• Business Edition: 10 GB, $99.99 / month • Data transfers = $0.10 in / $0.15 out / GB - ($0.30 in / $0.45 out / GB in Asia)* • Data centers operating in U.S, EU and Asia
Current Azure Data Center Locations • • • •
South Central USA North Central USA Southeast Asia North Europe
Windows and SQL Azure Container
Windows and SQL Azure Container
Application Suitability • Suitable for SaaS ISV/Departmental Apps • Multi-tenant hosting: www.SiteMasher.com • Elastic provisioning: www.ticketdirect.com • Cost saving, out of the box HA: www.kbb.com
• Not yet ready for • Large OLTP/DSS systems • Tier 1, mission critical apps • Compliance/Specific Industry Regulations •
PCI/DSS, HIPAA, Financial transaction
Account Setup • Must have Windows Live ID • Windows Azure Portal • https://windows.azure.com
• SQL Azure Portal • https://sql.azure.com
• MSDN Premium Subscribers Deal • Eight months of free limited service • Three Web Edition databases
10
Service Provisioning Model • Each account has zero or more servers
Account
• Azure wide. Specify geo location for server • Billing instrument
• Each server has one or more databases • Contains metadata about the databases
Server
• Unit of authentication • DBs in a server reside in same geo-location
• Each database has standard SQL objects
Database
• Unit of consistency • Contains users, tables, views, indices, etc…
11
SQL Azure Supported Client Tools • SQL Server 2008 R2 SSMS • Generate Script Wizard
• Excel 2010 PowerPivot • SQL Azure Migration Wizard • Codeplex
• SQL Azure Data Sync
SQL Compatibility Supported • Tables, indexes and views • Stored Procedures • Triggers • Constraints • Table variables • Session temp tables (#t) • Some DMVs •
…
Not Supported Currently • Physical server or catalog DDL and views • CLR • Service Broker • Spatial • Distributed Transactions/Query • Full Text Search
SQL Azure Architecture • Shared infrastructure at SQL database and below • Massively distributed cluster w/ commodity hardware
• Scalable HA technology provides the glue • Each SQL Azure DB has 3 replicas Logical Server
• Automatic replication and failover
• Gateway Service forwards TDS requests SQL Azure Gateway Service Machine 4
Machine 5
Machine 6
SQL Instance
SQL Instance
SQL Instance
SQL DB User DB1
User DB2
SQL DB User DB3
User DB4
User DB1
User DB2
SQL DB User DB3
User DB4
User DB1
Scalability and Availability: Fabric, Failover, Replication, and Load balancing
User DB2
User DB3
User DB4
Programming Model - App Topology SQL Azure access from within MS Datacenter
SQL Azure access from outside MS Datacenter
(Azure compute – ADO.NET)
(On-premises – ADO.NET)
Application/ Browser
SOAP/REST HTTP/S
App Code / Tools
ADO.NET
App Code (ASP.NET)
Windows Azure
T-SQL (TDS)
SQL Azure
MS Datacenter
Code Near
T-SQL (TDS)
SQL Azure
MS Datacenter
Code Far
Demonstrations • • • •
SQL Azure Portal Connect to SQL Azure with SSMS Explore how to work with SQL Azure Show new features added in Service Update 1
16
Thank You! • Resources • SQL Azure Team Blog • http://blogs.msdn.com/ssds/
• Blog • http://glennberrysqlperformance.spaces.live.com
• Twitter: @GlennAlanBerry
17
Glenn Berry, MVP Database Architect NewsGator Technologies, Inc.
Agenda • • • •
Introduction to SQL Azure Discuss SQL Azure Provisioning/Pricing Cover Architecture and Client Tools Demonstrate SQL Azure
2
What Is SQL Azure? • Limited version of SQL Server “in the cloud” • Based on SQL Server 2008
• Hosted in one of four Microsoft data centers • Relational engine only in v.1 • 1GB or 10GB size limit
3
SQL Azure Benefits • • • • •
Familiar SQL Server relational model Supports existing APIs & tools Zero hardware management overhead No SQL Server license expense Built-in high availability
Current SQL Azure Pricing • Web Edition: 1GB, $9.99 / month
• Business Edition: 10 GB, $99.99 / month • Data transfers = $0.10 in / $0.15 out / GB - ($0.30 in / $0.45 out / GB in Asia)* • Data centers operating in U.S, EU and Asia
Current Azure Data Center Locations • • • •
South Central USA North Central USA Southeast Asia North Europe
Windows and SQL Azure Container
Windows and SQL Azure Container
Application Suitability • Suitable for SaaS ISV/Departmental Apps • Multi-tenant hosting: www.SiteMasher.com • Elastic provisioning: www.ticketdirect.com • Cost saving, out of the box HA: www.kbb.com
• Not yet ready for • Large OLTP/DSS systems • Tier 1, mission critical apps • Compliance/Specific Industry Regulations •
PCI/DSS, HIPAA, Financial transaction
Account Setup • Must have Windows Live ID • Windows Azure Portal • https://windows.azure.com
• SQL Azure Portal • https://sql.azure.com
• MSDN Premium Subscribers Deal • Eight months of free limited service • Three Web Edition databases
10
Service Provisioning Model • Each account has zero or more servers
Account
• Azure wide. Specify geo location for server • Billing instrument
• Each server has one or more databases • Contains metadata about the databases
Server
• Unit of authentication • DBs in a server reside in same geo-location
• Each database has standard SQL objects
Database
• Unit of consistency • Contains users, tables, views, indices, etc…
11
SQL Azure Supported Client Tools • SQL Server 2008 R2 SSMS • Generate Script Wizard
• Excel 2010 PowerPivot • SQL Azure Migration Wizard • Codeplex
• SQL Azure Data Sync
SQL Compatibility Supported • Tables, indexes and views • Stored Procedures • Triggers • Constraints • Table variables • Session temp tables (#t) • Some DMVs •
…
Not Supported Currently • Physical server or catalog DDL and views • CLR • Service Broker • Spatial • Distributed Transactions/Query • Full Text Search
SQL Azure Architecture • Shared infrastructure at SQL database and below • Massively distributed cluster w/ commodity hardware
• Scalable HA technology provides the glue • Each SQL Azure DB has 3 replicas Logical Server
• Automatic replication and failover
• Gateway Service forwards TDS requests SQL Azure Gateway Service Machine 4
Machine 5
Machine 6
SQL Instance
SQL Instance
SQL Instance
SQL DB User DB1
User DB2
SQL DB User DB3
User DB4
User DB1
User DB2
SQL DB User DB3
User DB4
User DB1
Scalability and Availability: Fabric, Failover, Replication, and Load balancing
User DB2
User DB3
User DB4
Programming Model - App Topology SQL Azure access from within MS Datacenter
SQL Azure access from outside MS Datacenter
(Azure compute – ADO.NET)
(On-premises – ADO.NET)
Application/ Browser
SOAP/REST HTTP/S
App Code / Tools
ADO.NET
App Code (ASP.NET)
Windows Azure
T-SQL (TDS)
SQL Azure
MS Datacenter
Code Near
T-SQL (TDS)
SQL Azure
MS Datacenter
Code Far
Demonstrations • • • •
SQL Azure Portal Connect to SQL Azure with SSMS Explore how to work with SQL Azure Show new features added in Service Update 1
16
Thank You! • Resources • SQL Azure Team Blog • http://blogs.msdn.com/ssds/
• Blog • http://glennberrysqlperformance.spaces.live.com
• Twitter: @GlennAlanBerry
17
