Working Without A SQL Server DBA? Do These 10 Things Now

Have you seen the job boards recently? Hiring an experienced full-time DBA is expensive. And many companies simply cannot justify that kind of spend for a full time DBA, especially if they have fewer than a couple of dozen instances of SQL Servers in production. The result? Many companies are working without a SQL Server DBA.

That’s is a scary place to be.

Sure, SQL Server is resilient and works well.

Until it doesn’t.

Then what? It’s up to a sysadmin or senior developer to try to wrangle life back into the SQL Server or bring the database back online. Not a fun task. Especially when senior management is looking over your shoulder and users are losing patience.

So what should you do if you find yourself working without a SQL Server DBA?

10 Things to do now if you’re working without a SQL Server DBA

SQL Server DBAs do a lot of things. Many of them go largely go unnoticed when things are going well. But that doesn’t mean what they are doing isn’t important. In fact, it’s because of what they are doing that things are going well. The servers are available, response times are good, and users are happy.

So, if you find your self working without a DBA, what should you do? Let’s take this in stages.

First, make sure everything is working as you think it is

Everything seems to be working, right? You hope so. But, of course, hope is not a good strategy. And no news is not always good news. So, if you’re working without a SQL Server DBA, let’s first make sure everything really is working as you think it is.

1. Get a list of all your production and lower-level SQL Servers

Start by getting a list of all of the production servers in your environment. If you have a few, or even a few dozen, SQL Servers this sounds like a pretty straightforward exercise. However, there may be some surprises out there in the deep recesses of your environment. Sometimes people leave and take with them the awareness of key assets, including SQL Servers.

Look for production servers that somehow have dropped off the radar. Are there development SQL Servers that now have a limited role in production? What about those instances dedicated to HR?

Get a complete list. Document their use as best you can. Include the development, QA, test, and UAT instances, as well.

2. Make sure you’re back ups are working

Being able to restore a database to the right point in time, in a reasonable amount of time, is one of the core responsibilities of a DBA. Make sure your SQL Server backups are occurring regularly. Check the SQL Agent jobs to ensure they are scheduled, enabled, and are completing successfully. Ensure all databases in Full Recovery Mode have transaction log backups. Don’t just take image snapshots.

If you don’t have regularly scheduled full, differential, and transaction log backups, fix that.

3. Check the integrity of your databases

Corruption is an insidious thing. It can go unnoticed for days or even weeks, and then, all of a sudden, it becomes a problem. The longer corruption goes unnoticed, the more difficult and intensive it is to recover from.

Backup and restores don’t fix database corruption. If a database was corrupt when the backup was taken, the corruption will be present when the database is restored from the backup.

So, next you’ll want to run integrity checks on your databases. Verify that the databases are in good shape. This is a resource intensive process so you’ll want to run the integrity checks during periods of lower activity, such as nights or weekends.

See When Was the Last Known Good DBCC CHECKDB Integrity Check? for more information.

4. Determine your SQL Server version and patch levels

Not every company can live on the cutting edge. There are a quite a few instances of SQL Server 2017, 2016, 2014, 2012, etc., still in production. And, yes, I know a daring few are still running SQL Server 2000.

There are a ton of reasons to upgrade. You know that. Improved security, better performance, enhanced reliability, and the list goes on. But upgrading is not always an option.

So, it’s important to know what version you are running. At least you’ll know which servers are at more risk.

Using the list you compiled in Step 1, determine what version and patch level each SQL instance is running. See What SQL Server Version Am I Running? for more information.

Second, help keep your SQL Server working

Now you have a list of all your SQL Servers. You know that they don’t have hidden corruption and are being backed up. Now, it’s time to look for ways to keep your SQL Servers working well.

5. Configure notifications and alerts

We all know bad things can happen. Backups can fail. Writes to disk can get corrupted. Unexpected issues can arise. Some of these may be immediately obvious, but others may not. So it’s important for SQL Server to be able to tell us when something starts to go downhill.

Make sure Database Mail is configured for each of your SQL Servers. Create operators with email addresses that go to distribution lists so multiple people or systems get notified when a job fails or potential corruption is noticed.

Setup the failsafe operator.

6. Create maintenance jobs

The developers at Microsoft have done a really good job in making sure SQL Server works, even when ignored. However, with a little TLC, it can work much better.

