Migrating access applications to net and sql server
Download
1 / 22

Migrating Access Applications to .NET and SQL Server - PowerPoint PPT Presentation


  • 95 Views
  • Uploaded on

Ken Young [email protected] Tim Landgrave [email protected] Migrating Access Applications to .NET and SQL Server. Agenda. Why Migrate from Access? Current Access Migration Methods Why is Migration Tedious, Difficult and Expensive? What are the Business Benefits of Migrating?

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 ' Migrating Access Applications to .NET and SQL Server' - naiara


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
Migrating access applications to net and sql server

Ken Young [email protected]

Tim Landgrave [email protected]

Migrating Access Applications to .NET and SQL Server


Agenda
Agenda

  • Why Migrate from Access?

  • Current Access Migration Methods

  • Why is Migration Tedious, Difficult and Expensive?

  • What are the Business Benefits of Migrating?

  • Tool - Application Refactoring and Migration Suite (ARMS)

  • How Can ARMS Help and Why?


Driving issues for access migration
Driving issues for Access Migration

  • Not a Client/Server architecture

    • Value is Performance, flexibility, management

  • Basic database administration

  • Data re-centralization

  • Workgroup applications “grow up”

    • Number of user increase

    • Data capacity requirements outgrow Access MDB size and performance guidelines

    • Functional complexity outgrows MS Access and VBA capability

  • Security requirements tighten

  • End user freedom, agility

  • Data integration flexibility

  • Self-service (i.e. reporting, analysis)


