Informatika | Adatbázisok » Database migration methodology

2.1 Database Migration Methodology Methodology for database migration is proven, time-tested and has been successfully implemented in many projects across the globe for various clients. Blends this migration methodology with Database Migration tool provided by Microsoft named SSMA – SQL Server Migration Assistant for executing this migration. The highlights of the methodology are as follows: • The solution consists of two distinct stages namely, the preparation stage and the implementation stage. This distinction provides greater control and hence better management of a data migration project. • All the activities in the preparation stage are carried out based on the implementation stage for data migration. • The constraints on implementation like the batch window for cut-over, data cleanliness, national language conversion and other complexities are taken into account right from the project start-up through the activities in the preparation stage. • Best practices

and lessons learnt from earlier data migration assignments are embedded into the approach. The following figure shows the high-level view of database conversion methodology. Preparation Stage Strategy Definition Analysis Design Build Inventory Data Model Analysis Mapping DB Object Migration Scripts Mapping Strategy & Standards Data Analysis Migration Script Design ETL Scripts Tool Selection Data Quality Assessment Data Cleansing Scripts Rollout Options Extraction, Audit & Validation Criteria Audit & Validation Scripts ImplementationStage Roll-out Rehearsal Environment Setup Environment Setup Migrate Database Object Migrate Database Object Extract Data Parallel Runs Transform Data Full Dress Rehearsal Load Data Verification & Validation Database Conversion Methodology understands, based on the RFP requirements and AIG investments’s response to queries, that actual production implementation will be handled by AIG investments with support.

Hence the focus of the conversion strategy for will be on the Preparation stage only. The different steps of the preparation stage are described in the table below: • Step Strategy Definition • Step Sub- • Description Inventory Consolidation of inventory on source and target databases, data models, volumes of data available in the different data stores. Migration Analyzer tool which is part of the Microsoft SSMA toolset would be used for this purpose. Mapping Strategy Finalization of mapping strategy. Target to source is the preferred option. SSMA offers Schema & Data Migrator tool that automatically maps most of the source Oracle database objects to SQL Server objects. Strategy needs to be defined for anything that does not map by the tool or that needs to be handled differently. Analysis Design Build Tool Selection proposes to use SQL Server Migration Assistant (SSMA) for the migration. Rollout Options Rollout or cut-over options for production data

migration. The choice of big-bang, incremental or long-bang option is decided based on the Client’s requirements and constraints Data Model Analysis Analysis of the source and target data models for cardinality and optionality. Data stores with multiple layouts are analyzed for record identifiers. Data Analysis Derivation of the domains, ranges and set of valid values for a set of data items identified for profiling. Data Quality Assessment Identification of unclean data in terms of business as well as domain values. The detection rules are employed for this purpose Extraction, Audit and Validation Criteria Derivation of extraction criteria based on the analysis results and choice of implementation option. The audit and validation criteria are derived based on the requirements for assessing completeness and correctness of data migration. Mapping Derivation of the mapping rules and specifications based on the source and target data structures, types and domain information.

Business logic present in stored procedures and functions also need to be converted. Migration Script Design Derivation of the sequence of execution of scripts based on the dependencies (both technical and business). The batch window available for cut-over is the guiding principle for the design. Development of Scripts Development and Unit Testing of the database object migration scripts, data extraction scripts, data transformation scripts (some of the cleansing rules are accommodated as part of transformation programs), additional data cleansing scripts, if required, audit and data validation scripts. These scripts are also integration tested, if required. The Schema & Data Migrator and SQL Converter tool will be used • Step • Step Sub- • Description for actual data migration. The process steps to be executed using this tool for the migration would be listed down as part of the development scripts along with any other custom scripts that are needed. The

