SQL Server Migration Planning Project Workbook

Dear Viewers,

Please use below url to access the SQL Server migration project planner.

Downloadable URL: 26Feb2025_SQLServer_Migration_Project_Planner

Google Workbook Contents and Sections:

Section I: Executive Summary & Project Goals

Project Name
Project Sponsor/Stakeholder
Business Drivers for Migration
Migration Goals & Objectives
Target Environment (High-Level)
Expected Migration Timeline
Key Success Metrics
Project Team & Key Contacts

Section II: Source Environment Inventory & Assessment - A. Source Server Infrastructure

Source Server Name(s)
Server Physical/Virtual
Operating System - Version & Edition
Operating System - Service Pack Level
CPU Details - Number of Cores
CPU Details - Processor Type
Memory (RAM)
Storage Configuration - Disk Layout
Storage Configuration - Storage Type
Storage Configuration - Free Disk Space
Network Configuration - Network Interface Cards (NICs)
Network Configuration - Network Bandwidth
Network Configuration - Firewall Rules
Backup & Recovery Strategy - Backup Types & Frequency
Backup & Recovery Strategy - Backup Retention Policy
Backup & Recovery Strategy - Disaster Recovery (DR) Setup
Security Configuration - Authentication Mode
Security Configuration - Auditing Enabled
Security Configuration - Compliance Requirements

Section II: Source Environment Inventory & Assessment - B. Source SQL Server Instance(s)

SQL Server - Service Pack & Cumulative Update Level
SQL Server Collation
SQL Server Features Enabled
Maximum Server Memory Configuration
Number of Databases Hosted
TempDB Configuration - File Locations
TempDB Configuration - Number of Files
TempDB Configuration - Size
TempDB Configuration - Autogrowth Settings
Linked Servers
SQL Server Agent Jobs - Number
SQL Server Agent Jobs - Types
Custom Server Configuration
Performance Metrics (Current Baseline) - CPU Utilization (Average)
Performance Metrics (Current Baseline) - CPU Utilization (Peak)
Performance Metrics (Current Baseline) - Memory Utilization (Average)
Performance Metrics (Current Baseline) - Memory Utilization (Peak)
Performance Metrics (Current Baseline) - Disk I/O (Average)
Performance Metrics (Current Baseline) - Disk I/O (Peak)
Performance Metrics (Current Baseline) - Wait Statistics (Top Wait Types)
Performance Metrics (Current Baseline) - Query Performance (Slowest Queries)

Section II: Source Environment Inventory & Assessment - C. Source Databases Inventory & Assessment

Log File Locations
Filegroups - Number
Filegroups - Purpose
Database Objects Inventory - Tables
Database Objects Inventory - Views
Database Objects Inventory - Stored Procedures
Database Objects Inventory - Functions
Database Objects Inventory - Triggers
Database Objects Inventory - Indexes
Database Objects Inventory - Users & Roles
Database Dependencies
Data Sensitivity & Classification
Archiving & Purging Requirements
Database Usage Patterns - Peak Usage Times
Database Usage Patterns - Transaction Volume
Database Usage Patterns - Read/Write Ratio
Database Usage Patterns - Key Tables (Heavily Accessed)
Database Maintenance Plans
Backup History & Integrity
Database Features Used - Change Data Capture (CDC)
Database Features Used - Change Tracking
Database Features Used - Temporal Tables
Database Features Used - In-Memory OLTP
Database Features Used - Stretch Database
Database Features Used - Database Mirroring
Database Features Used - Replication as Publisher/Subscriber
Database Features Used - Always On Availability Group as Secondary
Database Features Used - Contained Databases

Section III: Application Inventory & Dependencies

Database Name (for Application Info)
Application Name
Application Owners/Contacts
Application Connection Strings & Methods
Application Connection String Details
Application Compatibility with Target SQL Server Version
Application Downtime Tolerance
Application Performance Requirements (Post-Migration)
Application Testing Plan
Third-Party Application Dependencies

Section IV: Target Environment Details & Considerations

Target Platform Selection Justification
Target Server Infrastructure Details (if applicable)
Target SQL Server Version & Edition
Target SQL Server Configuration
Migration Tools & Technologies Planned
Migration Approach & Strategy
Downtime Window & Cutover Plan
Rollback Plan
Validation & Testing Plan (Post-Migration)
Post-Migration Support & Monitoring Plan
Licensing & Cost Considerations
Security Considerations in Target Environment
Disaster Recovery & High Availability in Target Environment

Section V: Migration Risks & Mitigation Strategies

Risk - Potential Migration Risk
Risk - Risk Assessment & Prioritization
Risk - Mitigation Strategies
Risk - Contingency Plans

Section VI: Sign-offs and Approvals

Prepared By
Prepared Date
Reviewed By (DBA Team)
Review Date (DBA Team)
Approved By (Project Sponsor/Stakeholder)
Approval Date (Project Sponsor/Stakeholder)
Approved By (Application Owners)
Approval Date (Application Owners)
Approved By (Security Team)
Approval Date (Security Team)

Oracle external table to read the pre-processed file output from a os process monitoring command and perform aggregation or analytics to get more intelligence

Reference: https://asktom.oracle.com/Misc/oramag/preprocess-external-tables.html

YouTube:

Step1) Ensure you have the file which we wanted to process using oracle external table is ready already

