1 / 36

Migration Done Right - Migrating to SQL Server on premise and in the cloud

SESSION CODE: DAT304. Dandy Weyn Sr. Technical Product Manager Microsoft Corp. Migration Done Right - Migrating to SQL Server on premise and in the cloud. Upgrade versus Migration …. Why Upgrade OR Migrate?. What is Upgrade ?. Existing version of SQL Server to a higher version.

job
Download Presentation

Migration Done Right - Migrating to SQL Server on premise and in the cloud

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: DAT304 Dandy Weyn Sr. Technical Product Manager Microsoft Corp. Migration Done Right -Migrating to SQL Server on premise and in the cloud (c) 2011 Microsoft. All rights reserved.

  2. Upgradeversus Migration…

  3. Why UpgradeORMigrate?

  4. What is Upgrade? Existing version of SQL Server to a higher version (c) 2011 Microsoft. All rights reserved.

  5. Extend any data End user analytics Common Tools Appliances OData Data Sync Code Name “Denali” On Premises & Private cloud Common Architecture Public Cloud

  6. Migration Impact Assessment Database Migration Process Scoping and Planning Microsoft Assessment and Planning Toolkit • Database Discovery Database Migration • Non-code requirements (AlwaysOn, Perf, locale, maintenance, dependencies, etc…) Application Remediation • Schema Conversion • Data Migration Application Deployment Migration Assistant • Embedded SQL Statement • Database connectivity • User Login and Permission

  7. Microsoft Assessment and Planning (MAP) Toolkit Ideal discovery tool for Database Migration And Consolidation Projects http://www.microsoft.com/map • Agentless inventory and assessment • Provides readiness assessments for • Windows 7, Office 2010, Internet Explorer • Windows Server 2008 R2, Hyper-V • Windows Azure Platform • SQL Server, Oracle, MySQL, and Sybase Discovery • Generates customizable proposals and reports

  8. demo SQL Server Migration Assistant

  9. SQL Server Migration Assistant Automates and simplifies all phases of database migration • Assess migration complexity • Migration Analyzer • Migrate schema and business logic • Schema Converter • Migrate data • Data Migrator Support migration from Oracle, Sybase, MySQL and Access database • Validate converted database • Migration Tester

  10. The complexity of Migration…

  11. “Crikey!Your language is slightly different …” “you triggered me BEFORE… instead of AFTER” “I am a variable character too” “The way I deal with dates is different too” “I am a total package” “What’s that sequence?” I said, "Do you speak-a my language?" (c) 2011 Microsoft. All rights reserved.

  12. Database Migration Challenges The challenge of feature and non-standard mappings SQL Server Oracle Table/View Table/View Data Types Data Types T-SQL Procedure Procedure User Function User Function Package UserDb Sequence Trigger Trigger System Function System Function Emulator Standard Package PL/SQL SysDb Master

  13. demo A closer look at Schema Conversion

  14. SSMA Migration Best Practices Customize SSMA project settings Customize mapping behavior Customize feature behavior Customize migration project Customize Data Type Mapping GetSQL SERVER MIGRATION ASSISTANT Leverage SQL Server native Datatypes Use the smallest datatype Rethink data type mapping and advantages F R E E Re-Engineer Code Rethink Stored Procedure Logic Rethink Function Logic http://blogs.msdn.com/b/ssma/ Optimize Infrastructure, People and Processes

  15. SSMA Migration Paths

  16. To the Cloud Migrations

  17. Data Migration Scenarios One-Off Scenarios Tools • BCP • SSMS Generate script wizard • SQL Server Import & Export Data • SSIS • SQL Azure Database Copy • SQL Azure Migration Wizard • DAC • DAC Logical Imort/Export • SQL Server Migration Assistant • Migration to/from SQL Server • Transfer between SQL Azure servers/databases On-Going • Publish to cloud • Share between on-prem and cloud apps • Data distribution • One-way or two-way • SQL Azure Data Sync • Sync Framework

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

  19. demo Moving to and From SQL Azure

  20. Resources Database Migration Resources & WhitePaper http://www.microsoft.com/sqlserver/en/us/product-info/migration.aspx Free Technical Support ssmahelp@microsoft.com Discussion Forum • http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration SSMA Team Blog • http://blogs.msdn.com/b/ssma/

  21. Question & Answer Session (c) 2011 Microsoft. All rights reserved.

  22. Migration Success stories and customer evidence What some of our customers say … We moved nine-terabyte system migrated from Oracle using SSMA that resulted in significant TCO savings. • We were going for Better - Faster - Cheaper. And we attained all three! • We not only reduce $63,000 in annual licensing cost but we also improved uptime. • We improved development productivity and lowered cost after migrating our mission-critical system from Oracle. http://www.microsoft.com/casestudies

  23. Win a Touch Mouse! Complete your session evaluation on Schedule Builder for your chance to win a Windows 7 Touch Mouse • Exclusive for Windows 7 • Eleven multi touch gestures • Contoured shape for superior comfort 10 prizes drawn every day!* *Please see Registration for full terms and conditions

  24. GET REWARDED FOR SIMPLY PARTICIPATING IN TECH∙ED AUSTRALIA DOWNLOAD QR CODE READER FOR YOUR MOBILE DEVICE Windows Phone 7 we suggest Quick Reader Symbian we suggest the built in reader or mobile Tag IOS we suggest QR Reader Blackberry we suggest Mobile Tag Android phones we suggest Barcode Scanner GO TO THE TECHQUEST WEBSITE WWW.TAGLY.COM & CREATE A PROFILE EXAMPLE ONLY

  25. Dandy Weyn dandyw@microsoft.com Sr. Technical Product Manager Database Track PM TechReady13 http://www.ilikesql.com Twiiter @ilikesql

  26. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarksin 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. (c) 2011 Microsoft. All rights reserved.

  27. Resources • www.msteched.com/Australia • Sessions On-Demand & Community • www.microsoft.com/australia/learning • Microsoft Certification & Training Resources • http://msdn.microsoft.com/en-au • Resources for Developers • http:// technet.microsoft.com/en-au • Resources for IT Professionals (c) 2011 Microsoft. All rights reserved.

  28. aPPENDIX

  29. Schema Conversion Details (c) 2011 Microsoft. All rights reserved.

  30. Converting Data Types • Migration Challenges • Dynamic precision and scale • NUMBER, VARCHAR2 • Wider range of supported value • Oracle stores from 4712 while SQL Server’s DATETIME stores from 01/01/1753 and DATETIME2 stores from 01/01/0001 • TIMESTAMP has nanosecond precision while DATETIME2 stores up to 100 nanoseconds • Complex data type • User defined type: Object type, collections • SSMA Conversion Approach • Convert to maximum possible • NUMBER converted to float(53), which has the maximum precision from SQL Server floating-point numbers • VARCHAR2 converted to VARCHAR(MAX) • Project setting • specify how to handle out of bound values during data migration • Error, null, closest supported value • Raise conversion message when user defined type is used in table, view, and PL/SQL program

  31. Converting Procedures and Functions • Migration Challenge • No big differences between procedure and function in Oracle • error handling • DML statement: update, insert, delete • temporary table access • calling procedures (except extended procedures) • Parameter • Support IN, OUT and IN OUT parameters • Scale and precision not specified • Parameter overloading • Procedures don’t return result sets • Use cursors, records and collection as output parameter • Autonomous Transaction • SSMA Conversion Approach • Functions converted to function and stored procedure when containing operations forbidden in functions are needed • such procedures have $IMPL suffix • Extended stored procedure to call procedure within function and emulate autonomous transaction • master..xp_ora2ms_exec2

  32. Converting Oracle Packages • Migration Challenge • Oracle specific implementation to logically group schema objects • Compare to SQL Server’s Schema: • no concept of global package objects • variables and constants • no concepts of private/public objects • private functions, procedures, types • no concept of package state • e.g. invalid because of compilation errors • Module nesting • definition of procedures/functions • inside other modules • SSMA Conversion Approach • Convert to procedure and function with the following naming convention • dbo.PACKAGENAME$MODULENAME • Emulate package variable by storing values in a central table • Inline substitution for module nesting

  33. Converting Oracle Sequences • Migration Challenge • Generate number sequence – not bound to a table • NEXTVAL methodto generate next value of a sequence • CURRVAL method to retrieve current value of the sequence (bound to current session scope) • SSMA Conversion Approach • One table per sequence • uses IDENTITY • get values through procedures/functions • Maintenance • additional procedures to drop/create • Limitation • min/max values are not enforced • functions are slower • use extended stored procedure to exec procedure • some options not available • NOCYCLE, NOCACHE, NOORDER SQL Server code name Denali supports SEQUENCES

  34. Converting Oracle Triggers • Migration Challenge • FOR EACH ROW trigger • The most common Oracle trigger which fires for each row of the source statement. • SQL Server only supports statement level trigger • Before trigger • Possible to modify the actual field values that will be stored in the table, or even cancel the execution • Allow multiple trigger for each events (INSERT or UPDATE) • SQL Server only support one trigger per event • SSMA Conversion Approach • Row-level triggers are emulated with a cursor loop and generate ROWID for table with trigger. • BEFORE triggers are converted to INSTEAD OF trigger • Incorporate triggering statement into the trigger body • Cursor loop to handle affected multiple rows • Logic from all BEFORE triggers on that table into a single target trigger.

More Related