sql windows azure l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL & Windows Azure PowerPoint Presentation
Download Presentation
SQL & Windows Azure

Loading in 2 Seconds...

play fullscreen
1 / 29

SQL & Windows Azure - PowerPoint PPT Presentation


  • 199 Views
  • Uploaded on

SQL & Windows Azure. Customer Evidences from MTC. Agenda. Microsoft Cloud SQL Azure Overview Customer Cases SQL Azure Migration Considerations. Power of Choice. Microsoft Cloud Services. Private. Public. SaaS Software as a Service . Web Applications. Web Applications. P aaS

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL & Windows Azure' - zaltana


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql windows azure

SQL & Windows Azure

Customer Evidences from MTC

agenda
Agenda
  • Microsoft Cloud
  • SQL Azure Overview
  • Customer Cases
  • SQL Azure Migration Considerations
microsoft cloud services

Power of Choice

Microsoft Cloud Services

Private

Public

SaaS

Software as a Service

Web Applications

Web Applications

PaaS

Platform as a Service

AppFabric

IaaS

Infra as a Service

Dynamic Data Center Toolkitfor Hosters

Dynamic Data Center Toolkit for Enterprises

typical azure application
“Typical” Azure Application

Windows Azure Platform

Your Application

Your Data

Web Site

(ASPX, ASMX, WCF)

Web Site

(ASPX, ASMX, WCF)

Web Role

(ASPX, WCF, PhP)

Queues

LB

Worker Service

Worker Role

Internet

Blobs

LB

Tables

Your Relational Data

Triggers

SPs

Views

Tables

case 1 large si back office app
Case 1: Large SI Back-office App
  • Offshore Employee Payment, Travel, Leave and Vacation management
  • All web-based applications
  • Integration Points:
    • Authentication (2 factor)
    • Reporting
    • ERP
    • And other few systems
slide6

Some on-premise integration have latency from few seconds to few hours.

  • Database size at 700GB.
  • Use NT services, SQL Jobs, Linked Databases, Messaging & Web services
analysis
Analysis
  • Do they really need the entire Database?
  • Lot of dependency on On-Premise systems with varying latencies
  • Reporting (CTP Now)
  • Will have to build a Federated system for 2-Factor Authentication (effort)
conclusion
Conclusion
  • Migrate-able DB size reduced to < 20 GB
    • No partitioning or sharding required
  • Only some critical portions of the system migrated
  • Heavy on-premise dependency apps were left on premise
  • Evaluated Service Bus but rejected it
    • Lack of state persistence (now fixed)
case 2 web based hr software
Case 2: Web-based HR Software
  • Multi-tenancy
  • Database Size 5GB, grows to 40 GB
  • Lot of Documents. Grow exponentially with DB
  • Heavy ongoing customization results in frequent Schema changes
  • Uses MSMQ
  • Integration with SAP
  • Other file and messages based integration
slide10

Usage of crystal reports, active reports

  • Usage of NT Services, Web services
analysis11
Analysis
  • Moderate sized DB
  • Lot of documents to be stored as Blobs
  • Minor compatibility with SQL Azure
  • Large cache to be migrated
conclusion12
Conclusion
  • NT Services migrated to worker roles
  • Email service migrated to worker roles
  • Multi-Tenancy
    • One DB per Customer
    • Shared application instance (Minimal customization)
  • Reports
    • Active reports installed as startup task
    • Crystal report – still being evaluated
  • Integration with SAP/ERP
    • Simple CSV extract(blob +worker role)
case 3 online shopping ecommerce
Case 3: Online Shopping (eCommerce)
  • Max DB size 10GB
  • Large asset(images/videos) store, up to 35GB
  • Dynamic Scaling
  • Custom handlers, rewrite rules
  • Database driven site, lot of agent jobs running
  • Integration with email services
analysis14
Analysis
  • Database size and growth is ideal for migration
  • Email/Payment services
  • Frequent application Updates
  • SEO friendliness of the application
  • Frequent SKU updates
conclusion15
Conclusion
  • Assets can be abstracted behind blobs
  • Database migration was smooth
  • Email/Payment services integration required worker role
  • Agent job migration to worker role
  • Usage of CDN for static content
evaluation criteria
Evaluation criteria
  • Pattern of on-demand scale
  • Database size/Growth plan
  • ALM
    • Code change frequency
    • DB Change frequence
      • Schema and Data
  • Added Latency
  • Data transfer considerations ($$$)
    • Query patterns
    • Blobs
  • Dynamic environment
  • Authentication (DB based, Enterprise Identity)
