“Has this ever happened to you…?”
Circa 2006, I was in charge of managing some critical FTP infrastructure and uploads. Renaming the uploaded files from our format to the client’s preferred naming conventions was originally a 2 hour per day job; and then the data quintupled in a short period of time, making doing it manually untenable. I ended up automating this using a batch file, so a 10 hour a day job now took 30 seconds. I didn’t tell anyone about this, and used my spare time to help my team and supervisor with their workload. When the boss found out, his question for me was, “Do you know anything about databases?” My reply: “Sure!” Him: “Come with me.”
This is how I became an Accidental Database Administrator. An Accidental Database Administrator (DBA) is defined as someone who inherits or is suddenly in charge of a database. Over the course of my career, I’ve found myself in this position many times, and have learned valuable insights and important lessons from both research, and hands-on.
SQL Skills offers a 30 day guide for the Accidental DBA. This is by no means exhaustive (it only focuses on MSSQL, doesn’t touch on ETL or cloud processes, etc). This is not my first time going through this, but I thought I’d take the time to write and share what I pick up from it this time around. Although it’s a little dated now (referencing SQL Server 2012), the fundamentals are still there. So time for a refresher!
This came up as a result of an accidental database shrink command (thankfully, not my fault — this time). Although no production servers were harmed in the making of this mistake, I wanted to follow up on it and ensure I was aware of what to do in case issues did arise. For example, having to turn off backups to successfully perform the shrink and other considerations.
If you or someone you love has been affected by database memory issues or slow execution plans, please don’t hesitate to reach out to us — we’re here to help!
Here’s the first few days of Accidental DBA distilled for you. I recommend going through it as a good heads-first look into managing DBs… but it’s only the surface!
Day 1: Know your hardware and licensing! Since 2012 SQL licensing is per-core, affecting chips with higher core counts, as well as older dual-core processors which still require up to 4 licenses.
Day 2: RAID for performance, not size – It’s better to use many small drives to get maximum disk I/O, instead of a few large drives.
Day 3: SSDs – Traditional HDDs are okay for sequential read/write, but for random access SSDs are better. If you have OLTP (online transaction processing), tempdbs or logfiles, then SSD will be better. (Note: SSDs can suffer from write degradation, especially higher density QLP drives, so use caution!)
Day 4: Install Considerations – per-service accounts (Windows or SQL) instead of ‘sa’ for everything, Power Management, RAID caching, Windows Instant File Init, Lock Pages in Memory, and Cumulative updates.
Bonus: SQL Reports to find resource usage, including query execution times, expensive queries, top connections in addition to resource usage. Simply Right Click the DB in question > Reports > Standard Reports > Performance Dashboard, Memory Consumption, Top Queries, Expensive Queries, Top Connections. Thanks to LiquidWeb article for this.