Cloud Computing: SQL Data Services

gayheadtibburInternet and Web Development

Feb 5, 2013 (4 years and 5 months ago)

446 views

Christoph

Pletz

Senior Consultant

Trivadis AG

Meinrad Weiss

Principal Consultant

Trivadis AG

Technol ogi ekompetenz: Mi crosoft, Oracl e,
I BM, Open Source

Business

Intelligence

Bestehende

IT Kunden

Infrastruktur

Currently 13 locations with over 500 employees

Baden, Basel, Bern, Brugg, Lausanne, Zurich

Düsseldorf, Frankfurt, Freiburg, Hamburg, Munich, Stuttgart

Vienna


Since January 2008, the Trivadis Group has a qualified majority of
shares in MIK AG, based in Brugg


Financially independent and sustainably
profitable


Key figures in 2007

Consolidated income

CHF 98 million / EUR 60 million

Services for more than 600 clients in over 1‘500 projects

Over 125 Service Level Agreements

More than 5'000 training participants per year

Research budget:

CHF 6.0 million / EUR 3.6 million

Azure Overview

SQL Data Services (SDS)

SQL Server ↔

SDS Compatibility

SDS Client Applications

Wrap Up








Designed to be: “File System in the cloud”



lowest cost efficient solution for simple


data storage and retrieval in the cloud



can store



Queues



Blobs



ISAM style, simple Tables


(non
-
relational)





Designed to be: “File System in the cloud”



lowest cost efficient solution for simple


data storage and retrieval in the cloud



can store



Queues



Blobs



ISAM style, simple Tables


(non
-
relational)



Database

Data Sync

Reference
Data

ETL

Data Mining

Reporting

Low
-
cost and zero friction addresses web/dept workloads

Target is 3 to 10 GB Databases

High compatibility with existing SQL Server offering


Resources

Dedicated

Shared

Low

High

“Friction”/Control

SDS (RDBMS)

Value Props:


Value Props:

Roll
-
your
-
own HA/DR/scale

Hosted

On
-
premise

Value Props:

SQL,
Tables

and
Rows

T/SQL via TDS

A
uthority
,

C
ontainer,


E
ntity

SOAP/REST

Simple
Tables
,
Blobs
, Queues

SOAP/REST

Massive

Architecural

Change

Key
-
Value

DB



Existing

KnowHow


and
Applications



New Web 2.0 and
Silverlight

Applications



Other

Vendors

like

Amazon

A
uthority,

C
ontainer,


E
ntity

SOAP/REST

SQL, Tables

and Rows

SQL, Tables

and Rows

Simple Tables,
Blobs,
Queues

SOAP/REST



Existing KnowHow


and Applications



New Web 2.0 and Silverlight Applications



Other Vendors like Amazon

SOAP/REST

Data Services

T/SQL via TDS

T/SQL via TDS

Each
account

has

a billing relationship with Microsoft

owns one or more
virtual servers

Each
server

has

one or more databases including
virtual
master

each database limited in size

one or more
logins

Each
database

has

one or more
SQL users

Server=
server1
.data.database.windows.net

Database=
testDB

Login=
HansM
[@server1]

(maps to
testuser
)



Master



AdventureWorks



Northwind



Pubs



Master




Pubs



Northwind




Adventureworks


SQL Server



Master



AdventureWorks



Northwind



Pubs

SQL Data

Services

One SQL Server instance per physical machine

Multiple logical
databases per instance

Randomly distributed by infrastructure

Ressource

sharing

Scalable HA technology

Automatic replication and failover

Provisioning, metering and billing infrastructure

Machine 5

SQL Instance

SQL DBs

DB5

DB1

DB3

DB2

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

Machine 6

SQL Instance

SQL DBs

DB1

DB6

DB2

DB3

Machine 4

SQL Instance

SQL DBs

DB1

DB2

DB3

DB4

Machine 7

SQL Instance

SQL DB

DB4

DB5

DB1

DB7

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

Select *

from Customer

Primary

New Primary

Secondary

Secondary

Update Customer

Set Priority = 1

Secondary

Secondary

Primary

TDS Gateway

Front
-
end Node

Protocol Parser

Gateway

Logic

TDS

Session 1

Front
-
end Node

Protocol Parser

Gateway Logic

TDS

Session 2

Master Cluster

Master Node

Data Node
Components

Partition Manager

Master Node

Data Node
Components

Partition Manager

1

2

4

5

6

7

8

Utility Layer

Account
Database

Billing/Usage
Database

Service

Platform

Monitoring

Load
Balancing

Node

Provisioning

Roles

3

Machine 5

SQL Instance

SQL DB

DB5

DB1

DB3

DB2

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

Machine 6

SQL Instance

SQL DB

DB1

DB6

DB2

DB3

Machine 4

SQL Instance

SQL DB

DB1

DB2

DB3

DB4

Machine 7

SQL Instance

SQL DB

DB4

DB5

DB1

DB7

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

Connect

DB2

A
uthority,

C
ontainer,


E
ntity

SOAP/REST

SQL, Tables

and Rows

SQL, Tables

and Rows

Simple Tables,
Blobs,
Queues

SOAP/REST



Existing KnowHow


and Applications



New Web 2.0 and Silverlight Applications



Other Vendors like Amazon

T/SQL via TDS

T/SQL via TDS

Ditto?


In Scope for v1 (likely)

Tables, indexes and
views

Stored Procedures

Triggers

Constraints

