slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Migration Done Right - Migrating to SQL Server on premise and in the cloud PowerPoint Presentation
Download Presentation
Migration Done Right - Migrating to SQL Server on premise and in the cloud

Loading in 2 Seconds...

play fullscreen
1 / 36

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


  • 239 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Migration Done Right - Migrating to SQL Server on premise and in the cloud' - job


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

(c) 2011 Microsoft. All rights reserved.

what is upgrade
What is Upgrade?

Existing version of SQL Server to a higher version

(c) 2011 Microsoft. All rights reserved.

slide6

Extend any data

End user analytics

Common Tools

Appliances

OData

Data Sync

Code Name “Denali”

On Premises & Private cloud

Common Architecture

Public Cloud

database migration process
Migration Impact AssessmentDatabase 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
microsoft assessment and planning map toolkit
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
slide9
demo

SQL Server Migration Assistant

sql server migration assistant
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
crikey your language is slightly different
“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
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

slide15
demo

A closer look at Schema Conversion

slide16

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

data migration scenarios
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
scenarios and examples
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

slide21
demo

Moving to and From SQL Azure

slide22

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/
question answer session
Question & Answer Session

(c) 2011 Microsoft. All rights reserved.

migration success stories and customer evidence
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
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

slide26
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

slide27

Dandy Weyn

dandyw@microsoft.com

Sr. Technical Product Manager

Database Track PM TechReady13

http://www.ilikesql.com

Twiiter @ilikesql

slide28

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

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

schema conversion details

Schema Conversion Details

(c) 2011 Microsoft. All rights reserved.

converting data types
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
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
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
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

converting oracle triggers
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.