This is something I have thought a lot recently since I recently saw a project that absolute didn’t care in the slightest about this and used many vendor specific features of MS SQL all over the place which had many advantages in terms of performance optimizations.
Basically everyone always advises you to write your backend so generically with technologies like ODBC, JDBC, Hibernate, … and never use anything vendor specific like stored procedures, vendor specific datatypes or meta queries with the argument being that you can later switch your DBMS without much hassle.
I really wonder if this actually happens in the real world with production Software or if this is just some advice that makes sense on surface level but in reality never pans out. I personally haven’t seen any large piece of Software switch to a different DBMS, even if there would be long term advantages of doing so, because the risk and work to retest everything would be far too great.
The only examples I know of (like SAP) were really part of a much larger rewrite or update rather than “just” switching DBMS.
Oh yes! Yes yes yes. I worked at a company where the DB admins were tasked with moving away from OracleDB to MariaDB and are now moving to PostgreSQL. They have thus been migrating for about 20 years now. It is a huge expense with multiple teams across the business dedicated to this sole task.
One would think the devs learned from the first migration not to write DB specific queries, but alas… To be fair though, managers also pushed for quickly written code and hired a lot of junior engineers straight out of uni. The turnover rate was amazing back then (1-2 years max) and probably stayed about the same or got worse.
At another company, the client used some old Microsoft database on-prem, wanted to migrate to PostgreSQL, but had trouble with their datacenter (couldn’t scale IIRC) and had to migrate to the cloud first. Of course the only vendor of the old version of that Microsoft DB is Azure and by the time their migration to the cloud was complete, the DB was not officially supported anymore. So now, they are paying out of their ass for premium support as, IINM, they are among the last clients still running that version of the DB.
The entire code base is handwritten SQL in the flavor of microsoft’s DB which is of course incompatible with anything else.
Their migration started close to a decade ago and by the time I left the end was nowhere in sight.
So yeah, staying DB agnostic is not a joke.
CC BY-NC-SA 4.0