1 / 33

Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices

COS308. Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices . Mark Scurrell Lead Program Manager Microsoft. Agenda. Utilizing SQL Azure What’s the same, what’s different, what’s unique? One-time migration to SQL Azure From SQL Server

arwen
Download Presentation

Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices

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. COS308 Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices Mark Scurrell Lead Program Manager Microsoft

  2. Agenda • Utilizing SQL Azure • What’s the same, what’s different, what’s unique? • One-time migration to SQL Azure • From SQL Server • From Access and other data sources • On-going data sharing • Between on-premises and cloud • Between cloud databases • Roadmap

  3. Comparing SQL Server and SQL Azure • Goal is symmetry: • Database - T-SQL, features • Tools • Connectivity • Frameworks • Some variations exist: • Table design • Some features • Scale strategy • Overview: http://msdn.microsoft.com/en-us/library/ff394102.aspx • Differences are being reduced: • Roadmap at end

  4. Variations from SQL Server • Source database version: • SQL Azure based on SQL Server 2008 • Moving to Denali engine • Table design: • Clustered index required • No physical/server configuration • Features: • Some features not yet available • E.g. Agent, Full-text, encryption, service broker, SQL CLR • Connection Handling • Scaling • Further info: • Porting TFS: http://blogs.msdn.com/b/bharry/archive/2010/10/28/tfs-on-windows-azure-at-the-pdc.aspx

  5. Connection Handling • Latency • Connections can be dropped • Load-balancing • Throttling • Check & retry • Retry logic info: • http://blogs.msdn.com/b/appfabriccat/archive/2010/10/28/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications.aspx

  6. Throttling • Ensure a good experience for each tenant • Prevents excessive machine load • Causes: • Lock consumption, log usage, huge transactions, tempdb usage, memory usage, idle connections • Impact and handling: • Not the same as on-premises SQL Server • Connections become invalid • Transactions can fail

  7. Scaling SQL Azure Database SQL Azure Database • Scale-up: • Currently by DB size, to 50GB • Will always be limits • Behavior different at limits • Scale-out: • Huge scale possible • Need to partition data • Elasticity: • Growth • Predictable peaks – grow and shrink CPU/IO CPU/IO DB Size DB Size SQL Azure Database CPU/IO DB Size # DB’s Time

  8. One-Time Data Migration to SQL Azure

  9. Data Transfer Tools

  10. DAC and DAC Import/Export • DAC: • Defines schema and objects • Author in VS • Register, extract, deploy in SSMS • Full compatibility with SQL Azure • DAC Database Import/Export: • DAC Framework V2 CTP; available on SQL Azure Labs • Import and export of data • Compressed using ZIP • DAC Import/Export Apps: • Sample client EXE with V2 CTP • Cloud support coming soon – import/export of Windows Azure blobs • More Info: • DBI306: Using Contained Databases and DACs to Build Applications in Microsoft SQL Server Code-Named "Denali" and SQL Azure

  11. Database Migration withDAC Framework Import/Export DEMO

  12. SQL Server Migration Assistant • Automate and simplify database migration • Supports migration to: • SQL Server and SQL Azure • Supports migration from: • Oracle, Sybase, MySQL, Access • Migrates: • Schema • Data • SSMA 5.0 now available • Free tool and free support • More info: • DBI307: Automating Database Migration to Microsoft SQL Server

  13. Migration from MS Access • Why migrate? • High availability of SQL Azure • Management of multiple databases • Multi-user access • Accessibility • Steps: • Migrate database to SQL Azure • Can use SQL Azure schema to cater for multiple Access DB’s • Redirect UI to database

  14. SQL Server Migration Assistant DEMO

  15. On-Going Data Sharing

  16. Scenarios Cloud On-Premises Application Application SQL Azure SQL Server Application Application • E.g. Geo-located web applications • E.g. Traffic manager E.g. Single location, branch office, retail E.g. One-way publish, two-way sharing, aggregation SQL Azure SQL Azure Application Application E.g. Reporting E.g. Web site reference data SQL Server SQL Azure

  17. SQL Azure Data Sync • No-Code Sync Configuration • Easily define data to be synchronized and locations • Choose how often data is synchronized • Full Data Synchronization Capabilities • Two-way sync of same data, as well as one-way sync • Conflict Handling • Detect and resolve conflicts caused by the same data being changed in multiple locations • Logging and Monitoring • Administration capabilities for tracking usage • Scale • Service scales as resources requirements grow

  18. SQL Azure Data Sync CTP2 demo

  19. SQL Azure Data Sync Components Service Data E.g. Configuration, Logs & Diagnostics Win Azure Tables, Blobs, Queues Admin UI Windows Azure SQL Azure Data Sync SQL Azure Sync Agent HTTPS Sync Framework User Databases + Change Tracking + Sync meta-data User Databases + Change Tracking + Sync meta-data

  20. SQL Azure Data Sync • Sync Provisioning: • Change tracking via triggers and side-tables • Minimal DML overhead • No change to user tables • Stored procs and meta-data tables • Snapshot isolation enabled for SQL Server database • Batching: • Data change split into batches for transfer • Transactional Consistency: • Batches split into transactions to apply • PK/FK ordering preserved • Transaction boundaries at source not preserved • E.g. Could get new Order applied in separate transaction to associated new OrderDetails

  21. Planned V1 Features not in CTP2 • Improved UI and Windows Azure portal integration • Filtering: • Allow selection of columns • Subset of rows via filter specification • Specify conflict resolution policy • Specify sync direction per member pair • Cater for schema updates and/or schema sync

  22. V1 Planned UI

  23. SQL Azure Data Sync Roadmap • Limited CTP2: • Available now, but closed • See me at end of session to get access! • Public CTP3: • Q3 2011 • V1: • Q4 2011

  24. SQL Azure CY11 Investment Themes FASTER INNOVATION Higher level Services & Developer Agility SCALE ON DEMAND Scalability and Performance MANAGED SERVICE Enterprise Readiness and Manageability • Rich Insights With Reporting as a Service • Ability to author rich reports, host in Azure and view in on-premises or cloud apps • Spanning/Hybrid Apps With Data Sync Service • Synchronize data between SQL Azure DBs and between SQL Azure & SQL Server DBs • Rich Developer Experiences • Visual Studio “Juneau” • Visual Studio LightSwitch • Supported JDBC driver • Localized Azure Portal • Data Protection/Recovery • Local Backup/PIT Restore: to recover from users/app errors • Geo Backup/Disaster Recovery from data center/geographical disasters • Data Movement • DB Import/Export of data and schema between SQL Azure and SQL Server; import/export via Windows Azure blobs • Migration support to SQL Azure from Sybase, in addition to existing support for Access, MySQL, Oracle • Enhanced Management Experiences • Enhanced portal-based management • Multiple DB administrators per subscription to support enterprise scenarios • Scale-out with Federation • Scale to virtually unlimited size to host very large databases across multiple servers • Better support for multi-tenant applications with numerous tenants • Scale-up & Scale-down Databases • Scale beyond 50GB, less than 1GB • Performance Predictability • Reserve compute capacity with Resource SLA • Enhanced multi-tenancy support • APIlevel support for multiple DB server per subscription, provisioning & de-provisioning

  25. Required Slide Speakers, please list the Breakout Sessions, Interactive Discussions, Labs, Demo Stations and Certification Exam that relate to your session. Also indicate when they can find you staffing in the TLC. Related Content • Breakout Sessions: • DBI306: Using Contained Databases and DACs to Build Applications in Microsoft SQL Server Code-Named "Denali" and SQL Azure • DBI307: Automating Database Migration to Microsoft SQL Server • COS310: Microsoft SQL Azure Overview: Tools, Demos and Walkthroughs of Key Features • DBI403: Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations • Product Demo Stations: • Cloud Computing & Online Services / Cloud Data Services • Find me later: • Cloud Data Services product demo station (10:30AM – 1:00PM)

  26. References • Overview information: • http://www.microsoft.com/windowsazure/ • http://www.microsoft.com/en-us/SQLAzure/datasync.aspx • http://www.microsoft.com/en-us/sqlazure/reporting.aspx • SQL Azure Forum: • http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/threads • SQL Azure Blog: • http://blogs.msdn.com/b/sqlazure/ • Feature voting: • http://www.mygreatwindowsazureidea.com/

  27. Free Windows Azure 30 Day Pass

  28. Track Resources Don’t forget to visit the Cloud Power area within the TLC (Blue Section) to see product demos and speak with experts about the Server & Cloud Platform solutions that help drive your business forward. You can also find the latest information about our products at the following links: • Cloud Power - http://www.microsoft.com/cloud/ • Private Cloud - http://www.microsoft.com/privatecloud/ • Windows Server - http://www.microsoft.com/windowsserver/ • Windows Azure - http://www.microsoft.com/windowsazure/ • Microsoft System Center - http://www.microsoft.com/systemcenter/ • Microsoft Forefront - http://www.microsoft.com/forefront/

  29. Resources • Connect. Share. Discuss. http://northamerica.msteched.com 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

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

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

More Related