Migrations from Oracle/Sybase/DB2 to

Microsoft SQL Server– it’s easy!

January 2010

Dmitry Balin

dmitry@dbbest.com

Academy| Enterprise Partner Group

Successful migrations

DB Best Technologies – about us

Established in March 2002 as LLC in Delaware

80+ employees

Offices in US, Europe, Ukraine

DB Best has been developing database products and providing consulting

services

Microsoft acquired DB Best migration products in January 2005

Academy| Enterprise Partner Group

Microsoft acquired DB Best migration products in January 2005

Main business activities:

database consulting (migrations, upgrades, remote DBA, BI/ETL)

custom software development (.Net, Java, XML, Perl, Web 2.0)

developing database products (DB Best Database Compare, T-SQL Analyzer,

OraMigrator)

SQL Server MigrationsWhy the focus on migrations & what are some of the key challenges?

Sales Blockers How we can help

Migrations to SQL Server�

�

“60 – 65% of migrating customers are migrating from Oracle because of cost.”

Noel Yuhanna, Forrester Analyst

* Source: Microsoft CMG DBTracker G7 Summary 2007

Sales Blockers�

�

�

�

�

How we can help�� Migration Solution Center Migration Solution Center

��

�� SSMAs (Oracle, Sybase, Access & SSMAs (Oracle, Sybase, Access & PowerBuilder)PowerBuilder)

��

�� SSMAhelpSSMAhelp

5

� SQL Server Total Cost of Ownership� No price increase for SQL Server 2008� Migration calculations http://www.cioview.com/� Learn of the total economic impact deploying SQL Server 2008 can have: New Independent

Study Finds 162% Risk-Adjusted ROI for SQL Server 2008

� Enterprise Data Platform:

� SQL Server Enterprise Data Platform

The Value of Migrating to SQL ServerWhy should customers consider migrating to SQL Server?

� SQL Server Features & Function: In depth comparison analysis with key competitorsdemonstrate the power of SQL Server.

� Oracle Comparisons� SQL Server 2008 compared to Oracle 11g database. Deployment� Why use SQL Server instead of Oracle for SAP

� Compare SQL Server to IBM DB2� Compare SQL Server to MySQL� Redmond Magazine: SQL Server Closing the Gap

� SQL Server Security & Compliance: SQL Server 2008 is secure by design, default, and deployment. The following resources define these capabilities.� SQL Server 2008 Security Overview for Database Administrators� Compliance Guide for SQL Server 2008

6

Identifying & Enabling Migration OpportunitiesHow to spot & activate migration ready scenarios?

Ecosystem

• Heterogeneous Environments: >90% of all enterprise customers are running multiple database products. Business capabilities (BI, DW) are a key consideration and investment areas.

Customer Question: Do all the existing database platforms in your organization meet your business needs?

• Application Upgrades: Upgrades are points in time when companies deeply consider their underlying data storage.

Budgetary or Vendor Satisfaction

• Cost Cutting: In today’s economic climate many customers are cutting budgets in IT.

Question: How much is your current database environment costing you and how much less could it be on SQL Server?

• SQL Server as the Standard: Many companies either do not have SQL Server as a standard or do. There are questions that can enable migrations for both.

Question: Getting SQL Server on your standards list can influence costs of your other database vendors. data storage.

Customer Question: As part of your application upgrade are their capability enhancements and/or cost reduction benefits your organization could realize?

• Product Support: Product end of support life-cycles are times when customers will think through their future investments in a product.

Customer Question: With your current database reaching end-of-support, have you analyzed cost savings or improved capabilities that your organization could realize?

• Hardware or Infrastructure Upgrades: When customers upgrade hardware they are typically scaling up or out existing line of business applications.

Customer Question: Does your underlying data platform scale to meet new demands?

can influence costs of your other database vendors. What will make you consider adding SQL Server as a standard?