[oracle@vcentos79-oracle-sa1 OSpsstat]$ pwd /home/oracle/dba/OSpsstat [oracle@vcentos79-oracle-sa1 OSpsstat]$ ls -altr ... -rw-r--r--.  1 oracle oinstall  24526 Feb 13 23:03 tmp_st4_02 -rw-r--r--.  1 oracle oinstall  73604 Feb 13 23:03 processed_ps2.out drwxr-xr-x.  2 oracle oinstall   4096 Feb 14 00:02 . -rw-r--r--.  1 oracle oinstall    160 Feb 14 00:02 OSPSW_20515.bad -rw-r--r--.  1 oracle oinstall  19293 Feb 14 00:07 OSPSW_20515.log [oracle@vcentos79-oracle-sa1 OSpsstat]$ wc -l processed_ps2.out 752 processed_ps2.out [oracle@vcentos79-oracle-sa1 OSpsstat]$

Step2) Frame external table command from the output of the file present in os

Example:

create table indata1

(

    cust_id     number,

    cust_name    varchar2(20),

    credit_limit number(10)

)

organization external

(

    type oracle_loader

    default directory etl_dir

    access parameters

    (

       records delimited by newline

       fields terminated by ","

    )

location ('indata1.txt')

)

/

Actual:

create directory ospsw_data as '/home/oracle/dba/OSpsstat';

create table ospsw

(

sampltm date,

osusr varchar2(50),

ospid number,

osppid number,

prprty number,

pcpu varchar2(10),

pmem varchar2(10),

vsz number,

rss number,

wchain varchar2(50),

processstat varchar2(3),

processstart varchar2(20),

processdur varchar2(20),

processcmd varchar2(1000)

)

organization external

(

    type oracle_loader

    default directory ospsw_data

    access parameters

    (

       records delimited by newline

       fields terminated by ","

       (

        sampltm date "dd/mon/yyyy hh24:mi:ss",

        osusr char(50),

        ospid char(10),

        osppid char(10),

        prprty char(10),

        pcpu char(20),

        pmem char(20),

        vsz char(20),

        rss char(20),

        wchain char(50),

        processstat char(3),

        processstart char(20),

        processdur char(20),

        processcmd char(1000)

       )

    )

location ('processed_ps2.out')

)

/

Output:

SQL> drop table ospsw; Table dropped. SQL> create table ospsw ( sampltm date, osusr varchar2(50), ospid number, osppid number, prprty number, pcpu varchar2(10), pmem varchar2(10), vsz number, rss number, wchain varchar2(50), processstat varchar2(3), processstart varchar2(20), processdur varchar2(20), processcmd varchar2(1000) ) organization external (     type oracle_loader     default directory ospsw_data     access parameters     (        records delimited by newline        fields terminated by ","        (         sampltm date "dd/mon/yyyy hh24:mi:ss",         osusr char(50),         ospid char(10),         osppid char(10),         prprty char(10),   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32          pcpu char(20),         pmem char(20),         vsz char(20),         rss char(20),         wchain char(50),         processstat char(3),         processstart char(20),         processdur char(20),         processcmd char(1000)        )     ) location ('processed_ps2.out') ) / 33   34   35   36   37   38   39   40   41   42   43   44   45 Table created. SQL> select count(1) from ospsw;   COUNT(1) ----------        752 SQL>

step 3) Start querying the data and start to perform analytical/aggregation functions

SQL> select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1; SAMPLTM             ,SUM(TO_NUMBER(PCPU)),SUM(TO_NUMBER(PMEM)),  SUM(VSZ) --------------------,--------------------,--------------------,---------- 13/FEB/2025 22:44:42,                  .3,                 3.2,   7884632 13/FEB/2025 22:45:12,                  .3,                 3.2,   7884632 13/FEB/2025 22:45:42,                  .3,                 3.2,   7884632 13/FEB/2025 22:46:12,                  .3,                 3.2,   7884632 SQL>

Errors to be aware:

SQL> create table ospsw ( sampltm date, osusr varchar2(50), ospid number, osppid number, prprty number, pcpu number(5,4), pmem number(5,4), vsz number, rss number, wchain varchar2(50),   2    3    4    5    6    7    8    9   10   11   12   13  processstat varchar2(3), processstart varchar2(20), processdur varchar2(20), processcmd varchar2(1000) ) organization external (     type oracle_loader  14   15   16   17   18   19   20   21      default directory ospsw_data     access parameters     (        records delimited by newline  22   23   24   25         fields terminated by ","        (  26   27          sampltm date "dd/mon/yyyy hh24:mi:ss",  28          osusr char(50),         ospid number,  29   30          osppid number,  31          prprty number,         pcpu char(10),         pmem char(10),         vsz number,         rss number,  32   33   34   35   36          wchain char(50),         processstat char(3),         processstart char(20),         processdur char(20),         processcmd char(1000)        )     ) location ('processed_ps2.out') ) / 37   38   39   40   41   42   43   44   45 Table created. SQL> select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1; select sampltm,sum(to_number(pcpu)),sum(to_number(pmem)),sum(vsz) from ospsw group by sampltm order by 1 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "identifier": expecting one of: "binary_double, binary_float, comma, char, convert_error, date, defaultif, decimal, double, float, integer, (, lls, lls_compat, no, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned" KUP-01008: the bad identifier was: number KUP-01007: at line 6 column 15

This is because of usage of number data type in the access delimiter definition which isn’t accepted by oracle it seems.