So, create maintenance jobs to periodically check for corruption in your database. Use scheduled jobs to proactively maintain indexes and to backup all system and user databases regularly.

We recommend using Ola Hallengren’s SQL Server Backup, Integrity Check, and Index and Statistics Maintenance scripts. It’s robust, easy to use, and completely free of charge. In our experience, it’s far superior to SQL Server’s maintenance plans.

7. Apply cumulative updates

Bring your SQL Server instance up to date on cumulative updates. Work through the list created in step 1, applying the most recent updates to the SQL instance – first in test, then in production. Although it’s not often that a CU causes an issue, it is possible. So, avoid applying an update directly to production, if possible. Test first.

Third, get proactive with your SQL environment

Now that you’ve ensured backups are happening, gotten some maintenance tasks scheduled, and configured some basic alerts and notifications, it’s time to get a bit more proactive with your environment.

8. Monitor your SQL Server

If a SQL Server is really critical, you don’t want to rely on the basic alerts and notifications you set up in step 5. Rather you’ll want to invest in a monitoring tool that lives outside of SQL Server to keep an eye it.

SolarWind’s SQLSentry and Database Performance Analyzer (DPA) and Redgate’s SQL Monitor tools are some of the more popular monitoring tools out there.

Most have a free trial period. Take advantage of it. Keep in mind that you’ll need to configure the monitoring software. Avoid alert fatigue by tuning the alerts to what’s important in your environment.

Spend some time learning the monitoring software. Don’t allow it to become shelfware.

9. Assess the SQL Server’s configuration

Take a deeper dive on each of your SQL Servers to examine their configuration. Compare their settings to industry best practices.

Look at default configurations such as settings for MaxDOP and Cost Threshold for Parallelism. Look at database file placement, the number of tempdb files, and who has sysadmin access, to name but a few.

For more information about assessing your SQL Servers, see Is My SQL Server Configured Properly?

10. Regularly test your backups

Database backups are important, but only if they give us the capability to restore when needed. To ensure that you can actually do that, it’s important to periodically test your backups by restoring to a test environment.

Create a tickler on your calendar to remind you to test them periodically. Every two weeks? Monthly? Maybe quarterly? It depends on your environment.

Better yet, create an automated way to do this on a scheduled basis. Don’t forget to check the integrity of the restored database.

See How Often Should I Test My SQL Server Backups? and How to Test SQL Server Backups Using dbatools for more information.

Keep going. Don’t stop here

This list is not comprehensive. It’s not a do-these-10-things-and-that’s-it kind of list. There are certainly many other things you could and should do if you’re working without a SQL Server DBA. But this is a good start.

Here are a few other things to consider to help channel your inner-DBA.

1. Recovery Point Objective. Determine your Recovery Point Objective (RPO) and ensure your current backup/recovery plan supports it.
2. Recovery Time Objective. Determine your Recovery Time Objective (RTO) and ensure your current recovery plan is well-documented and supports it.
3. Disaster Recovery. Determine and document your disaster recovery processes. Test and refine those processes regularly.
4. High Availability. How much does it cost your organization if SQL Server is down for a hour? For a day? For a week? What can be done to prevent that from happening? Would implementing a Failover Clustered Instance help? Do you need an Availability Group? See What’s the Difference in SQL Server FCIs and AGs?
5. Capacity Planning. Create a roadmap to ensure your SQL environment will support the growth initiatives of your company.
6. Daily Health Checks. Create a process or a system to check on the health of your critical SQL Server systems daily to ensure they are healthy and performing well. Review the SQL Server logs, the Windows logs, the SQL Agent logs, etc.
7. Plan Ahead. How are you going to support your SQL Servers when something bad happens? How will you equip your team so they can resolve the issue? What resources will they need?
8. Review security. Data breaches and ransomware attacks are a big, albeit illegal, business. Review your SQL environment as part of your broader security initiatives.

This is what we do

We bring reliability, performance, and security to companies that traditionally haven’t been able to justify an experienced SQL Server DBA on their payroll. It’s called SEROShield. We manage, monitor, and protect SQL Servers for our clients for a predictable monthly investment. It’s SQL Server management – simplified.

Want to learn more about how SERO Group helps organizations manage their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.