Moving from Sybase, SQL Server, DB2 or MySQL to Oracle, or from Oracle to PostgreSQL, changes more than where the data lives. The schema, the stored procedures, the data types and the way applications talk to the database all have to be converted. We plan the move, automate as much of the conversion as we can, keep the old and new databases in step while you test, and validate the data table by table before you switch.
Assessment
Every engine has its own dialect. We inventory the tables, views, stored procedures, triggers, functions and jobs, run them through conversion tools to see how much converts cleanly, and estimate the code that needs rewriting by hand. We also look at what the tools cannot see: the queries built inside the applications, the reports, the batch jobs and the other systems that read from or write to the database. The result is a plan with the effort, the risks and a recommended order of work.
Conversion
We convert the schema and code with tools such as AWS DMS Schema Conversion and Oracle SQL Developer's migration support, then finish by hand the parts they cannot handle. The usual traps are known in advance: Oracle treats an empty string as NULL, PostgreSQL folds unquoted names to lower case where Oracle folds them to upper case, date and time types differ in range and precision, and T-SQL, PL/SQL and PL/pgSQL handle transactions and errors differently. Each converted procedure is tested against the original with the same inputs.
Data movement
For a small database with a weekend to spare, a bulk load may be enough. For anything the business relies on all day, we load the new database and then replicate changes from the old one continuously, with Oracle GoldenGate or AWS DMS depending on the engines, so the two stay in step while you test. That is how an e-discovery company moved from Sybase to Oracle with zero downtime and no disruption to its clients' work.
Validation
Data that has crossed engines has to be checked, not assumed. We compare row counts and checksums table by table, check the values where types were converted (dates, decimals and character sets), run your reports on both databases and compare the results. Performance is tested too, because a query that was fast on the old engine can need a new index or a rewrite on the new one.
Common moves
Heterogeneous migrations tend to follow a few patterns. Sybase and DB2 databases move to Oracle when the business wants to standardize on it. Oracle databases move to PostgreSQL when the business wants an open-source engine. Several engines are consolidated onto one to simplify support. Any of these can be combined with a move to a managed cloud service such as Amazon RDS, but doing both at once adds risk, so we often recommend two steps.
Case studies
Client names are withheld. We are happy to share details on a call.
DeliveredZero downtime
Legal technology
Sybase was holding back a platform that processes large volumes of legal data. We converted the schema, kept both databases in sync with continuous replication, validated the data and switched over without interrupting the business.
Zero downtime at cut-overNo disruption to client workflowsQuestions
It depends on the code. Tables, indexes and simple views usually convert cleanly; stored procedures, triggers and engine-specific features need more work. The assessment runs your own schema through the conversion tools, so the estimate is based on your code rather than on a rule of thumb.
Usually some do. Connection strings and drivers change, and SQL written inside the application may use features of the old engine. We list each change for your developers, help them make it, and test the applications against the new database before the switch.
Yes, and for anything important we recommend it. Continuous replication keeps the new database current while you test it with real data, and the switch happens only when the results are right.
We compare row counts and checksums table by table, check the values where data types were converted, and run your own reports on both databases. You get the results in writing before you decide to switch.
It can be done, but each change is a source of risk, so we often recommend two steps: change the engine first, or move the database as it is first, depending on which brings the business more benefit sooner. The assessment sets out both options.
From the blog
March 7, 2023
February 27, 2023
November 16, 2022
Related services
Move to the cloud, build for it
Trusted data, reports and analytics agents
Support, security and monitoring from one MSP
On-site IT support across New Jersey, New York City and Philadelphia. Remote services wherever you are. Areas we serve →
We run your schema through the conversion tools and come back with the effort, the risks and a cut-over approach, before you commit to anything.
A few lines is enough. The engineers who do this work will reply within one business day.
Or email it.services@princetonits.com or call +1 (212) 507-9350.
Princeton IT Services