Benefits to customers
Benefits to Customers

  • Platform Support

    • Windows Platform and .NET Framework

    • SQL Server 2000

      • RDBMS

      • Reporting Services

  • Visual Studio.NET

  • Enables Office 2003 deployment

    • Customers want to deploy Office 2003 and can’t due to Access 97 dependency

  • Customer Satisfaction

    • Eliminates Access database corruption issues

    • Increases the availability of the data (slow connections)

    • Removes Access MDB file deployment

  • Technical Advantages

    • Scalability

    • Data Security

    • Improved performance

    • Reliability

    • Team Development


  • Challenges until now
    Challenges – Until NOW!

    • Developer quality tools

    • Form & Report Migration was a manual process and therefore COST PROHIBITIVE

    • Reliable & consistent methodology

    • Migrate cost, and analysis thereof


    Access application types

    Mission

    Critical

    Apps

    Reporting Apps

    Departmental & Workgroup Applications

    Non-Mission Critical Apps

    Access Application types

    Component

    Re-use

    (forms, reports, modules)


    What an access application is

    Client

    Computer

    Client

    Computer

    Client

    Computer

    Client

    Computer

    What an Access Application is…

    Biggest Issue:

    Everyone obtains a copy of the MDB file in their client computers memory

    Network

    \\fileserver\sharename

    MS Access MDB File

    Every computer makes a local working copy of the MDB file

    Form

    VBA

    Code

    Report

    NOT a Server based architecture

    LIMIT: 2 gigabytes minus the

    space needed for system objects.

    • Key architecture concerns

    • Fat client

    • No Server side processing


    After migrating to net and sql server

    Client

    Computer

    Client

    Computer

    Client

    Computer

    Client

    Computer

    Microsoft .NET

    Application Server

    Microsoft .NET

    Application Server

    Microsoft .NET

    Application Server

    Form

    Form

    Form

    Code

    Code

    Code

    Report

    Report

    Report

    After Migrating to .NET and SQL Server

    • Key benefits:

    • Thin client/Browser Option

    • Rich Client

    • Strong Security (SSL)

    Network

    Every computer only uses data they need!

    Pure Client/Server based architecture

    Microsoft SQL Server

    • Key benefits:

    • Reliability

    • Performance

    • Scalability

    • Security

    • Flexibility

    • Management & Control

    • Reduced cost of ownership

    Database


    Refactoring @ work

    User Interface

    Business Logic

    Data Access

    Refactoring @ Work

    Access Application

    Automate

    SQL Server


    What this means
    What this means

    • Deployment

      • No more downloading .mdb to client

    • Better memory utilization on client

      • No longer loading Access engine on workstation

    • Better utilization of SQL Server


    Migration process
    Migration Process

    • Data

    • QueryDefs

    • Forms

    • Code

    • Reports


    Processing data
    Processing Data

    • Migrate data using the Upsizing Wizard

      • Does a decent job

      • Reports on the process

      • Can result in .mdb linked to SQL


    Processing querydefs
    Processing QueryDefs

    • Upsizing wizard does not do much

    • SQL Syntax must match SQL Server

      • Lots of tweaking

      • Testing of new syntax

      • Porting to stored procedure / view is optional


    Processing forms
    Processing Forms

    • Forms require recoding

    • Very tedious process

      • Controls/properties/attributes

      • Rewiring event handlers


    Processing code
    Processing Code

    • Convert code to VB.NET

      • Language constructs (VBA to VB.NET)

      • Access specific code

        • Items such as message box, forms, etc

        • Data features such as dlookup

      • Data objects

        • DAO / RDO to ADO.NET


    Processing reports
    Processing Reports

    • SQL Reporting Services

      • Import Access reports to SQL Reports

      • Moves reports to web

      • Allows for feature rich easy to use reports

        • IT and user friendly


    Migration tools
    Migration Tools

    • 2nd Genesis Software Application Refactoring and Migration Suite (ARMS.NET)

      • Form Converter

      • Object Builder

      • Access2NET Resource Kit

        • QueryDef Converter

        • VBA to .NET

        • Win Forms Databinder

    • SQL Server Reporting Services Import Tool (Microsoft)

    • Coming Soon…

      • Access to ASP.NET


    Automated migration using 2 nd genesis arms net tools
    Automated Migration Using 2nd Genesis ARMS.NET Tools

    Tools Save $$

    2nd Genesis ARMS.NET – Forms, QueryDefs, Code

    Microsoft Access Upsizing Wizard

    Mission

    Critical

    Apps

    Reporting Apps

    Reporting Services Import Tool

    2nd Genesis ARMS.NET – Forms, QueryDefs, Code

    Microsoft Access Upsizing Wizard

    Departmental & Workgroup Applications

    Component

    Re-use

    (forms, reports, modules)

    2nd Genesis ARMS.NET

    Non-Mission Critical Apps


    Migration process using arms net
    Migration Process Using ARMS.NET

    • Analyze the initial .mdb using

    • Create plan

    • Convert the Access Application

      • Data

        • Upsize the Access MDB to SQL Server

        • Convert Access QueryDefs to SQL Server Stored Procedures

      • UI

        • Convert Access forms to .NET WinForms

      • Business

        • Migrate VBA Code to .NET Classes that manage the data

        • Tie Forms to .NET Classes

      • Reports

        • Convert Access Reports to SQL Reporting Services reports

        • Modify QueryDefs to use SQL Server data directly

      • Final System Testing


    Conversion example
    Conversion Example

    • National Services company has built its business on one Access file

      • Initial .mdb was 64MB (one project)

        • No data, only forms and code

      • Analysis when converted shows:


    Developer productivity
    Developer Productivity

    • Typical approach to manual forms conversion with error rate, analysis, QA, and repair applied to example customer application

    • For Example, a single form with 10 controls with 150 properties will require:

      • 2 hours to analyze

      • 16 hours to convert code

      • 6.64 hours to debug and repair

      • At $50/hour (burdened rate), this single form costs $1,232 to convert

      • Sample application is 16,000 hours in forms

        • Which at $50/hour is approximately $800,000 JUST for Form conversion

    • Conversion without tools is cost prohibitive!


    Why our approach is better
    Why our approach is better

    • Fewer errors in development

      • Client does not pay to find and fix errors not introduced

        • Cost is drastically lower than manual conversion

      • More reliable code

      • Focus on features not mundane tasks

    • Better use of developer effort

      • Adding new Functionality

      • Re-factoring - not rewriting remodel to achieve goals above

    • Bottom line

      • Reduction of engineering staff for mundane tasks

      • QA improves dramatically (developer errors go down dramatically)

      • Client should not spend money to have bodies convert Access attributes to .NET target attributes


    ad