Overview
A typical engagement starts with a business that has run SAP ASE, formerly Sybase, behind an order and stock system for many years. A vendor quote has called the move to PostgreSQL mostly automatic, and the team wants an independent view of what that means for their own code before it commits.
Challenge
Conversion tools move tables and a share of the code. The effort sits in stored procedures that return result sets straight to the caller, transaction control inside procedure bodies, triggers that use the inserted and deleted tables, and behaviour that depends on the server's sort order. Change capture has limits too: AWS DMS, for example, reads one ASE database per change-capture task and cannot use a warm standby as its source.
Environment
Several SAP ASE servers in the customer's data center, hundreds of Transact-SQL stored procedures and triggers, a Replication Server warm standby, nightly dump jobs, and applications in more than one language that connect through different drivers.
Approach
AceMQ engineers take a read-only inventory of servers, databases, objects, replication definitions and clients, and use ASE's monitoring tables over a representative period to find which procedures are still called. Schema and code go through a conversion tool on a copy, and every item the tool flags is classified by the hand work it needs. A data movement path is chosen against the downtime budget, and rehearsals are planned before cutover.
Solution
- Inventory of servers, databases, tables, procedures, triggers, user-defined types, replication definitions, jobs and connecting clients
- List of procedures never called in the observation period, proposed for retirement instead of conversion
- Trial conversion of schema and code on a copy, with each flagged item classified by effort
- Hot-spot list: result-set procedures, transaction control, trigger logic, legacy outer joins, temporary table scope and sort-order dependence
- Comparison of full load and change-capture routes against the downtime budget and the limits of the tools
- Rehearsal plan with row-count and checksum validation, report comparison and a rollback that keeps ASE read-only for an agreed window
Outcome
The engagement delivers the inventory, a retirement list, a classified conversion hot-spot list, a recommended data path, an order of databases and a rehearsal and cutover plan. The customer then decides whether to move, with AceMQ's help or without it, and ASE support can continue until the last database has moved.
Technologies
PostgreSQLAWS Schema Conversion Tool
Linux
Kubernetes
dbt
Kafka
Docker