testing requirements as part of the build phase can vary based on the implementation options and the business requirements. Business continuity factors, constraints in infrastructure and availability of specialists are considered and the suitable testing methodology is recommended. Performance management is a critical component in data migration due to the batch window constraints on cut-over. This is addressed from the planning through the build phases. 2.2 Database Migration Technical Approach There are many benefits on moving to SQL Server from Oracle database. Key benefits are listed below. • • • • SQL Server provides Integrated Business Intelligence, Reporting and ETL features that to improve the capabilities of the underlying solution Reduces total cost of ownership: Lower licensing, people and administrative costs Highly productive Microsoft tool. – quickly build applications on SQL Server SQL Server is relatively easy to install and administer The following

figure gives a high-level view of the steps for database platform migration from source Oracle database to target SQL Server 2005 database. Source/ Existing Database (Oracle) Target Configuration Metadata & Database Architecture (Sql Server) Source Configuration Metadata (Oracle Database Mapping & Translation Target Object Scripts & Modules Deployment Package for Oracle Objects Target Database (Sql Server ) ORACLE to SQL Server 2005 Platform Migration Methodology A brief overview of the different steps involved in the approach is described in this sub-section. 1. Acquire Source Metadata In this step the detailed Metadata will be acquired for the existing Oracle database platform. This will include (not limited to) gathering information about the following areas: • Current Hardware configuration and estimate for capacity • Current Operating System and specific configuration for the Oracle database • Oracle database configuration Parameters about Disk

Storage, I/O, Cache (SQL and Data), DB layout, Archival, Backup and so on. • Oracle database and Schema objects – Partitions, Tables, Constraints, Indices, Views, Triggers, Stored Procedures, Sequences, and so on. • OS Scripts for Oracle database administration, monitoring, alerts and so on. • Data Load, Extract SQLs, Validation SQLs and so on. • Job Scheduling Scripts • Database Security Configuration • Database Failover, High Availability, Replication configurations The goal is to capture as much critical information as possible that is required for an equivalent operation of the database in the target environment/platform. These activities can be automated using a combination of: • SSMA - SQL Server 2005 Migration Analyzer tool • ORACLE 10G Administration tools • The following are some of the statistics computed by the Migration Analyzer over all database objects in the source Oracle database. The statistics include: • Total number of

database objects such as procedures, functions, triggers, tables, indexes, packages, sequences, and views. • Total number of code lines, including per-object statistics. • Total number of SELECT, INSERT, DELETE, and UPDATE statements. • Total number of CURSORs, RECORDs, and EXCEPTIONs. • Total number and percentage of automatically convertible components. • Estimated complexity of conversion. • 2. Prepare/Define Target Metadata In this step the detailed Metadata is created for the target SQL Server 2005 platform. This will include (not limited to) defining the appropriate information based on the requirements and analysis of the source metadata such as: • Hardware configuration for the target SQL Server 2005 database • Operating System details relevant to SQL Server 2005 database • SQL Server 2005 Database configuration parameters • SQL Server 2005 Database and Schema objects based on analysis of existing Oracle 10G database • Database

Administration related scripts and modules • Data Load and extraction routines • Job Scheduling scripts • High Availability Configurations Executing this step ensures that the target database platform will be in a position to support the existing functionality without any disruption of services. The Architecture of the target Database Platform can be created using the SQL Server 2005 Database Management tools or a default database configuration can be achieved using the SQL Server 2005 Migration Workbench. 3. Apply Mappings and Translations The Schema & Data Migrator toll from (SSMA) will be used for mapping and translation of Oracle schema to SQL Server database schema. The schema is the basis for defining the target database. In migrating from Oracle to SQL Server, the new schema can be derived in a fairly straightforward manner from the original schema. Because of feature differences between the two platforms, the mapping between the schemas may not be one to

one. An example of where this arises is in dealing with long-valued attributes such as BLOBs. Oracle supports multiple BLOB-valued attributes per table while SQL Server supports only one per table. In this case, a single table in Oracle may map to multiple tables in SQL Server Because of such differences, migrating the data between databases may not be a simple table copy, because the new table structures may be somewhat different from the old ones. The Schema & Data Migrator (SSMA) automatically converts an Oracle database schema (tables, constraints, indexes) to a Microsoft SQL Server schema and physically migrates the data to populate the new database. Both source (ORACLE 10G) and target (SQL Server 2005) metadata information is compared and mapping is generated for similar objects and translation rules are identified/applied, if there is no one-to-one direct mapping. There are ORACLE 10G objects which may not be migrated directly and some manual intervention or translation is

required for their migration. ORACLE 10G objects like Stored Procedures, Data Load Scripts, and Administration scripts and alerts, High availability and replication, and Job Scheduling scripts and so on, need some extra attention to be migrated successfully. Executing this step ensures that there are no gaps between the source and target database platforms and the new database does not cause any issues in the migrated environment. The Mappings and Translations can be created using SQL Server 2005 SSMA tool and with the help of ORACLE 10G and SQL Server 2005 Administration tools. Business Logic Conversion SQL, Stored Procedures, Functions, Triggers, Package, System functions, cursors, exception handling and transaction management are some of the business logic components that need to be converted from Oracle to equivalent SQL Server implementation. Schema & Data Migrator and SQL Converter will offer functionality for business logic objects migration. The complexity of a database

from a migration perspective is a function of the number of business logic objects it contains, the size of those objects, and the particular language features used to implement them. Additional complexity derives from differences in the system function libraries provided in the two platforms. For example, Oracle’s stored procedure language, PL/SQL, has many features not supported in Transact-SQL (T-SQL), the stored procedure language of SQL Server, and even where similar features exist, the languages contain many syntactic and semantic differences. To migrate a PL/SQL object that uses a feature that has no T-SQL counterpart requires that that capability be emulated in T-SQL using the available features. Developing a good emulation can be quite subtle and time consuming. In fact many subtleties need to be addressed in the migration whether developing emulations for missing features or accounting for semantic differences in corresponding features. 4. Generate Target Database

Scripts/Modules This step is carried out once the source (ORACLE 10G) and target (SQL Server 2005) environments have been analyzed and mappings/translations have been created and verified. In this step the actual database scripts and/or modules are developed or generated from tools and a release build package is created for the target database (SQL Server 2005). This is in a deployable form and is thoroughly tested and validated in a test environment. This release package consists of scripts for all schema objects, configuration files, data load scripts, administration scripts, user security profiles, archival and backup modules and so on. The scripts and modules are created based on target Operating system, SQL Server 2005 Home directory and appropriate paths. The goal of this step is to have a validated build script which can be used as a package for deployment in the target database environment. The scripts/modules can be built using SQL Server Migration Assistant - SSMA, SQL Server

2005 Administration tools depending on the type of objects. 5. Deploy Target Objects In this step, the actual database environment is created from the Deployment Release package built in previous stages. This step is carried out once the appropriate Hardware and Operating system have been established and infrastructure is ready to support the target SQL Server 2005 database. The objects are created by executing the Scripts and Modules in the proper order to maintain dependencies. The deployment process is either centralized or distributed depending on the requirements and the objects are propagated accordingly. Connectivity is established between various servers and client machines. The High-availability and replication options are deployed based on various sites and terms of usage. Once the deployment is complete, the database platform is ready for use. 6. Verify Target Database Platform This is the final step of the Database Platform Migration approach. Once all SQL Server 2005

objects are deployed into the target environment the instances are verified from an operational point of view. Connectivity is tested and all components are validated The goal of this step is to ensure availability of target database platform for all consumer applications and prepare for Data Migration. The verification process can be automated using SQL Server 2005 Administration tools and some test scripts can be executed to validate the deployment. Migration Tester tool from SSMA provides automated validation of the migration process. 2.3 Data Migration Technical Approach Based on our experience we have seen that majority of data can be migrated using an automated tool like Schema & Data Migrator of SSMA. But from time to time we have encountered complex situations where export/import or Load/Unload utilities from ORACLE 10G and SQL Server 2005 have been utilized for data migration. In some other situations where ongoing data migration is needed a third party ETL tool like

SSIS and DataStage are also considered. These tools can greatly reduce the development time compared to PL/SQL and can handle heavy data loads and complex transformations. The following figure gives a high-level view of the steps for data migration from source ORACLE 10G to target SQL Server 2005. Source Data Analysis & Profiling Target Data Model Analysis Data Mapping & Transformation Rules Source / Existing Database (Oracle) Data Migration Routines Target Database (SQL Server ) Data Validation & Database tuning Data Migration Methodology As part of the Data Migration approach the following major steps are carried out. 1. Source Data Analysis This step is for performing detailed analysis of the data sources involved in the data migration process. This includes: • Study of existing ORACLE 10G data models to fully understand the entities, relationships, constraints, rules, data types, data formats, and so on. • Understand source data for as part of migration

analysis. The goal of this step is to have a comprehensive understanding of the data that is in the scope of migration and derive a complete strategy for migration. 2. Target Database/Schema Analysis This step is to analyze the Target Database/Schemas where the data will be migrated to. This includes: • Understanding of the entities, relationships, data types, data formats and so on, of the target data model in SQL Server 2005 • Analyzing the database platform and reviewing differences from the source ORACLE 10G platform, which might create issues during data migration • Understanding if there are any changes to the data model due to change in platforms – such as data types, data formats, partitions and so on. The goal of this step is have a good understanding of the target SQL Server 2005 database and schemas involved in data migration and assist in developing the migration strategy. The Database/Schemas Analysis can be automated using SQL Server 2005 Administration

tools and reports can be published for review. 3. Data Mapping and Transformation definition This step is for defining the data mapping between the source ORACLE 10G and target SQL Server 2005 database/schemas. This includes: • Defining Data Element mapping between ORACLE 10G and SQL Server 2005 database/schemas • Defining the transformation rules based on requirements. The rule can be stored in repository with details of their usage • Verifying the constraints and ensuring they are taken care of either by the database or by the data migration routines The goal of this step is to fully understand the source and target data elements and mapping between them. This understanding is used to define the migration routines to avoid any gaps in data in the migrated environment. The Schema & Data Migrator tool will be used for generating data mapping and transformation definition. 4. Data Migration Routines (Extract, Validate, Transform, and Load) This step is for defining and

building the Data Migration routines that will actually move the data from source to target database/schemas. This includes: • Building Data Extraction routines based on source Data analysis and data mapping documents • Applying Data Quality rules (if applicable) and build modules accordingly • Building Data Transformation routines based on data mapping and transformation rules document • Building Data Load routines based on target analysis and data mapping documents • Developing Batch Schedules based on dependencies and on going need (if applicable) • Building migration audit and notification routines to track, monitor and verify the data migration processes • Building Error handling and Re-start routines to handle exceptions during the data migrations process The goal of this step is build the data migration routines based on the analysis and mapping work done in previous steps. This is critical to ensure the success of the overall data migration

effort. Schema & Data Migrator tool from SSMA will help with the data migration 5. Data Validation This step is to perform the validation of the migrated data to ensure that correct data is available to the applications and reports. This includes: • Performing Data Validation checks on data that has been loaded • Running Application process on the migrated data to ensure validity of data • Running reports over the migrated data and matching with existing reports to ensure accuracy of data • Verifying the data load logs and audits to make sure nothing was dropped during the migration The goal of this step is to ensure successful data migration for all databases/schemas in scope. The Zebra Visualization Tool from SSMA extends the GUI to address the need for testing as well as to enable the user to follow the automated migration process. After the PL/SQL code has been converted, this tool allows the user to easily browse through the code and to find the corresponding

code structures