Five Goals for Your SQL Servers in the New Year

We’ve made it! The start of a new year. Now is a great time to reflect on what you’d like to accomplish over the next 12 months at work, and more specifically, what you would like to do with your SQL Server environment this year. To help with that, here are five activities or goals to think about for your SQL Servers over the next 12 months. Take these as a starting point and make them your own. 

Goals for your SQL Servers

Although every environment is unique and each business has its own set of important measures for success, here are five goals for your SQL Servers to consider as you plan the coming months. These goals are designed to help build a robust and responsive data infrastructure to support your organization.

1. Assess your SQL Servers
2. Update your SQL Servers
3. Start performing health checks
4. Implement monitoring and alerting 
5. Review your Disaster Recovery plans

Goal 1: Assess your SQL Servers

Whether you’re responsible for one SQL Server sitting in a server room next to you or hundreds of SQL Servers spread around the world, it can be difficult to keep up with the exact configuration of each SQL Server. This is especially true if you’re time is split. For example, when you’re a network administrator, web developer, or systems admin who is also tasked with taking care of the SQL Servers in your spare time.

Uncertainty about the patch level, configuration, and health of each or your SQL Servers creates stress and unease. Doubts creep in.

Do any of these internal dialogs sound familiar? 

• Are the SQL Servers behind on patching? Probably, I’m not sure, but they are working, right? (See Is There an Update for My SQL Server?)
• Are the SQL Servers configured optimally? Probably not. I’ve been meaning to do some research and check. But at least no one is complaining right now. And no new is good news? (See Is My SQL Server Configured Properly?)
• Are the databases healthy? I remember setting up some maintenance jobs a while back, I think. If they weren’t healthy, I’m pretty sure I’d know. 
• We pay a maintenance fee to the application vender. Does that include applying SQL Server updates? I’m not sure.
• Ok, I really should have a look, but ugh, when? 

Now is that time. The new year brings with it an opportunity to examine each of your production SQL Servers to see if they are configured optimally.

So what should you check?

Examine the log files, the configuration settings, the maintenance jobs, the performance DMVs, and patch level of each of your critical servers. Do you have the right number of tempdb files? Do you have any unneeded services running? Are there an excessive number of waits being reported? Are you still using the default values for MAXDOP and Cost Threshold for Parallelism? Etc?

Look for areas of concern, and there will likely be some, and prioritize resolving them. 

Not sure where to start? We can help – Getting Started with a SQL Server Assessment.

Goal 2: Update your SQL Servers 

Once you’ve assessed your SQL Servers, you know your starting point. You know the overall health of each instance. Next, make a plan to address or remediate the instances as needed. 

Start with the most important servers first and attack the Priority 1 issues as soon as you can. The P1 issues are those items that are of grave concern due to security and data integrity, recoverability, and availability. 

“As soon as you can” will mean different things to different organizations. Some environments allow minor changes and patches directly to production servers during off hours. For example, want to apply the latest CU? “Sure. Anytime after 7:00 PM Thursday will be fine.” 

Other businesses have a more standardized process in place. Make a remediation plan; confirm with application venders; present the plan to the Change Control Committee for review, deconflicting, and sequencing; apply the changes to a lower level environment; confirm with the QA team that the changes didn’t introduce ill effects; add the patch to the list for the next quarterly patching window, etc. 

Whatever your approach, get the process started. 

Goal 3: Start performing health checks

You’ve assessed and remediated your SQL Servers. Now, let’s ensure that the environment remains up to date and in good health. Make a plan to check on your SQL Servers regularly.

We recommend Daily Health Checks for your SQL Servers. (That’s what we do for our DBA as a Service customers.)

Create a checklist to review each day. Some things we like to check are: 

• Review the SQL Server and Windows log files for errors, unexpected restarts, excessive failed login attempts, and other warnings that could indicate potential issues.
• Check the history for each scheduled SQL Agent job to ensure none have failed. 
• Examine the size and growth of each data and log file to check for anomalies in growth. 
• Verify that each SQL Server is up to date on patches.

Goal 4: Implement monitoring and alerting 

Proactively checking the status of each of your SQL Servers is helpful. But there may be occasions in between the health checks when the SQL Server experiences difficulties. When that happens, it’s really nice to know that before your users and your boss becomes aware. 

It’s best to identify and resolve an issue before anyone else notices. However, the next best thing is to be able to answer a call with “Yeah, I know and I’m working on it,” rather than “Really? When did this start? Tell me the server again.” 

To get to that point, you’ll want to ensure that all SQL Servers are configured with Database Mail, and that standard alerts are created. We recommend creating alerts for all errors with a Severity level 16 or above, along with errors 823, 824, and 825. There may be other alerts you’ll want to create in your environment as well. 

Additionally you’ll want to ensure that all jobs are configured to notify you upon failure. Make sure to enable the Fail Safe Operator, too. You never know when msdb will have an issue.

Often there are other events inside of SQL Server that you’ll want to know about when they happen. Maybe it’s Disk I/O latencies? Or the occurrence of deadlocks? Or perhaps it’s an excessive number of recompiles? 

That’s when a more advanced monitoring solutions can help. We’ve used most of them in our customer environments and all add value over what you can do solely in SQL Server. Our preference is SentryOne’s SQLSentry. It’s well designed and very helpful.

Whichever you choose, install it, configure it, tune it, and then actually use it.

Too often, we see environments where a monitoring tool was purchased and even installed, but ignored because no one had the time to figure it out. There were too many warnings so the team began ignoring or disabling them. 

Goal 5: Review your Disaster Recovery plans 

Despite all of your efforts to create and keep a healthy system, bad things can still happen. 

Hardware can fail. Databases can corrupt. Users can delete data. Not to mention other issues like ransomware hitting your network, tornados hitting your data center, or any of the other things a year like 2020 can throw at us. (See Protect Your SQL Server from MrbMiner and Other Malware Attacks)

We live in a world where the ability to recover from the unexpected is, well, to be expected.

So, now is a great time to review your High Availability and Disaster Recovery plans. 

• Work with your business stakeholders to understand their expectations. What is their Recovery Time Objectives (RTO) and Recover Point Objectives (RPO)?
• Brainstorm ways to accomplish your objectives. Should you consider Failover Clustered Instances? Or maybe other HA/DR options? 
• Create a cadence to test your SQL Server backups. Restore selected database backups to a test system and run an integrity check on the restored database periodically. Schedule it on your calendar.
• Document your processes. Record the steps and all the information you’ll need, including contact names and numbers, that will be important should an event requiring a recovery become necessary. 
• Schedule DR tests. There is nothing more insightful than actually going the process over a weekend. It will reveal shortcomings and gaps in your plans or documentation. Refine your plans and test again a few months later.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.