Question: You’ve already made SQL Server a standard. Have you looked a ways of cutting costs by moving existing databases to SQL?

• Database Vendor Satisfaction: Dissatisfaction with current database vendors (e.g. performance, support, maintenance costs) can cause customers to evaluate their database use scenarios.

Customer Question: Are you fully satisfied with your existing database vendor? Do they compete for your business on cost, feature, and quality of support? Or do they compete directly with you?

7

Typical Migration Scenarios

� Two types of applications:� Custom-written: Migration can be done with the customer and a

partner

� Packaged: Talk to the ISV

� Always do assessment first!

� (using Migration Analyzer module of SSMA + � (using Migration Analyzer module of SSMA +

questionnaire)

� Many times involves working with SI

specializing in database migrations

8

Using SQL Server Migration Assistant (SSMA)

Assess the Assess the Migration ProjectMigration Project

Migrate SchemaMigrate Schema

SSMA Migration AnalyzerSSMA Migration Analyzer

SSMA Data SSMA Data MigratorMigrator

� SSMA: Automates components of database migrations to SQL Server

� SSMAs (Oracle, Sybase, Access & PowerBuilder Analyzer)

1111

22

SSMA Schema ConverterSSMA Schema Converter

Migrate SchemaMigrate Schema& Business Logic & Business Logic

Test the Test the Converted DBConverted DB

Convert theConvert theApplicationApplication

Test, Integrate Test, Integrate & Deploy& Deploy

Migrate Data Migrate Data

SSMA Data SSMA Data MigratorMigrator

SSMA Migration TesterSSMA Migration Tester

9

2222

3333

4444

SSMA Migration Analyzer

� Running it is the easiest way to start a migration project

� Fill out the questionnaire – helps with understanding the environment

� Does the same as the Schema Converter, but doesn’t show the conversion results

� Preferably to run on a backup/development/test database in order not to interfere with operations

� You can archive (zip) the reports directory, let us know (send email to ssmahelp@microsoft.com), and we’ll try to help you with interpreting the results

SSMA Migration ProcessSSMA Migration Process

Data MigratorData Migrator TesterTesterSchema Migrator Schema Migrator AnalyzerAnalyzer

10

SSMA Schema Converter� Almost never converts 100% (although, sometimes does ☺ - Motorola,

Valero)

� Default settings usually work for the initial conversion, but may require changes (e.g. type mappings) as the project progresses

� Sometimes it’s easier to make changes in the source stored procedures rather than complete the conversion manually

� If you don’t know what to do with the conversion errors:

� save the converted code as a script or save a project file

� send it to ssmahelp@microsoft.com

� SSMA support team will analyze the script and will provide some help

SSMA Migration ProcessSSMA Migration Process

Data MigratorData Migrator TesterTesterAnalyzerAnalyzer Schema Converter Schema Converter

11

SSMA Data Migrator

� SSMA for Oracle v2 – uses linked server to move data

� logged operation => slower than SSIS

� good enough when need to move low data volumes

� SSMA for Sybase/Access/Oracle v3-4 – uses direct pipe to move data

� reads and writes data using non-logged operation

� on par with SSIS or bcp out/in� on par with SSIS or bcp out/in

� doesn’t write to the disk

SSMA Migration ProcessSSMA Migration Process

TesterTesterAnalyzerAnalyzer Schema Migrator Schema Migrator Data MigratorData Migrator

12

SSMA Migration Tester

� Was introduced first in SSMA for Oracle v3.1 (August ‘07)

� Exists in SSMA for Oracle and Sybase v4

� Typically used for unit testing of converted stored procedures

� Customers prefer to run it on existing data sets, rather than letting the tool to create data

� The next most expensive migration step after the schema conversion� The next most expensive migration step after the schema conversion

SSMA Migration ProcessSSMA Migration Process

Data MigratorData MigratorAnalyzerAnalyzer Schema Migrator Schema Migrator TesterTester

13

SSMA Migration Challenges

Oracle Sybase Access

�� Oracle FormsOracle Forms

�� RowRow--level triggerslevel triggers

�� PackagesPackages

�� Package variablesPackage variables

�� Collections (nested Collections (nested tables, VARRAYs, objects)tables, VARRAYs, objects)

�� Outer joinsOuter joins

�� RAISERRORRAISERROR

�� PRINTPRINT

�� UPDATE with aliasUPDATE with alias

�� Comparison and Comparison and concatenation with concatenation with

�� Conversion queriesConversion queries

�� Application Application performance post performance post migrationmigration

tables, VARRAYs, objects)tables, VARRAYs, objects)

