Day 23 - Becoming a Database Administrator

By: Jaryd Remillard (@KarateDBA)
Edited by: Benjamin Marsteau (@bmarsteau)

Database is a term that is thrown around in meetings amongst all industries. The term is almost always used with a sense of urgency and importance yet contains a vast mystery. It can be a topic that some may feel too confident, one with absolute no knowledge and one that refers to a single copy of a glorified excel spreadsheet sitting on their desktop. In my short time as a database administrator, I have found that it is typically the confident ones that venture into this mystery with the full understanding of the business value and risk that come as the database administrator. Like any area of science, technology, engineering, and math, acronyms are favored, so let it be known that the title of database administrator can be abbreviated as DBA.

Like any career path, one database administrator path will not necessarily align with the direction you have to take. It is not to discredit the value of the journey and course someone purposely took or perhaps accidentally stumbled into; there are specific points to remember which in itself could present an opportunity in your journey. Instead, be aware, just like theoretically there is an infinite number of ways of solving a problem with the code, there is an endless number of directions to reach your destination of becoming a DBA. All in all, I hope the reflection I have done of my journey I took to become a database administrator will set you up for success.

Start with the basics

When I was 12 years old, I befriended a stranger online through a collective group of people who played an online video game. We were idling in our TeamSpeak server when I asked them what they were up too, they replied saying they were coding a website for our group. The concept immediately struck me with curiosity like a static shock, the idea of how to construct a website was so far-fetched I just had to learn so I could quench the burning desire. I naively asked if it is a drag and drop type of process. They laughed and began to talk about and teach me HTML and showed me how to view the source of a website. The concept blew my mind; words typed in a specific manner can be translated into a structure that is displayed on my screen. It made me feel like anything was possible. I kept building websites with HTML, leveling up to using CSS, JavaScript, learning Linux, and eventually PHP. Soon after, I was building login systems, registration systems, user profiles, all in a LAMP stack that required knowledge of basic SQL, learning simple DML's, DDL's, DCL and TCL's, I wrote whatever worked. The experience and newfound knowledge I bashed together eventually turned into a charming but underwhelming social network that I named Express-It. Building the schemas in phpMyAdmin was accessible in the sense of, "I create a column, PHP writes to it, there we go.” However, as the social network grew to a whopping 100 people, which were primarily friends and family for moral support, it caused my website to slow to a crawl. What I did not understand was the more extensive technical specifics of ints, unsigned bigints, varchars, indexing, and primary keys and how various people at the same time querying similar things while the SQL scanned the entire database affected performance. I could not wrap my head around it, nor did I think there was anything but what the query was because it did the job locally. Frankly, it also didn't occur to me that my schemas and queries were DBA's nightmare. I shut down Express-It since my curiosity shifted from LAMP stacks to learning cybersecurity, doing basic IT jobs for friends and family, and I was sick of the free hosting tier I was using.

School of Hard Knocks

As I shifted my focus from building to fracturing, SQL came up in the form of learning its flaws. From the various types of SQL injection, brute force, and DoSing a database. My knowledge expanded to be more aware of possible vulnerabilities and the importance of a database, including losing data. This experience exemplified when the code I had from my Express-It website and hundreds of hours of various other projects I stored on a flash drive, as an interim between moving homes, was accidentally reformatted while transferring some photos by a family member. Losing all my work taught me how easy it can be for a large part of my life to disappear. I then realized the hard way that backups are a thing and I became hyper-aware. I learned to keep at least two to three copies of whatever had importance on separate data stores, I learned that flash drive and hard drives could die without warning or get overwritten accidentally, you can never have enough backups, and corruption is a thing. My motto became "backup backup backup, correctly.” I often chuckle when reflecting this time of my life because it reminds me of high school, where any time a big paper was due, someone always had the excuse that their file was gone, overwritten, or became corrupted the night before, conveniently, perhaps honestly so. I could not help but blurt out of my smart mouth, "Should have backed up.” Unfortunately, the lesson I learned came back to haunt me in a different form.

Venturing Further and Beyond!

