PEACE OF MIND: QUICK TOUR THROUGH THE NEW SQL SERVER MIGRATION ASSISTANT (SSMA)

Dmitry Balin contains materials and information that Microsoft considers confidential, proprietary, and DB Best Technologies Confidential and Proprietary © 2011 Microsoft Last Updated: Friday, April 24, 2020

SESSION OBJECTIVES AND TAKEAWAYS

• • • • • • Why Migrate to SQL Server Database and Application Migration Process SQL Server Migration Assistant (SSMA) Database Migration Challenges Migration Best Practices Resources

WHY MIGRATE TO SQL SERVER

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 reduced annual licensing costs but we also improved uptime.

We improved development productivity and lowered cost after migrating our mission-critical system from Oracle.

DATABASE AND APPLICATION MIGRATION PROCESS

• •

Database Discovery Non-code requirements (HADR, Perf, locale, maintenance, dependencies, etc.)

• • • •

Migration Impact Assessment Schema Conversion Data Migration Embedded SQL Statements

• •

Database connectivity User Login and Permission

DEMO

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 Convert schema and business logic Migrate data Validate converted database code

Support migration from Oracle, Sybase, MySQL and Access databases to SQL Server

DATABASE MIGRATION CHALLENGES

Feature and dialect differences requires non-direct mapping and emulation

PL/SQL User Db SysDb Master

DEMO

A Closer Look at Schema Conversion

WHAT’S NEW IN SSMA

SSMA 5.0

 Migration to SQL Server 2012  Multi-thread data migration  Globalization support  Oracle User Defined Type discovery  Sybase migration enhancements

SSMA 5.1

MIGRATION BEST PRACTICES

Customize SSMA project settings • 65 configuration options for Oracle Migration • • 55 configuration options for Sybase Migration 65 configuration options for MySQL Migration 25 configuration options for Access Migration • Testing and validation • Allocate ~30% of the project effort to testing and validation of the converted code • Use tools (e.g. DB Best Database Compare Suite) to validate schema and data conversion Performance testing and benchmarking is key metrics for project success • Application conversion • Analyze and identify all applications interfacing with the Oracle database • Look for embedded SQL statements spread across the application code Optimize infrastructure, people and processes • Server, storage, security, maintenance, monitoring, troubleshooting • Advanced level training/workshops

RESOURCES

• Database Migration Resources & Whitepaper http://www.microsoft.com/sqlserver/en/us/product info/migration.aspx • Free Technical Product support [email protected]

(FREE technical support from Microsoft CSS) • Discussion Forums http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration • SSMA Team Blog http://blogs.msdn.com/b/ssma/ • Additional migration resources and tools http://www.DBBest.com

FURTHER. FORWARD. FASTER.

•

Learn more

Visit the Microsoft Virtual Academy

Free technical SQL Server training courses •

Get more

Download SQL Server 2012 Trial Software

180-day evaluation of SQL Server 2012 •

Do more

Participate in virtual launch activities and collect points for prizes

The more points you earn, the bigger your prize could be

APPENDIX

CONVERTING DATA TYPES

Migration Challenges • Dynamic precision and scale o NUMBER, VARCHAR2 • Wider range of supported value o o 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 o User defined type: Object type, collections SSMA Conversion Approach • Convert to maximum possible o o NUMBER converted to float(53), which has the maximum precision from SQL Server floating-point numbers VARCHAR2 converted to VARCHAR(MAX) • Project setting o 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 • Parameter o Support IN, OUT and IN OUT parameters o o Scale and precision not specified Parameter overloading • Procedures don’t return result sets o 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 SSMA Conversion Approach • 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 • 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

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

SSMA 5.0 SYBASE MIGRATION ENHANCEMENTS

• Support for migration to SQL Azure.

• Extended data access connectivity to Sybase ASE ADO.NET and ASE ODBC providers.

• Support for conversion of case sensitive Sybase database to case sensitive SQL Server.

• Extended support for conversion of Non-ANSI joins for DELETE and UPDATE statements.

• Removed dependency on separate SYSDB database on target SQL Server.

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