Assessment - Migration to Microsoft SQL Server and SQL Azure

It is important to gather appropriate information about the migration project to

  • Understand the current system architecture, workload, performance, security and availability requirements
  • Identify the migration complexity, scope, cost and time, ROI
  • Discover critical and challenging issues, dependencies
  • Define best practices and guidelines to follow
  • Plan the migration

Assessment Process and Methodology

The migration assessment involves 3 major stages:

  • High Level System Review
  • Automated Assessment (Tools)
  • Migration Design and Proof-of-Concept (POC)

High Level System Review

Talking to the product owner, architects and lead developers you can understand the high level system architecture and the major characteristics.

What are the key technologies used?

  • Database (Oracle 11g, 10g i.e.)
  • Server and client operating systems (Windows, Linux, Solaris, HP-UX, AIX i.e.)
  • Programming languages (Java, C#, C++ i.e.)
  • Development environments (JDeveloper, Visual Studio, Eclipse, PowerBuilder, Oracle Forms i.e.)
  • Databases access APIs (JDBC, .NET, ODBC, OCI, Pro*C i.e.)
  • Application/web server runtime environments (JBoss, Apache Tomcat, IIS, WebLogic i.e.)


  • ISV, Packaged or In-House Developed Application(s)
  • Architecture (Client-server, n-tier, Web-based, distributed databases i.e.)
  • Workload Type (OLTP, BI, mixed)
  • The Number of Independent Installations (Different Customers, Departments i.e.)
  • The Number of Concurrent Users, Transactions
  • Availability (24/7, Weekdays i.e.)
  • Data Volume and Growth
  • Replication (Heterogeneous Systems i.e.)
  • Current Development (Active, Frozen, Maintenance)
  • Testing Environment, Updates Deployment and Recovery Process
  • Acceptable (scheduled) Downtime Window
  • Timeframe for Migration Project


  • Database Version, Edition and Operating System
  • Database Size
  • Character Set
  • The number of tables, views, indexes, sequences, synonyms
  • The number of databases (instances), schemas
  • The number of objects and lines of code of server-side business logic (stored procedures, packages, functions and triggers)
  • The number of objects and lines of code in maintenance scripts (shell, SQL scripts, jobs)
  • Security (users, roles)
  • High-availability configuration


  • Application Types (programming languages)
  • Database APIs (ODBC, JDBC, OCI, Pro*C, CTLIB etc.)
  • Multithreading
  • Operating Systems (Windows, Linux, Unix)

Oracle Database Assessment

Collect information about Oracle database(s).

Oracle Instances and Schemas

Information about Oracle instances and schemas.

Number of Databases (Oracle Instances)

Do you need to migrate a single instance (one or more schemas within an instance), or multiple instances?

Multiple Instances

If you need to migrate multiple instances:

  • Are they logically independent and can be migrated separately?
  • Are database links used between instances?


Information about applications working with Oracle database.

ISV, Packaged or In-House Developed Application

If it is an ISV application, do you need to support both Oracle and SQL Server after migration, and ship 2 versions of the software to your customers?

If is it a packaged application, what type is it?

  • SAP R/3
  • JD Edwards
  • PeopleSoft etc.

If it is a in-house application, how many independent installations do you have (in different locations, offices, departments of your company i.e.)

Application Types

What kind of applications are used.

Programming Language:

  • C/C++
  • C#
  • VB.NET
  • Java
  • JSP
  • PowerBuilder
  • Visual Basic
  • Oracle Forms
  • PHP
  • Perl
  • Python
  • Progress OpenEdge
  • Delphi
  • SQL*Plus, SQL*Loader and shell scripts

Database API:

What database access API and driver (provider) are used to work with Oracle.

  • ODBC
    • Oracle ODBC Driver
    • Microsoft ODBC Driver for Oracle
  • OCI
  • Pro*C
  • OLE DB
  • JDBC
  • SQLJ
    • Oracle Data Provider for .NET (ODP.NET)
    • .NET Data Provider for OLE DB (System.Data.OleDb)
    • .NET Data Provider for ODBC (System.Data.Odbc)
    • .NET Data Provider for Oracle (System.Data.OracleCLient)
  • Pro*COBOL

Operating System

What operating system do your applications run:

  • Windows
  • Linux
  • Solaris
  • HP-UX
  • AIX

Tools for Assessment

SQLines tools allow you to perform comprehensive migration assessment of SQL scripts, embedded SQL statements, database API and the entire applications.

SQLines can generate custom reports using various report templates and assessment functions. You can fully define the presentation (HTML, XML, flat file etc) and content of the reports.

Reports are interactive, you can sort and filter the data, and there are links to documentation, sample code and detailed problem descriptions for most issues found during an assessment.

How It Works

SQLines processes SQL scripts or application source code and generates assessment reports using the specified report templates.

If you need to assess objects that are stored in a database (views, stored procedures, triggers etc.), you have to extract them to SQL files before you can run the assessment. Also if you want to assess applications, you need to have access to their source code.

Key Features

SQLines tool provides the following assessment features:

  • Database objects
  • Data types
  • Statements
  • Built-in functions, packages, classes etc
  • Specific expressions, clauses, properties etc
  • Different (unique) conversion issues
  • Conversion issues description, reference, solutions and code examples
  • Lines of code requiring modification
  • Lines of code in files
  • Lines of code for various object types (tables, views, procedures etc.)

SQL Scripts Assessment

Additional features for database scripts assessment:

  • DDL Statements
  • SQL Queries and Subqueries
  • Stored Procedures, Functions and Triggers