�� System packages (not System packages (not supported by SSMA supported by SSMA Extension Pack)Extension Pack)

concatenation with concatenation with NULLNULL

�� SybaseSybase--toto--SQL SQL replicationreplication

14

“Without SSMA, it would have been a huge undertaking just to inventory and catalog the system,

let alone do the conversion.” Tom Masciovecchio,

Director of Publishing Systems, Simon & Schuster

“SQL Server Migration Assistant allowed us to migrate the database in just one day with 100% accuracy. Our DBA

was able to stay focused on more important things.”Gordan Redzic,

Automation Team Lead, iDEN Automation Group, Motorola

SQL Server Migration Assistant Customer Evidence

SQL Server Migration Assistant (SSMA) was very simple to use and ensured

data integrity and accuracy during the migration.”Sanjay Bhadra,

Alliance Manager, Sonata Software

“SSMA automated the migration, saving us from having to do hours and hours of manual coding.”

Paulo Henrique da Silva, IT Manager, Atento

All case studies and testimonials are at: http://www.microsoft.com/sql/migration/custevidence.mspx15

Making it Real

Database Landscape

Oracle

� Longtime Oracle & Teradatacustomer

� Oracle Customer Advisory

Removing Barriers

� Platform capabilities & TCO vs. Oracle

� Sales coverage with database leadership team

“Mission critical DB’s run in Ford’s manufacturing, engineering & product planning groups. If DB’s

go down, plants can’t build & ship cars. Ford is in the business of designing, engineering,

manufacturing & selling cars – period.”Jon Brusseau, Global ATS, Microsoft

Demonstrated Results

� June ’07 Ford made Oracle the exception & SQL the new standard

� $10 million dollar savings per year in maintenance fees

� Oracle Customer Advisory Board

� ~1200 instances of Oracle

� 2500-3200 Oracle apps in production

SQL Server

� ~ 120 instances of SQL

� SQL primary entry via 3rd party machinery

� Global resource coordination:

� Chicago MTC for SQL HA solution

� Oracle SSMA

� DB migration event

� Database and application conversion completed through Migration CoE

� Knowledge transfer to Ford migration center (India) from Migration CoE (Ukraine)

�

year in maintenance fees

� Removed from Oracle Customer Advisory Board

� Ford Microsoft reference-able

� Over 1200 apps migrated

� SQL 2008 certified by January

Call to Action & Resources

•• Consider migration opportunities to SQL Server in your existing ecosystem Consider migration opportunities to SQL Server in your existing ecosystem

•• Leverage the available tools & resources Leverage the available tools & resources –– Download SSMA now!Download SSMA now!

Resources Links

SQL Server Migration Solution Center http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx

Resources

Call to Action

17

SQL Server Migration Solution Center http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx

SQL Server Migration Assistants http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx

SQL Server Migration Assistant

SupportSSMAhelp@microsoft.com

DB Best Technologies – a migration

partnerhttp://www.dbbest.com, http://dbbest.net/blog

Dmitry BalinDB Best TechnologiesCEOdmitry@dbbest.com408-202-4567

Do you have any final questions?

Dmitry Balin

Academy| Enterprise Partner Group

Dmitry Balin

dmitry@dbbest.com

1 408 202 4567