Migrating Between Proprietary and Open Source Databases

1 .Migrating between Proprietary and Open Source Databases Calculating your ROI Wednesday, May 29, 2019 

2 .Agenda ● About my employer and I ● A simple statement about database Migration ● My experiences in database Migration ● Some specific things to consider in modern database migration ● That simple statement again © The Pythian Group Inc., 2019 2018 2 

3 . PYTHIAN A global IT company that helps businesses leverage disruptive technologies to better compete. Our services and software solutions unleash the power of cloud, data and analytics to drive better business outcomes for our clients. Our 20 years in data, commitment to hiring the best talent, and our deep technical and business expertise allow us to meet our promise of using technology to deliver the best outcomes faster. © The Pythian Group Inc., 2019 2018 3 

4 . 20 Years in Business 400+ Pythian Experts 350+ Current Clients in 35 Countries Globally © 2017 Pythian. Confidential 4 

5 .DIVERSIFIED, BLUE CHIP CLIENT BASE Media/Information Financial Services Retail E-commerce SaaS Services © The Pythian Group Inc., 2019 2018 5 

6 .About me 45 years of working with data Started with flat files on Tape drives Over the years have worked with: ISAM, Codasyl Network databases, IBM IMS, Tandem SQL/MP IBM DB2, Sybase ASE, Informix, MySQL, Cassandra, MongoDB PostgreSQL and many others Helped to migrate thousands of databases at AOL from Sybase to MySQL Today I travel around the world helping Pythian’s customers get the best return on their database investments. © The Pythian Group Inc., 2019 2018 6 

7 .About me Part II The fun stuff: I like to travel I love animals, Nature, National parks, Museums, Zoos and Gardens I take a lot of pictures of flowers and landscapes. They are patient subjects I take very few pictures of people. They are almost never patient subjects © The Pythian Group Inc., 2019 2018 7 

8 .A Simple Opening Statement ● Database migrations are usually hard work ● SQL is a language with many dialects ● Database servers are each designed with different design philosophies ● Operational tooling around each database varies in maturity and functionality between proprietary and open source ● Documentation is often badly lacking in the open source community ● So… ● Look for an easy out ● Or Consider a complete re-write ● Or…. Well migrate © The Pythian Group Inc., 2019 2018 8 

9 .The Easy out ● We had a customer with 100+ SQL Server replicated clusters ● They asked us to help them migrate to an Open Source Database to save on license costs ● We did an initial evaluation and offered them two options ● 1 – Spend 2 years and about 1 Million $ to migrate to PostgreSQL or MySQL ● 2 – Create 10 up sized server clusters and migrate all of the 100+ databases to one for a few thousand $ from us to do the work. Then re-negotiate with Microsoft for the new smaller footprint. ● They took choice two. © The Pythian Group Inc., 2019 2018 9 

10 .Some ancient history ● 15 years ago AOL having adopted Linux as their operating system of choice decided to pick an Open Source database ● We picked MySQL to replace approximately 30 thousand Sybase Server licenses ● We had a proxy we had developed with its own SQL dialect and it looked like a fairly simple task of implementing a MySQL version then putting it into place. ● We Overlooked Stored procedures. ● Stored Procedures in MySQL were a new thing then and we had something like 15 thousand Sybase Stored procs ● It didn’t go well for many reasons. ● At the same time…. © The Pythian Group Inc., 2019 2018 10 

11 .Ancient history continued ● All of our code at that point was pretty monolithic and written in C/C++ ● Most of our new hires had mostly Java backgrounds and management was buying into the service oriented development story. ● Time to market for new features was of course the big feature. ● So… We re-wrote about 300 applications into a few thousand services ● Replacing all those Sybase databases with MySQL. ● How long did it take? Six years but the results were pretty good. © The Pythian Group Inc., 2019 2018 11 

12 .Over the last Five years ● I have worked with several customers who decided to migrate from either Oracle or SQL Server to an open source database. ● The happiest ones by far are the ones who decided do to a complete re-write in the process. ● The ones doing migration have ended up in two categories: ● 1 – Fairly simple database with simple queries and no or few stored procedures ● 2 – Large database, lots of tables, lots of queries, stored procs and specialized functions. ● Group 1 completed their migrations ● Group 2 are still working on theirs or have given up. © The Pythian Group Inc., 2019 2018 12 

