I started software development journey from Oct 2000 in the US (my LinkedIn profile here), for about 8 years my main focus was the C++ CAD software development, and I didn’t use much Database or SQL in my day job. I did take a graduate level database course when I was in Rolla in year 1999 or 2000.
I started to use database, SQL and Oracle PL/SQL for my day job from 2010, when I started working for a small consulting company that does work for the Union Pacific Railroad. The application I worked on uses quite a bit of Oracle PL/SQL partially because during the time of development (around 2000 to 2003), PL/SQL was the “new and hot”, and also Oracle itself was the mainstay in the enterprise application/database development platform too. Later on at Mastercard I learned the Master Reward System (or platform) used PL/SQL extensively too, as one of my colleagues put it, it’s really the heart of the system. This is also confirmed by one of the former developers’ LinkedIn profile (John McGuinness).
- Lead developer and technical owner of back end of MasterCard Rewards Loyalty System.
- Used C++, Korn Shell, PL/SQL, and Java in a Sun Solaris Environment.
Btw, once in 2005, I recall my “formerly IT/software engineer” mortgage broker and I had a quick talk after the closing of the house (condo to precise), and I recall he was doing VB (Visual Basic) before his mortgage career, and we tend to agree “software development using database as backend” is funner. Green is always greener at neighbor’s yard. I think it applies in the software development field as well 🙂
My own DB experiences – Working in progress as of 01-14-2024
Below are some of my observations on the relational DB in various production environments across multiple applications.
Unidev (contractor for Union Pacific Railroad): year 2010, when I 1st touched the DB, Oracle SQL and PL/SQL, C++.
Unigroup (contractor): IBM DB2.
Arch Coal (now Arch Resources): I used SQL server, it’s a small app in which I created both backend (C# .NET web services), and front end (iOS on iPad).
Btw, for own mobile apps, iOS apps, I used parse and google firebase (no longer active maintained).
Mercy: I used both Oracle and MySQL (we have different apps using different databases for backend).
Mastercard: Oracle (the main backbone for the MRS, see below), and Postgres (a small database for the Pay with Rewards app), this is the most substantial app I ever worked on – Mastercard reward system (MRS), Exadata: which is the high performance Oracle/Sun Sparc dedicate DB hardware. At MC I had opportunity to work on some performances issues on our SQL, both in the web application and the batch application, including promotion.
I also touched upon the Oracle Advanced Queues for the Pay with Rewards app I mentioned above. Overall I gained most of my DB exposure when I was working at MC. Before working at MC, I think I consider myself still a newbie on the DB (I was more confident on the java, and web apps dev side of things), after the MC gigs I was much more confident on both the DB as well as web admins (Site Reliability Engineering, using Google’s term).
A very subtle yet interesting observation: at MC they have teams such as software engineering (dev team), business operation (Technical/Production Support + Site Reliability Engineering), Database Engineering (DBA team) etc. At my current place we have dev team, Dev Tools team (somewhat like Dev Ops), dedicated Production Support team, and Data Management team (DBA team). Overall from my experience, only the MRS at MC has a dedicated application DBA. Most places don’t have a DBA with in-depth DB knowledge.
Ascension: MySQL on GCP, GCP big query
Enterprise Fleet Management (current employer): Oracle and Postgres (no production yet), some issues around DB (frequent locking, and concurrent DB connection exceeding alert limit, this happened at MRS too).
When the system hangs due to the exhaustion of database connections (basically DB server got overloaded), DBA’s usual workaround is killing the blocking session. In the extreme cases, they have to restart the DB server – but this could cause outage if there is no secondary DB server.
DBA stands for database administrator (see Oracle’s DBA definition).
Application DBA is a DBA that has knowledge on the application database, in other words, he/she may be familiar with the data model in the database, and sometimes could provide valuable input to the performance and optimization of the database design, and database queries.
Outage
To avoid outage, we could have a primary and secondary database and database servers. Basically during normal operation, we use the primary DB server. Keep in mind the data is sync’ed (one way) from the primary to the secondary all the time (sometimes this is called data replication on Oracle). In the event, we need to take the primary DB server down, we can start up the secondary server 1st, switch the application DB connection to the secondary server, and this way we avoid an outage. When the primary DB server is available again, we can cut the application traffic back, keep in mind, when we switch back and forth, we need to make sure the data is kept up to date: for example, the case of switching back to the primary server, we need to replicate the data from secondary server to the primary server 1st. There are existing software solutions for that, e.g., Oracle GoldenGate is a popular choice for Oracle DB replication.
Web app <=> [ Primary server ] => secondary server
Some tips:
Always backup before deleting.
In addition to relational DBs, we need to be aware of object datastores (sometimes people called NoSQLs) such as MongoDB.
We can do Entity Relational (ER) model using MySQL Workbench for the WordPress MySQL DB – I think the Data Modeler role at MC (and other large organizations) may used similar tools to check the data modeling. It seems many small organizations don’t have this capability. The ER model is a very good way to introduce new developers to the system.
Advanced Topics