I went on a hiatus from technology for a bit to focus on school and sports. Eventually, when my interest in technology came back, an internship opportunity landed on my lap, which still to this day I attribute to luck as the the news of an opportunity was shared to me by the CTO of the relevant company. Before starting, I was asked what I wanted to do during the internship, specifically, what direction of my career did I want to go. I thought it was software engineering; at the time, building and designing were shiny to my eyes. However, I was conflicted as I still enjoyed living in the terminal, something about the rawness of text on a blank background with specific commands can be utilized to effectively navigate the computer in ways a GUI could not. It still drew me in even when I was deep in an IDE, I had moments of barbarianism when I would code in vim. I knew programming was not what I wanted to do for a full eight hours a day, I was conflicted and I shared my concerns. They mentioned DevOps, and it was perfect. I would get the complete balance of being in the terminal and writing code, I then embarked on the start of my career. As an intern, a lot of my tasks were simple; data entry, set up a local environment, break the local environment, finish some tickets, attend standup, and the like. But one task stood out to me, the need for an internal tool to show the difference between a system at one point in time compared to another time, such as permissions and data in the file, essentially a beefy diff. Like most ideas, it was a task that seemed easy at first but was exponentially more complicated than initially anticipated. As I dug into the task, the first tool I chose was Python to program in as it seemed easy to learn and it was all the rage. As I learned more about Python's data types, I naively figured it was an excellent function to cache all files on a system in a dictionary, which unbeknownst to me beforehand, resulted in Python running out of memory. After consulting some of the engineers nearby on how to navigate this issue, it was recommended to use a database. So naturally, I chose Sqlite3. I moved to MySQL pretty soon after, sqlite3 was just not working out. I figured MySQL was perfect as it is a solid relational database, I would have the freedom to specify what kind of data to store and it made storing md5 checksums. I was eventually able to get the program to work to some degree of success but not without experiencing bottlenecks. Previously in my past, the amount of data I worked with was so little that there was little to no need for optimization. So when caching the majority file information on a system, well, that is when I started to see performance impacts on the database, particularly the length of time in executing the program and overall high usage of memory in both primary and secondary. I figured the best direction to tackle this problem would be to take it to a deeper level, learning of the internals of MySQL. Basics of how the client and server work together and elementary query optimization, but with limited guidance, there was only so much I could dig on my own. Eventually, time continued, and I moved to a local company as a system administrator. My new job exposed me to different types of databases; MongoDB and SQL Server, along with MySQL again. I spent a lot of my time at my new position on the front end and web servers like Apache, Jekyll, GruntJS workflows as well as Active Directory, I still got to see the back end as well. Naturally, it fascinated me more, in between tasks I learned how it was accessed by services and as an administrator, how to view the permissions of users, and how to query what you wanted. Questions about the front end were easy to answer but the back end had a lot of unanswered questions I could not find the answer too, various topics such as; internal functionalities, maximum capabilities, dynamically manage a user, etc. Databases remained a mystery I wanted to solve and I was ready to go Sherlock. I read the documentation and tinkered with the databases, and then I would go home to set one up for myself, just to see how I can break it. Unfortunately, my time became more consumed with school and the front end of a job at the time, although I knew I wanted to get back to the databases in the future. Soon, an opportunity opened to work as a student employee in the IT department at my university. This potential new position would save me an hour commute to school as well as an hour commute to work, although it was not nearly as technical as what I was currently doing and perhaps a step back; it was the right decision for me at the time. I had a feeling this job could turn into something more technical than what I was already doing.

Uh-oh

After completing three years of college and almost a year as a student employee, I was offered a full-time job in the IT department of the university I was attending. It was a decision that was difficult to choose and took some time to weigh the pros and cons; to take the risk and leap of faith into the field or continue education for another two years while piling debt to then flow into the field. Ultimately, I chose to take the job to pay off the student loans that I accrued, which was almost the size of my salary and nearly twice my weight in stress. Also, I knew this was an opportunity to delve deeper into different technologies and learn what it means to take ownership and responsibilities. The job was to be the system administrator for the STEM department, with that indeed came with a lot of responsibilities of managing various software, some cloud-based but many on premises-based. Much of the software I managed used a SQL Server to manage logins, logs, barcode numbers, etc. Little did I know that an SQL Server was actively in use until I got a call from a chemistry department head saying they are unable to log into their science rental equipment management software. I searched all over in our wiki and could not find a single trace of this existence, for a moment I thought this was a prank. I asked my coworkers if they had heard of this software, if it even exists; I got nothing in response. I dug further, even so far as reaching out to our previous system engineer that worked prior. It turns out this software had an SQL Server sitting in an undocumented virtual machine, lost in tribal knowledge. Unfortunately, there were no records of this software ever being provisioned. To look on the bright side, one of the science teacher's users in this database for some reason had super privileges, giving me the ability to login and work some magic, thinking this was the end of the immediate problem. But there was an itch in my brain, questions that stuck with me; what had happened? Why did it all of a sudden stop working? Why is it sitting on a VM undocumented? How can I prevent this from the future? Why is there no accountability and visibility with this database? It was going to be forgotten in this state, or I had to work on keeping this reliable in all aspects, especially documentation. I made a page on everything I learned about this software, including representatives from the company, run books for the database, and how the client and backend work. At that moment this is when my interest in reliability and uptime exponentially grew, especially around databases. Before I left, I made one big oops.