13 .So how do you do a migration ● Make sure you really want to go down this road ● Analyze your schema, queries, STPs etc ● Use SQLines (general purpose), Commercial package, AWS DMS/SCT, Ora2pg (oracle to PG only) ● Work out a strategy to convert your schema and queries based on the analysis. ● Convert your schema and queries and test them against a test database ● Chang your applications if needed, to meet missing features in the new database technology ● Copy your database from the old proprietary to the open source one. This will be a point in time logical backup and reload. ● If you need a zero downtime migration then use a tool like Goldengate or Continuent tungsten replicator to bring the new DB up to date. © The Pythian Group Inc., 2019 2018 13 

14 .What are your your costs and savings ● Cost ● Staff time to do the analysis and conversion ● Tools to help with the migration Golden Gate, Tungsten proprietary conversion tool etc. ● Lost opportunity cost while you are doing the conversion and not implementing new features ● Training on the new Database and tooling ● Increased risk ● Savings ● License cost © The Pythian Group Inc., 2019 2018 14 

15 .So lets be honest this is probably about Oracle ● Oracle has some significant common features with PostgreSQL ● But… There are some major things you need should probably know ● Stored Procedures STPs ● PostgreSQL stored procedures aren’t Stored Procedures they are functions. May seem picky point but… ● PostgreSQL STPs are not pre compiled when created. They are parsed and query planned at first use on each new connection. ● PostgreSQL STPs are never really compiled hence they are significantly slower than Oracle STPs almost always. ● Numeric ● Oracle and PostgreSQL both have the numeric type which allows an large number of significant digits © The Pythian Group Inc., 2019 2018 15 

16 .So lets be honest this is probably about Oracle ● Numeric ● Oracle is smart about how numeric is interpreted. If a numeric is defined less than 10 digits it gets defined internally as a 4 byte integer. ● PostgreSQL always stores numeric fields as BCD value. So far as I know only IBM Z series systems have “hardware” instructions to handle BCD. Otherwise it gets processed in software. ● Operational tools. ● PostgreSQL operational tools are separate from the core server. Maintained by different groups in the community. Often poorly documented. ● Example – Until PostgreSQL 9 replication was only available externally. Until PostgreSQL 10 only binary replication between the same versions of PostgreSQL could be used. © The Pythian Group Inc., 2019 2018 16 

17 .So lets be honest this is probably about Oracle ● Partitioned tables ● In PostgreSQL 8 partitioned tables were introduced. The mechanism was elegant and also kind of um… Stupid. ● In PostgreSQL 10 partitioned tables were re-introduced the right way and very similar to the way they have worked in Oracle forever. ● Use the new way. ● Vacuum ● Oracle and PostgreSQL both are MVC engines but their implementations are very different. ● PostgreSQL uses a leave in place algorithm which requires cleanup using a tool called vacuum. It is important to understand how it works and its impact on operations before the database explodes or shuts down. See: https://www.percona.com/blog/2018/08/06/basic- understanding-bloat-vacuum-postgresql-mvcc/ © The Pythian Group Inc., 2019 2018 17 

18 .So lets be honest this is probably about Oracle ● Connections ● PostgreSQL was developed before threads were invented. ● It still does not use them. ● Instead when a new connection request is made from a client a new process is forked. ● You probably don’t want more than a few hundred connections to a single PostgreSQL instance. ● Connection pooling tools most commonly pgBouncer are required to allow for thousands of client connections. © The Pythian Group Inc., 2019 2018 18 

19 .And what about the cloud? ● If you are going to migrate from Oracle are you going to the cloud? ● You probably are: ● Do you want to deploy on base infrastructure or are you looking for DbaaS? ● Amazon you get RDS PostgreSQL, MySQL and others as well as Aurora flavors of PostgreSQL and MySQL ● GCP offers CloudSQL with some fairly severe limitations including replication in and out limitations ● Azure Database for PostgreSQL and MySQL ● All the DbaaS offerings offer built-in replication/HA and automatic backups all at the push of a button or two. ● There are limits on some features of PostgreSQL including which extensions you can install on all three cloud vendor DbaaS offerings. © The Pythian Group Inc., 2019 2018 19 

20 .A Simple Closing Statement ● Database migrations are usually hard work ● SQL is a language with many dialects ● Database servers are each designed with different design philosophies ● Operational tooling around each database varies in maturity and functionality between proprietary and open source ● Documentation is often badly lacking in the open source community ● So… ● Look for an easy out ● Or Consider a complete re-write ● Or…. Well migrate © The Pythian Group Inc., 2019 2018 20 

21 .Thank You John Schulz Schulz@pythian.com © The Pythian Group Inc., 2018 21 

22 .© 2017 Pythian. Confidential 22