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.