SQL & Windows Azure

1. SQL & Windows Azure Customer Evidences from MTC

2. Agenda • Microsoft Cloud • SQL Azure Overview • Customer Cases • SQL Azure Migration Considerations

3. 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

4. “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

5. 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

6. 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

7. 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)

8. 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)

9. 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

10. Usage of crystal reports, active reports • Usage of NT Services, Web services

11. Analysis • Moderate sized DB • Lot of documents to be stored as Blobs • Minor compatibility with SQL Azure • Large cache to be migrated

12. 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)

13. 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

14. Analysis • Database size and growth is ideal for migration • Email/Payment services • Frequent application Updates • SEO friendliness of the application • Frequent SKU updates

15. 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

16. 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)

17. 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.)

18. 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

19. 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

20. 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

21. 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

22. 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

23. 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

24. Migration tools • Migration check • SQL Migration Wizard (codeplex) • Schema • SSMS • Data • BCP, sqlcmd, Roll-your-own (SqlBulkCopy API) • SSIS • Sync Framework

25. 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

26. 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

27. Performance Issues WAN latency with “chatty” applications Commodity disk subsystem & throughput Can get better throughput if app partitions queries across multiple machines in parallel

28. 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

29. © 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.