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.
My line of business is entirely a Microsoft shop so everything we’ve ever written has been for MSSQL.
That being said, I can understand the benefits of having a choice in backend. For example, for our Zabbix deployment some engineer just installed mariadb+zabbix on a server and called it a day. This has caused us no end of troubles (ibdata misconfigured, undo files too small, etc). After the last time I had to rebuild it due to undo file corruption I swore that if it broke again I was switching to postgres. So far knocks on wood we haven’t had any major issues. We’re still looking into and planning for a postgres migration but we’re hoping to hold out for a little longer prep time.
Maybe I should contribute a MSSQL engine for Zabbix so I can move it to a platform I’m more comfortable with. ;)