Every once in a while I get a question from either clients or from folks who’ve attended one of my presentations about just exactly what it is that DBAs should be doing day-to-day.
As I intimated at in my previous post on the importance of clearly documenting responsibilities and expectations, the reality is that this answer is really going to depend a ton upon your organization, your title, your environment, and a host of other factors.
That said, I think the following list of 10 tasks can serve as a semi-decent overview of many of the things DBAs typically do on a daily, weekly, monthly, and yearly basis.
1. Checking on Servers
Whether you're responsible for the hardware or not, there are plenty of things you'll want to do to check up on servers on a day to day basis. Checking logs and keeping tabs on the SQL Server Agent are exactly the kinds of things I'm talking about here:
- DBAs and the SQL Server Agent
- DBAs and SQL Server Logs
- DBAs and Windows Event Logs (not always accessible to you as a DBA)
In some environments as a DBA you may not have enough time to review details for every server every day. If so, set up a 'rotation' schedule where you make sure to keep looking at your most important (most mission critical) servers daily and then 'cycle' through semi-daily or weekly reviews of non-essential servers based on priority, etc.
Performance tuning and throughput will typically be part and parcel to your world as a DBA. Without performance baselines and other throughput benchmarks to compare current activity against, you'll quickly find that you're running blind. Happily, baselines are pretty easy to set up. (Though you'll likely also want to look at a host of different other ways to get a sense for workloads and throughput on your servers.)
3. Testing and Validating Backups AND the Ability to Restore Them
In my estimation, absolutely everything you do (short of security) takes a distant second place behind this concern. If you're not regularly testing your backups to make sure you can restore them (under a variety of different scenarios or 'simulated emergencies') then you’re not building the skills necessary to respond to a disaster and running the risk that a change somewhere within your environment has ruined your ability to either get your backups working OR allow users/applications to see backups should they end up being deployed to another host, data center, whatever. Moreover, regular testing helps you verify that you're staying within your SLAs by helping ensure that you're able to meet RPOs and RTOs.
4. Performance Analysis and Tuning
With baselines and a sense of what your current workloads are, you'll be able to spot when and where problems crop up. From there, you’ll typically spend a decent amount of your time as a DBA tuning those operations (and working 'gingerly' against production data in some cases) to consume fewer resources. As a DBA performance isn't exactly your primary concern—scalability is. And scalability (or the ability to handle more load) is enabled when you don't have unruly queries and operations capitalizing hardware resources on your server. Tuning and tweaking and ensuring proper indexes and the likes is part and parcel to what you do as a DBA in almost all environments.
5. Coordinating Data Pushes and Changes
Depending upon your role, your environment, and your data you might be periodically helping to push application changes and upgrades (with corresponding tweaks to schema and code) or regularly ensuring that 'data pushes' from staging to production and the likes are operating efficiently.
6. Patch Management
This is something you'll commonly need to schedule, address, and coordinate as well.
7. Code Reviews and Interactions with Developers
Personally, this was always one of my favorite things to do as a DBA—to help developers 'improve their craft,' better understand SQL Server and internals, and reinforce the idea that we're all working on the same team (even if our goals are much different; DBAs tend/trend towards security and safety and the status quo, while devs typically are focused on whole-scale change, massive changes, and 'violent revolutions' because technical advances in their fields of endeavor frequently provide massive benefits with complete and wholesale changes—something that typically doesn’t mesh to well with years' worth of 'entrenched' and mission-critical data and a HOST of dependent operations throughout your organization).
To address differences of opinion and focus between devs, DBAs, and management. Sadly, many DBAs spend too much time in meetings.
9. Capital Improvements and Initiatives
Throughout the year you’ll probably be involved in various initiatives or capital improvements—like making one of your key databases/servers more highly-available by throwing in Clustering, Mirroring, or AlwaysOn Availabililty Groups into the mix—or improving backups, bolstering security, and so on. These on-going projects, commonly, 'ebb and flow' but can consume a large amount of your time during the year and on a day-to-day basis—at times of the year.
Finally, while you will have some long days and even longer nights as a DBA, there will likely be times during the year when you could probably, realistically, get everything you truly need to do done in about 3-4 hours per day on-premises at work. Only, while management will patently expect that you're simply available and on-call late nights when problems happen or when code changes are being pushed or for longer hours when working on various 'initiatives,' I typically don't run into many organizations that let their DBAs (or other IT folks/developers) have any of that time 'back' when things start to get slow. Meaning, you’ll likely have points during the year when things get slow and where you can easily get 'your job' done in just part of a day but you'll still be 'expected' to be on-hand for the remainder of the day. At this point, you could fill your time chatting around the water cooler, ruining other people's lives by spinning up useless meetings, or looking at pictures of cats on the Internet.
Personally, I always used time like this for self-improvement—for my own initiatives—whether it was learning something new (like Powershell or SSRS or C# or whatever) or 'finally' optimizing my defrag routines or coming up with a cool way to automate such and such, or whatever. Doing so definitely made the time pass faster, and provided much better benefits than trite pictures of cats or discussions about how various sports teams did ever could.