Be wary of drives

I was put in charge of the psychology department on top of the STEM department. I had a psychology professor come in as her laptop was due for a replacement and was hoping to speed up the process as it was filling up a special order of a 512 GB Dell XPS primarily, which consisted of personal photos and research documentation. The first process was to back up her laptop to a hard drive we had using some software that did it block by block. I had our student employees complete this process overnight. I woke up to some great news; it kept failing with odd errors that warranted no response via a Google search. After some consulting with my coworkers, Office 365 comes with a 1 TB storage via OneDrive. We thought this was perfect; she can store all her valuable documents into OneDrive as we set up her new laptop and download it back down. She preferred that I did it personally as I was in charge of the psychology department, as our policy was, I had to agree. I began the process of uploading her documents to her OneDrive and it took days. Being new to Office 365, I had no idea why it was taking this long, but I shrugged it off as it eventually reported successful. I began to download her files onto the new computer and started the RMA of her old one. Problems were immediate; permission issues, too long file names, disappearing files, you name it. After hours and hours of work, going through shadow copies of our servers, looking at past backups we had, recursively changing permissions of the files, it was exhaustive. I was able to obtain about 95% of what she had previously, but the 5% I lost was a good chunk of her research. It was a time of reflection where my motto rang in my head non-stop, I missed one more backup somewhere. From then on, I was no longer super aware. I was hyper-aware and vigilant in storing data. Everything made me skeptical or ask questions, and it was a mark in my career of growth through failure. I had a burning desire in me to learn more about storing data; how do it robustly, safely, ensure validity and integrity. I set out to fulfill my desire.

Where to begin?

Finding information on reliability and internals of the data storage is a difficult task when you do not have any reference or expert to guide you towards the correct path. The internet is filled with how-to's; doing write and read queries, but understanding documentation on internal works is tricky to begin, let alone comprehend. I have finally started to dip my toes in and quickly learned it's difficult to give a summary of the paradox that is an SQL database, the simplicity of the query structure itself is one that provides a façade and false sense of understanding. Rather, there is much behind the scenes you cannot see. Knowing how to query to get what I needed from a database gave me the confidence that I knew what to do and how it worked. It wasn't until I started a personal project that was causing the database to suffocate that it made me realize that perhaps there is more to databases than my cute knowledge previously thought.

Personal Projects

Being in the technology field will expose you to various situations that are hard to prepare for in personal studies as well as higher education. Outages that are unpredictable due to customer behavior or merely no reference to what the threshold is of a service, primarily because you never reach that point. Scaling up is a term that I heard of but never understand, so natural curiosity decided I needed to seek out what it really means. It is impossible to scale up unless you have a lot of data to utilize. Finding large data sets that contained false and made-up data was a tall task, so then I had an itch to create a data generator to assist in learning how to scale. Yes, there are a few data generator websites. However, they seem to cap out of a million rows at the time, which is not enough for me and the service to really push it to the limits. In creating this data generator, I made it so it can spit it out in an SQL format, making it easy to slap into MySQL right away. Fortunately, it is capable of generating 8 figure rows of data with columns for names, addresses, cars, age, and other data after some heavy work in Python. I ran my data generators several more times to add up to 300 million rows, I decided it was time to load up a MySQL server in a LAMP stack with this data to use in a simulation of what would be a essentially a country sized simulation. With no visibility of the VM, OS, and the database, my PHP queries to MySQL locally took ages or crashed the VM altogether. I knew it was the database because even querying via phpMyAdmin was not returning results quickly or timed out, and I couldn't figure out how to better interact with the database. Thinking it lacked in power, I kept upping the CPU's and RAM which only led to crashing the host. I stepped back to think more about scaling; how could I, in this case, scale up if upping power wasn't the solution? Then the concept of how a CPU is designed rang in my head, distributing the job into smaller chunks. A saying from the CTO of the company that I interned told me, "Any big problem is just a subset of a bunch of smaller problems. Iterate those small problems, and now you've solved the big problem."

