1 / 46

Migrating Applications to SQL Azure

SESSION CODE: COS311. Migrating Applications to SQL Azure. Cihan Biyikoglu Program Manager – SQL Azure Microsoft Corporation Blog : “Your Data In the Cloud” on MSDN. v 1.1. Agenda – Migrating Applications to SQL Azure. Overview SQL Azure App Migration

yael
Download Presentation

Migrating Applications to SQL Azure

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SESSION CODE: COS311 Migrating Applications to SQL Azure Cihan Biyikoglu Program Manager – SQL Azure Microsoft Corporation Blog: “Your Data In the Cloud” on MSDN v1.1

  2. Agenda – Migrating Applications to SQL Azure • Overview • SQL Azure • App Migration • Data Access Tier Considerations • Database Migration • Migrating to SQL Azure form MySql • Moving Schema and Data into SQL Azure • Transferring Large Data into SQL Azure • Future

  3. SQL Azure Overview

  4. The Windows Azure Platform • Developer Experience • Use existing skills and tools Relational data Management platform Compute Storage Management AppFabric Flexible APIs Connectivity Access control Information Marketplace Reporting & BI Billing & Payments

  5. SQL Azure DatabaseThe first and only true relational database as a service Subtitle color Self-managed Elastic Scale Developer Agility • Build cloud-based database solutions on consistent relational model • Leverage existing skills through existing ecosystem of developer and management tools • Explore new data application patterns • Database utility; pay as you grow • Flexible load balancing • Business-ready SLAs • Enable multi-tenant solutions • World-wide presence • Easy provisioning and deployment • Auto high-availability and fault tolerance • Self-maintaining infrastructure; self-healing • No need for server or VM administration

  6. Application Topologies SQL Azure access from within MS Datacenter (Azure compute) SQL Azure Access from outside MS Datacenter (On-premises) SQL Azure Access from within and outside MS Datacenter (On-premises & Azure Compute) SQL Server App code/ Tools App code/ Tools Application/ Browser SOAP/REST HTTP/S Astoria/REST - EDM HTTP/S Windows Azure SQL Azure Data Sync Windows Azure App Code (ASP.NET) App Code (ASP.NET) T-SQL (TDS) App Code (ASP.NET) App Code (ASP.NET) T-SQL (TDS) T-SQL (TDS) Windows Azure SQL Azure SQL Azure SQL Azure Code Near Code Far Hybrid

  7. Data Access Tier Application Migration

  8. Migrating Applications

  9. Data Access APIs • Supported APIs • Connection String • ADO.Net • Encrypt=True and add @servername to User ID • ODBC • Encrypt=yes and add @servername to Uid

  10. Data Access APIs cont. • Protocols • Authentication Mode

  11. Connectivity • Robust Connection Management through Retry Logic • More On this Topic • COS305: Microsoft SQL Azure Development Best Practices • Flaky network connectivity • Code-Far topology • Failover • HW Failures or Upgrades • Resource Management Operations • Throttling and Load Balancing • ALTER DATABASE (T-SQL) • Changes to Database Name or MAXSIZE/EDITION ALTER DATABASE [TestDB] MODIFY (MAXSIZE=50GB) GO

  12. Collations • Default Collation • Set to SQL_LATIN1_GENERAL_CP1_CI_AS • Applies to… • Metadata, TempDb and Default for User Data • Object Names (tables, sprocsetc), String variables and Columns when no collation is specified. • Use column and/or expression level collation when needed CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] [int] IDENTITY(1,1) NOT NULL, [ErrorTime] [datetime] NOT NULL, [UserName] [nvarchar(4000)] COLLATE Turkish_100_CI_AI NOT NULL, [ErrorNumber] [int] NOT NULL, [ErrorMessage] [nvarchar](4000) COLLATE Japanese_CI_AS NOT NULL, CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ([ErrorLogID] ASC) ) declare @strnvarchar(128) COLLATE Greek_CS_AI

  13. Feature Parity • Administration Surface • Physical Server Properties does not apply in SQL Azure • You have a master database but no access to server level constructs such as sp_configure, endpoints, DBCC commands, server level DMVs and System Views • Programmability Surface • Certain Features are partially available today • USE, XML processing, deprecated T-SQL etc. • List is available here; http://msdn.microsoft.com/en-us/library/ee336267.aspx • Some features are not available today • Full-text Search, Remote Data Access and Linked Servers, Distributed Transactions, Change Tracking, Service Broker etc. • List is available here; http://msdn.microsoft.com/en-us/library/ee336253.asp

  14. Summary – Migrating Applications

  15. Moving Schema and Data Database Migration

  16. Database Migration MySql and Access to SQL Azure

  17. Migration Assistant for MySqland Access (CTP) • Scenario • Auto porting of schema, database code and data from MySql and Access to SQL Azure • SQL Server Migration Assistant for MySql and Access • Supports MySQL 4.1 and up • Support Access v 97 and up • SQL Server versions supported (all editions) • SQL Azure, SQL Server 2005, SQL Server 2008 and 2008 R2

  18. Amazon Relational Database Service w/ MySqlto SQL Azure SQL Server Migration Assistant DEMO

  19. Database Migration Transferring Schema and Data From SQL Server to SQL Azure

  20. Management Studio – Generate Script Wizard • Scenario • Migration of schema and/or data with fine grain control. • Pros • Native support for SQL Azure Schema options: “Engine Type = SQL Azure” • Ensures correct options and settings are applied for the TSQL script generation. • Requires explicit action on unsupported objects. • Cons • Verbose – INSERT Statements instead of raw data • Data is scripted with a fixed 100 row batch size. • Edit “GO” statements between small batches • Round-trip Efficiency • Use “SET NOCOUNT ON”

  21. Generate Script Wizard Migrating Databases to SQL Azure DEMO

  22. DAC (Data-tier Application) Packages • Scenarios • Self contained package for moving schema easily through the development lifecycle • What is a DAC Pack? • Single unit for authoring, deploying, and managing the data-tier objects • Development Lifecycle (VS 2010) • Editing DACs • Schema and DB Code Development, • Code Analyses, Deployment Policy Settings, • Schema Comparison and more… • Building DACs – the self contained database package • Management Lifecycle (SSMS 2008 R2) • Managing DACs • Registering existing database as DACs • Deploying and Upgrading databases using DACs,

  23. DACs Migrating Databases to SQL Azure DEMO

  24. Database Migration Transferring Large Data TO SQL Azure

  25. Transferring Large Data • Best Practices for efficient Data movement to SQL Azure • Use the right tools is key • BCP • SSIS • Network performance considerations • Latency vs Bandwidth

  26. Use the right toolsBCP & Bulk Copy APIs • Scenario • High speed programmable data import and export • Best Practices • Optimize Databases for Data Import • Disable/Delay creation of none Clustered Indexes • Order Data on the Clustered Index • Disable Triggers and Constraint checking • -N Native mode so no data type conversion needed. • -c Character mode for interoperability • -b batchsize will let you control transaction size • -h”TABLOCK, ORDER(…)” optimize writes

  27. Use the right toolsSSIS – Best of Breed Data Transformation Utility • SSIS Design Surface - Data Flow Task • Diverse Source and destinations • To/From Flat Files, ADO.Net, OleDB • Fully programmable flow • Loops, Sorts, Conditional operators, XML/WebServices Processing etc • VS Debugging support with data viewers, watches and conditional breakpoints • Best Practices – Data Flow Task • Remember; Optimize Databases for Data Import • Disable/Delay creation of none Clustered Indexes • Order Data on the Clustered Index • Disable Triggers and Constraint checking • Batch Size: Adjust the transaction size • Buffer and Blob temp storage area; spread over to multiple drives for large data transfers • Parallelization – based on execution trees, task will auto parallelize

  28. Use the right toolsImport and Export Wizard • Scenario • Simplified wizard for migrating schema and data through SSIS • Pros • Great performance out of the box • Allows ‘save as package’ for full control

  29. Parallelization and Network Performance • Extended Discussion on Network Performance in • COS13-INT: Database Performance in a Multi-tenant Environment • Both Latency and Bandwidth are key for high performance import&export • Latency is about laws of physics • Long distances means high latency • Parallelization can help mitigate some latency • Bandwidth is key! • Once you reach the bandwidth limit parallelization won’t help

  30. BCP PerformanceTest Setup • Schema CREATE TABLE [dbo].[table1]( [c01] [int] NOT NULL, [c02] [int] NOT NULL, [c03] [smallint] NOT NULL, [c04] [int] NOT NULL, [c05] [smallint] NOT NULL, [c06] [smallint] NOT NULL, [c07] [char](50) NOT NULL, [c08] [char](24) NOT NULL, … [c17] [char](24) NOT NULL, PRIMARY KEY CLUSTERED ([c01] ASC,[c02] ASC)) CREATE NONCLUSTERED INDEX [index1] ON [dbo].[table1] ([c07] ASC) • Networks • Commodity vs Commercial Network • Commodity Bandwidth - Low up and Medium down • Commercial Bandwidth – High Bandwidth up and down • Data Distribution: • Row Size: 600Bytes • Row Count: 100K • Pages: ~7700 • Total Size: ~60MB • Even Distribution on Pkey • Constraints and Indexes • Compound Pkey (int+int) • None-clustered key on char(50) • No triggers or relationships

  31. BCP Upload Performance - Rows/Sec

  32. BCP Download Performance – Rows/sec

  33. Summary– Database Migration

  34. Future

  35. What is coming? • More information on all upcoming features at • DAT209: What’s New in Microsoft SQL Azure • Migrating Application • Connectivity • OleDb Support • SQL Server Parity • DB Collation Support • OData Support – SQL Azure Labs • Further reach for SQL Azure data

  36. What is coming? • Database Migration • SQL Server Migration Assistant • MySqland Access to SQL Azure • DAC Packages • Ability to upgrade using DACs • SQL Web Management and Administration (SWA) • Web based database management tool

  37. Q&A Thank You!

  38. Required Slide Speakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session. Related Content • Breakout Sessions • DAT209 – What’s New in Microsoft SQL Azure • COS305 – Microsoft SQL Azure Development Best Practices • COS311 – Migrating Applications to Microsoft SQL Azure • Interactive Sessions • COS07-INT – Using SQL Azure as a Datahub to connect SQL Server and Silverlight clients • COS13-INT – Database Performance in a Multi-tenant Environment • Hands-on Labs • COS04–HOL – Introduction to Microsoft SQL Azure

  39. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • Team Blog – http://blogs.msdn.com/sqlazure • MSDN Dev Center – http://msdn.microsoft.com/azure/sqlazure • SQL Azure Labs – http://www.sqlazurelabs.com • Follow us on Twitter - @sqlazure Windows Azure Platform Training Kit - http://bit.ly/dgvMv0

  40. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Track Resources • Join a user group and network with peers to Connect, Share and Learn • Become a PASS Member (Professional Association for SQL Server) - www.sqlpass.org • Attend the PASS Summit 2010 – largest SQL Server conference http://www.sqlpass.org/summit/na2010 • Join the SQL Server conversation on Facebook, Twitter and YouTube • Find out what top SQL Server bloggers are saying on the Community Tab • Learn about applications running on SS08 R2 on the Application Showcase Tab • Ask questions and get answers, straight from SQL Server experts • Visit the Microsoft SQL Server Forums - http://social.technet.microsoft.com/forums/en-us/category/sqlserver

  41. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

  42. Required Slide Complete an evaluation on CommNet and enter to win!

  43. Track Resources • Windows Azure, Microsoft SQL Azure, Windows Azure platform AppFabric: www.windowsazure.com/techedus • Learn More: Visit the Windows Azure Boot Camp, Room 396

  44. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

  45. © 2010 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.

  46. Required Slide

More Related