Table variables,

session temp tables (#t)




Out of Scope for v1 (likely)

Distributed
Transactions

Distributed Query

CLR

Service Broker

Spatial


Physical server or
catalog DDL and views

Traditional
“Backup/Restore”

##t ?

API for provisioning

Web

SQL Server Management Studio (SSMS)

Basically available, but will have obviously
some restrictions

e.g. no
sp_configure



Standard Query Tools

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 (Geneva)

Windows Live ID

Encryption

TDS supports SSL

No database encryption at all in V1

Transparent Database
Encrytion


Encryption Functions

Scalability and/or consistency?

ACID (
Atomicity, Consistency, Isolation, Durability)

Write
n

nodes on Commit

Read from the max
n

nodes

BASE

Basically available, soft state, eventually consistent

Write
n

nodes on Commit synchronize more nodes
later

Read on more than the
n

nodes

SDS = (a very) ACID model

Writes on 3 nodes on Commit

Reads form 1 node


Scale Performance

SDS fabric can switch the primary node for a
database to a less busy node

SDS may throttle your session if it takes too
much resources

BYOP (bring your own partitioning)

PYON (Pay for your own physical node(s))

Scale Size of Database

Current offering is targeted at departmental
databases

BYOP (bring your own partitioning)

Clients connect directly to a database

Cannot hop across DBs

Any database client library for SQL Server
can be also used for SDS

ODBC, OLE DB, ADO.NET Provider for SQL
Server

ADO.NET Entity Framework

ADO.NET Data Services (Astoria)

LINQ to SQL


PHP native SQL Client



Cloud

SDS Database

TDS + TSQL Model

Windows Azure Compute

REST (Astoria)

ADO.Net
* + EF

Browser

HTTP

Application

SQL Client
*

TDS

* Client access enabled using TDS for ODBC,


ADO.Net, OLEDB, PHP
-
SQL, Ruby, …

Data Center

Web
Srv

SQL Client
*

HTTP+REST

Application

REST Client
*

Web App

SQL Client
*

HTTP+SOAP

Application

SOAP Client
*

“Code far” scenario

TDS is not designed for high latency networks

Recommendation

Batch SQL

statements

Use Functions/

Stored

Procedures

Table value

in parameters

are very handy

Symmetric Programming Model

Data Hub Aggregation

On Premises
Assets and Data

Mobile Users


Scalability

Partitioned databases

Geo
-
location and geo
-
redundancy

Flexibility

Distributed query

Support for multiple levels of hardware and software
isolation

Security

Future AD Federation

WLID, etc as alternate authentication protocols

Encryption

Invitation CTP


On
-
board invitation
-
only customers + some current

Public CTP


On
-
board all early adopters


Commercial Availability with Windows Azure in 2009

MIX ‘09

TechEd

Invitation

CTP

Public

CTP

Commercial
Availability

Migration

Mar 09

May 09

Jul 09

Nov 09

Sep 09

Apr 09

Jun 09

Aug 09

Oct 09

PDC

Internal

Adoption

High Availability

Including Data Hub Scenario

Fast and easy Deployment

Ideally “Code near” scenario with Web apps

Well known programming model

Low learning curve (you can learn it today)

Reliability

Confidence that data will not be lost

Replication, back
-
up

Defined SLA, at which Cost (?)

Team Blog

http://blogs.msdn.com/ssds



MSDN Development Center

http://msdn.microsoft.com/en
-
us/sqlserver/dataservices


Register @ the Azure start portal

http://www.azure.com






Limited Offer Only

check out these websites, blogs & more!

Presentations

TechDays:
www.techdays.ch

MSDN Events:
http://www.microsoft.com/switzerland/msdn/de/presentationfinder.mspx

MSDN Webcasts:
http://www.microsoft.com/switzerland/msdn/de/finder/default.mspx



MSDN Events

MSDN Events:
http://www.microsoft.com/switzerland/msdn/de/events/default.mspx

Save the date:
Tech•Ed

2009 Europe, 9
-
13 November 2009, Berlin


MSDN Flash (our by weekly newsletter)

Subscribe:
http://www.microsoft.com/switzerland/msdn/de/flash.mspx


MSDN Team Blog

RSS:
http://blogs.msdn.com/swiss_dpe_team/Default.aspx


Developer User Groups & Communities

Mobile Devices:
http://www.pocketpc.ch/

Microsoft Solutions User Group Switzerland:
www.msugs.ch

.NET Managed User Group of Switzerland:
www.dotmugs.ch

FoxPro User Group Switzerland:
www.fugs.ch










check out these websites, blogs & more!

Presentations

TechDays:
www.techdays.ch


TechNet Events

TechNet Events:
http://technet.microsoft.com/de
-
ch/bb291010.aspx


Save the date:
Tech•Ed

2009 Europe, 9
-
13 November 2009, Berlin


TechNet Flash (our by weekly newsletter)

Subscribe:
http://technet.microsoft.com/de
-
ch/bb898852.aspx



Schweizer IT Professional und TechNet Blog

RSS:
http://blogs.technet.com/chitpro
-
de/



IT Professional User Groups & Communities

SwissITPro

User Group:
www.swissitpro.ch


NT Anwendergruppe Schweiz:
www.nt
-
ag.ch


PASS (Professional Association for SQL Server):
www.sqlpass.ch











7.


8. April 2010

Congress Center Basel


Classic
Sponsoring Partners

Media Partner

Premium
Sponsoring Partners