I got it! Let me split the database into smaller sized databases, each containing a max of 10 million rows. If I needed something beyond the unique ID, I could query the next database instead of having MySQL scan the entire database. Distributing data through multiple instances of MySQL servers was a weak solution in this case, of course, as PHP now had to maintain 20 MySQL connections. Later I learned this moved the problem instead of solving it, and now I was stuck. I understood databases are complex at the time and are much more complicated than I initially thought and that fed my desire to learn more. I did not necessarily feel capable of being a database administrator, but I figured what is better than to in headfirst as a database administrator for a company.

I am a person that tries to not be afraid to delve into the unknown or face rejection. Imposter syndrome is real, but I know it is something you can grow past despite your thoughts no matter what your mind tells you otherwise. I scoured the internet for DBA jobs and found myself stumbling upon an entry-level DBA posting at the competitor of the company I interned at. It was perfect and I applied despite it feeling like a moonshoot as the position was based in a different state.

Don't be afraid

Unexpectedly, I got a callback. I flew through the phone interview, manager interview, and eventually hopped on a call for the technical interviews. I was as honest as I could be, I explained my attempts to scale, shared the little experience I had with MySQL, and why I wanted to be a DBA.

Simply put as to why I wanted to be a DBA, databases are facinating to me. We rely on databases for everything, but hardly anyone delves more in-depth than simple restarts or querying for what they need. I had a difficult time finding resources to help me learn deeper about SQL rather than how to write basic SQL queries, I was hungry; rather, I was famished to learn. I knew I lacked a lot of knowledge and was honest about it during my technical interviews but I backed it with what I was trying to do with MySQL. In particular, I shared my attempts of scaling by distributing the workload, having no idea what the correct term was other than using the description of distributing "it." I later learned it's called sharding. I jumped up and down after finding out the correct term as it had unlocked a vast amount of new resources via Google searches and technical conversations with people in the industry. During my technical inverview, I had a DBA on call, this was the perfect opportunity to ask what resource I should read so I jumped in as soon as I could. She recommended reading the Database Reliability Engineering book by Charity Majors and Laine Campbell. I Immediately bought it off Amazon, practically during the interview, and was extremely eager to crack it open the second it arrived. I started reading and taking impeccable notes, absorbing as much as I can.

This is the direction I needed, the direction I wanted to go, to push my mind, widen my thought process, making me aware that there is much more than writing code and setting up software such as; service level objectives/agreements, automating, the need for metrics and the alike. I just could not put the book down. It almost felt like I hadn't had a bite to eat in days essentially swallowing the book. Upon my second technical interview, I believe my famine showed. I talked about what I was learning and how I was applying it, and it raised the interviewer's eyebrow in a good way. I was flown to their headquarters for further interviewing.

Still much to learn

It is no secret this job was at SendGrid, and I am very fortunate to have found a job posting that was purposely looking to help the employee to grow. I attribute a lot of that to luck and the excellent mentality and awareness of the benefits of hiring and raising a junior employee at SendGrid. The distinctive culture included hunger, the hunger to learn, and I was viciously starving. I could not stop reading documentation, asking questions and writing everything down in a spiral notebook. I am fortunate to have a senior DBA on the team to guide me through processes of replication and basic troubleshooting of a MySQL server. Later I bought The High Performance MySQL: Optimizations, Backups and Replication book on Amazon, and soon after being hired, I started going through the book, diligently taking notes and asking questions along the way. The path to learning about SQL did not stop when I was hired; in fact, it just started.

Conclusion

Overall, my natural-born curiosity and love for challenges lead me to take an opportunity where no one else dared to venture. I broke my façade, thinking SQL databases are easy because I can query something by trying to force the database to kneel. Finding why was challenging, but that only led me viciously seek out a solution, not be afraid to apply for a DBA job. The key was realizing I always gravitated and asked myself the most questions when dealing with a database, I wanted to conquer databases. The two books mentioned are a great start to grow your knowledge beyond querying a database, but to delve deeper into what it is and how to use it. Another book to look at the Celko's Advanced SQL Programming by Joe Celko's, it does a good job of delving into how SQL works behind the scenes and make you realize that your queries can be optimized greatly. While there are many paths to take, the real take away is if you have the hunger to learn, you will succeed no matter what path you take.