evaluation criteria17
Evaluation criteria
  • Integration requirements (SAP, CRM, Other apps)
  • Services (SMS, Email, Payment)
  • Queue migration (if any)
  • Dependency on MS Office
  • Dependency on COM components
  • Non MS components (Java etc.)
sql azure network topology
SQL Azure Network Topology

Applications use standard SQL client libraries: ODBC, OLEDB, ADO.Net, …

Application

Internet

Azure Cloud

TDS (tcp)

Load balancer forwards ‘sticky’ sessions to TDS protocol tier

LB

Security Boundary

TDS (tcp)

Gateway

Gateway

Gateway

Gateway

Gateway

Gateway

Gateway: Service protocol gateway, enforces AUTHN/AUTHZ policy; proxy to SQL nodes

TDS (tcp)

Scalability and Availability: Fabric. Replication->Load balancing->Failover

SQL

SQL

SQL

SQL

SQL

SQL

pragmatic migration steps
Pragmatic Migration Steps
  • Restore a database on SQL 2008 R2
  • Run SQL Azure Migration Wizard
  • Fix identified compatibility issues
  • Migrate the Schema (script)
  • Migrate the Data (bcp, SSIS, Scripts)
  • Change the connection string (for in premise app)
  • Test
  • Migrate the Application
  • Test
migrating sql db to sql azure
Migrating SQL DB to SQL Azure
  • Ensure every table has a Cluster Index
  • SQL Authentication only
  • Data transfer costs apply to DB migration ($$$)
  • Test your migration on premise to minimize migration attempts ($$$)
  • Throttling of SQL Azure *****
  • Usage of XML data
corporate firewalls proxies
Corporate Firewalls/Proxies
  • Verify Server Name resolves to IP via PING/TRACERT (don’t expect server response)
  • Must open port 1433 outbound
  • May require a corporate proxy server
  • CorpNet
    • Must have ISA Firewall Client installed (domain credentials)
    • Proxy drops idle connections at 60 seconds
feature limitations
User

Supportability

Feature Limitations

Profiler/trace

DMVs

Catalog views

DBCCs

Performance Counters

USE [database]

SELECT INTO

Backup/Restore

Linked servers

DTC

Fulltext

CLR

sp_OA COM objects

BULK INSERT

Service Broker

Encryption

Auditing

Error Log

Extended stored procedures

Resource Governor

throttling watchdogs
Throttling & Watchdogs
  • Communication link failure
    • Idle connections at the gateway
    • Watchdog action when there is no active request
  • Msg 40501: The service is currently busy. Retry the request after 10 seconds. Code: %d
    • CPU Usage > 90 %
    • IO Response Time
  • Msg 40545: The service is experiencing a problem that is currently under investigation. Code: %d
  • Msg 40549: Session is terminated. Reason: %ls.
    • Long Running Transaction
    • Long Running Query
    • Locks Used
    • Log Space Used
migration tools
Migration tools
  • Migration check
    • SQL Migration Wizard (codeplex)
  • Schema
    • SSMS
  • Data
    • BCP, sqlcmd, Roll-your-own (SqlBulkCopy API)
    • SSIS
    • Sync Framework
azure scaling
Azure Scaling
  • Monitor Azure assets(Paraleap or newrelic or custom )
    • Provision Resources (more instances etc.)
    • Deprovision
  • What is the granularity
    • Azure web/worker role (APIS available)
    • Storage – 100 TB across queue/table/blob(get multiple accounts – plan for it)
    • Database – Plan/design for scale
sql azure scaling
SQL Azure scaling
  • Sharding
    • Functionality based from app level
    • Horizontal ( names with A-D on one server…)
      • Joins
      • FK
  • Automatic scaling/Load Balancing
    • Plan for scale
  • Managing Throttling
performance issues
Performance Issues

WAN latency with “chatty” applications

Commodity disk subsystem & throughput

Can get better throughput if app partitions queries across multiple machines in parallel

learnings
Learnings
  • Azure provides availability SLA, performance SLA is user’s reponsibility
  • Usage of network is ALWAYS charged
  • Capacity planning (SAAS-ifying)
  • Do not touch application with lot of integration points
  • Look at legal requirements
slide29

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.