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)
Download ReportTranscript 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)
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.
Upgrade
versus
Migration
…
Why Upgrade
OR
Migrate?
What is Upgrade?
Existing version of SQL Server to a higher version
(c) 2011 Microsoft. All rights reserved.
Extend any data End user analytics
On Premises & Private cloud Common Architecture Public Cloud
Database Migration Process
Database Discovery Non-code requirements (AlwaysOn, Perf, locale, maintenance, dependencies, etc…)
►
Migration Impact Assessment Schema Conversion Data Migration
Migration Assistant
Embedded SQL Statement Database connectivity User Login and Permission
Microsoft Assessment and Planning (MAP) Toolkit
► ► ► 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 Ideal discovery tool for Database Migration And Consolidation Projects http://www.microsoft.com/map
SQL Server Migration Assistant
SQL Server Migration Assistant
Automates and simplifies all phases of database migration
Migration Analyzer Schema Converter Data Migrator Migration Tester
Assess migration complexity Migrate schema and business logic Migrate data Validate converted database
Support migration from Oracle, Sybase, MySQL and Access database
The
complexity
of Migration…
“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.
Database Migration Challenges
The challenge of feature and non-standard mappings
UserD b SysDb Master PL/SQL
A closer look at Schema Conversion
SSMA Migration Best Practices
Customize SSMA project settings Customize mapping behavior Customize feature behavior Customize migration project Customize Data Type Mapping Leverage SQL Server native Datatypes Use the smallest datatype Rethink data type mapping and advantages Re-Engineer Code Rethink Stored Procedure Logic Rethink Function Logic Optimize Infrastructure, People and Processes Get
SQL SERVER MIGRATION ASSISTANT
F R E E
http://blogs.msdn.com/b/ssma/
SSMA Migration Paths
To the
Cloud
Migrations
Data Migration Scenarios
One-Off
• •
Scenarios
Migration to/from SQL Server Transfer between SQL Azure servers/databases • • • • • • • • •
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
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
Scenarios and examples
On-Premises Cloud Application SQL Server
• Single location, branch office, retail
Application SQL
•
Server
One-way publish, two way sharing, aggregation • • Reporting Web site reference data • • Geo-located web apps Traffic manager
Moving to and From SQL Azure
Resources
http://www.microsoft.com/sqlserver/en/us/product-info/migration.aspx [email protected]
http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration http://blogs.msdn.com/b/ssma/
QUESTION & ANSWER SESSION
(c) 2011 Microsoft. All rights reserved.
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
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
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 EXAMPLE ONLY GO TO THE TECHQUEST WEBSITE WWW.TAGLY.COM & CREATE A PROFILE
Dandy Weyn [email protected]
Sr. Technical Product Manager Database Track PM TechReady13 http://www.ilikesql.com
Twiiter @ilikesql
© 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.
(c) 2011 Microsoft. All rights reserved.
Resources
www.msteched.com/Australia Sessions On-Demand & Community http:// technet.microsoft.com/en-au Resources for IT Professionals www.microsoft.com/australia/learning Microsoft Certification & Training Resources http://msdn.microsoft.com/en-au Resources for Developers (c) 2011 Microsoft. All rights reserved.
APPENDIX
Schema Conversion Details
(c) 2011 Microsoft. All rights reserved.
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
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
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
►
Converting Oracle Sequences
Migration Challenge – Generate number sequence – not bound to a table – – NEXTVAL method to 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
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.