why you should(n't) run your databases in the cloud

WINDOWS AZURE PLATFORM

Compute: Virtualized compute environment based on Windows Server

Storage: Durable, scalable, & available storage

Management: Automated, model-driven management of the service

Database: Relational processing for structured/unstructured data

Service Bus: General purpose application bus

Access Control: Rules-driven, claims-based access control

THE SQL PLATFORM TO CLOUD

Initial Services Database – Core SQL Server database capabilities

Future Services Data Sync – Enables the sync framework (soon after PDC)

Additional SQL Server capabilities available as a service: Business Intelligence and Reporting

New services: Reference Data and Secure Data Hub

Symmetric Programming Model

Data Hub Aggregation

MICROSOFT SQL AZURE

Familiar SQL Server relational model

Uses existing APIs & tools

Friction free provisioning and reduced management

Built for the Cloud with availability and scale

Accessible to all from PHP, Ruby, and Java

Clear Feedback: “I want a database in the Cloud”

Focus on combining the best features of SQL Server

running at scale with low friction

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

Machine 5SQL Instance

SQL DBUserDB1

UserDB2

UserDB3

UserDB4

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

SDS Provisioning (databases, accounts, roles, …, Metering, and Billing


SQL DBUserDB1

UserDB2

UserDB3

UserDB4


SQL DBUserDB1

UserDB2

UserDB3

UserDB4

Scalability and Availability: Fabric, Failover, Replication, and Load balancing

SQL AZURE

DEPLOYMENT

Web Portal(API)

SQL Azure

TDS

DB

Script

SQL AZURE

ACCESSING DATABASES

Web Portal(API)

SQL Azure

TDS

Your

App

Change Connection String

DATABASE REPLICAS

Replica

1

Replica

2

Replica

3

DB

SQL AZURE DATABASE FEDERATIONS

Federations are objects that allow scaling-out of data for building data

tier applications with unlimited scalability and best price-performance

through amplifying backend elasticity and simplified multi-tenancy at

the database tier.

- Unlimited Scalability

- Dynamic and Online Elasticity

- Simplified Multi-Tenancy

EFFICIENT TENANCY MODELS

Classic Tenancy Model On Premise Single-Tenant-Per-Database

Cloud Tenancy Models Single-database-per-tenant does not work for long tail and large tenants

Utilize multiple-tenants-per-database and multiple-databases-per-tenant as well for full flexibility

Tenancy Models:

Single tenant per database

Multiple-tenants per database Multiple

databases per tenant

PROGRAMMING MODEL

Small Data Sets Use a single database

Same model as on premise SQL Server

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 in v1 For v1 will publish best practices for scale out

Post-v1 we are looking at building an abstraction to hide some of the complexities of partitioning

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 replicated with 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

DEPLOYMENT

Support for basic deployment options

SQL scripts work (but not attach database)

Geo-location of Windows Azure compute and SQL Azure Databases

Support for Application and multi-server management model

Support for application packages

Cloud or on-premise is a deployment time choice

Visibility of data across on-premise and the cloud

Support existing and new forms of deployment

SECURITY MODEL

Uses regular SQL security model

Authenticate logins, map to users and roles

Authorize users and roles to SQL objects

Limited to standard SQL Auth logins

Username + password

Future AD Federation, WLID, etc as alternate

authentication protocols

Security model is 100% compatible with on-premise SQL

PRICING

Standard pay-as-you-go (Web edition)

pricing

$9.99 per database up to 1GB per month


Standard pay-as-you-go (Business Edition) pricing






Databases can be either Web or Business Edition databases.

Web Edition databases supports up to 5 GB of data, and uses billing increments of 1GB and 5GB.

Business Edition database will support up to 50 GB, and uses 10 GB billing increments.

Billed based on the peak database size in a day, rolled up to the next billing increment.

AZURE GAP’S

Does not support user-defined common language

runtime (CLR) data types.

Some Transact-SQL statements do not support some of

the arguments and options that exist in their

corresponding Transact-SQL statements in SQL Server

2008 http://msdn.microsoft.com/en-us/library/ee336267.aspx

Soes not support all of the Transact-SQL statements

that are delivered in SQL Server 2008. http://msdn.microsoft.com/en-us/library/ee336253.aspx

http://msdn.microsoft.com/en-us/library/ee336267.aspx







IMPORTANT BIG GAPS

The USE statement does not switch between databases. To change databases, you must directly connect to the database.

SQL Azure Database does not support heap tables. A clustered index must be created before an insert operation is allowed on the table.

SQL Azure Indexes do not support : partition_schema_name,filegroups, FILESTREAM, TEXTIMAGE, PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, MAXDOP,DATA_COMRESSION

GUIDELINES & LIMITATIONS I

Starting with Visual Studio 2010, you can use the Server Explorer to connect to and to explore your databases in SQL Azure. Previous versions of Server Explorer are not supported. Visual Studio 2010 is fully supported.

SQL Azure Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premise SQL Server and connect to SQL Azure Database.

Both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options are set to ON. Because SET <snapshot_option> in the ALTER DATABASE Transact-SQL statement is not supported, these database options cannot be changed.

GUIDELINES & LIMITATIONS II

Default database collation is SQL_LATIN1_GENERAL_CP1_CI_AS, where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AS is accent-sensitive. SQL Azure Database does not allow setting the collation at the server or database level.

SQL Azure Database supports up to 150 databases in each SQL Azure server, including the master database.

MAXSIZE is specified when the database is first created and can later be changed using ALTER DATABASE. MAXSIZE

If you remove some data then there can be as much as a fifteen-minute delay before you can insert new data.

SQL AZURE DATA SYNC (CTP)

Data management capabilities allowing data sharingbetween SQL Azure and on-premises databases.

Extend enterprise data to the cloud, rather thanreplacing it, by synchronizing on-premises SQL Server with SQL Azure

Synchronize data between SQL Azure databases withina data center, to help scale-out data access acrossmultiple databases for elastic demand and usage spikes

Synchronize data between SQL Azure databases in different data centers, to extend data and provide geo-available data access

TOP FEATURES SQL AZURE DATA SYNC

Elastic Scale: Service scales as resources requirements grow

No-Code Sync Configuration: Easily define data to besynchronized with easy to use tools

Schedule Sync: Choose how often data is synchronized

Conflict Handling: Handle issues where same data is changedin multiple locations

Logging and Monitoring: Administration capabilities fortracking data and monitoring potential issues

Data sub-setting: Control of tables to be synchronizedbetween SQL Azure database

DATA SYNC – TABLE SELECTION

DATA SYNC - TABLE ORDER + AGENT

THE FACTS

Industry evidence suggests that shifting to the cloud saves 20%-50% off current IT deployments, and, according to its advocates, it can be many times more than that.

Switching costs to cloud computing are relatively low. It is even possible to run existing systems parallel to cloud computing, which makes migration easier.

Too few practical examples. Suppliers ahead of the game are not releasing examples of what works and what doesn't

Higher savings are available from newer cloud solutions, but they have not been considered as departments look to shave costs on old technology and existing contracts.

PERSONAL OPINION – CONSOLIDATE

Private vs. Public Cloud

Private cloud != (virtualized) local environments in your data center

Private cloud is taking the concepts of Public Cloud and making those concepts viable in your own data centers.

elastic scale

metered billing

self-provisioning

consolidated servers

maximum utilization of infrastructure

etc.

VIRTUALIZE =! PRIVATE CLOUD

BUT SHOULD BE SEEN AS A PART OF IT

Customers should virtualize SQL Server Start from smallest workload

Continue to larger workload over time with experience

Microsoft support SQL Server virtualization http://support.microsoft.com/?id=956893

Customers should not virtualize SQL Server CPU: Need more than 4 logical processors

Memory: Need more than 64 GB per virtual machine

Check Troughput

http://support.microsoft.com/?id=956893

why you should(n't) run your databases in the cloud

Technology