The Perfect SQL Server: Striving for Excellence in an Imperfect World

Does the perfect SQL Server exist? And what would that even look like?

Let’s think about it for a moment.

If the perfect SQL Server exists, it would be flawlessly tuned for its workload. It would have the ideal amount of hardware resources, and its settings would be perfectly optimized.

The perfect SQL Server would have a complete and unfaltering High Availability configuration. It could survive any kind of disruption: a SQL Server issue, an operating system issue, a hypervisor issue, a hardware issue, a connectivity issue, a datacenter issue, etc.

It would also have a proven, documented, and unassailable Disaster Recovery plan. If something did happen, the company’s Recovery Point Objectives (RPOs) and Recovery Time Objectives (RTOs) would be easily met.

What about patching and upgrades? Operating system patches, SQL Server Cumulative Updates, and application updates would all be exhaustively tested in lower-level systems prior to ever seeing the production environment.

Data pipelines would be efficient and accurate. They would be thoroughly documented, and exception handling would be robust.

In short, the SQL Server would be protected from every imaginable contingency—from ransomware to hardware failures, from runaway queries to datacenter disruptions, from users and the things users do.

And that’s not even a comprehensive list.

Do you have an environment like that?

Probably not.

Can you create one?

Again, probably not. At least not anytime soon. And probably not on a budget.

Don’t let the perfect be the enemy of the good

Just because you may not be able to create a perfect SQL Server or data environment, that doesn’t mean all hope is lost. You can, and should, take incremental steps toward a better SQL Server.

So, how do you take action? Here are a few things you can do if you’re not already doing them.

Disaster Recovery

You may not be able to create an extensive approach to Disaster Recovery. However, here are some things you can do:

• Backups. Regularly verify and test your approach to backups and restores. See How Often Should I Test My SQL Server Backups? for more information.
• RTOs & RPOs. Your organization may not have explicitly defined its RTOs and RPOs, but you can set your own baseline standards. Then, adjust them as needed. See How to Align Your SQL Server to Your RPO and RTO Goals.
• Log shipping. If you don’t have the budget for “push button DR,” you can still leverage an oldie-but-goodie feature of SQL Server to help recover from a disaster: log shipping. See Configure Log Shipping (SQL Server) for a step-by-step guide.
• Asking questions. Not all advancements need be technology-based. Understanding the current state and ideal future state has value. Knowing how your business, operations, and customers would be affected if your systems were unavailable can help you align your disaster recovery approach. See Where to Start with Disaster Recovery in SQL Server.

Maintenance, alerting, and monitoring

If your company doesn’t allocate money for SQL Server tools such as SQLSentry or Redgate Monitor, you still have options. Here are some free tools you can use to maintain and monitor your servers:

• sp_blitz. You can run Brent Ozar’s sp_blitz to get a good overview of how your SQL Server is configured and functioning. See sp_Blitz®: Free SQL Server Health Check Script by Brent Ozar.
• SQL Server Maintenance solution. Ola Hallengren has a great set of tools for backing up and maintaining your SQL Servers. See SQL Server Backup, Integrity Check, Index and Statistics Maintenance (hallengren.com)
• Built-in capabilities. SQL Server has some alerting and monitoring capabilities. See A Severe Error Occurred! 5 Ways to Detect Database Corruption Early and Azure Arc-Enabled SQL Server: Centralized Management for Hybrid Environments
• Set a schedule. If you haven’t already done so, create a monthly cadence for applying patches and other updates to your SQL Servers. First to test, then to production.

Testing and lower-level systems

Sometimes the resources required for a complete set of lower-level systems simply aren’t available. The costs for allocating a full Dev, Test, QA, UAT, Demo, Training, Staging, etc, environment can add up quickly. However, you may be able to replicate some of those environments in a cost-effective way.

• Combined systems. If separate environments for each isn’t doable, perhaps you can combine some of those environments.
• Reduced environment. Sometimes storage can be an issue, especially for multi-TB databases. However, you may be able to create processes to copy a smaller subset of data to lower-level systems.
• Containers. Containers may be a good option for some lower-level systems. See Getting Started with SQL Server in a Docker Container

The SERO Group can help make your SQL environment better

Often, budgetary and capacity constraints mean our SQL Server environments are not what we wish they were. But, since we’re unlikely to ever get all the money and help we want, we can and should make incremental improvements in every area we can.

Here are a few other helpful resources:

• SQL Server Settings: Optimize For Ad-hoc Workloads
• The Risks of Mixing SQL Server Native Backups with Snapshot Technologies
• Archiving and Deletion Strategy…KonMari for Data Management?
• 5 Reasons You Should Use SQL Audit to Enhance Database Security

We work with a lot of companies that cannot justify a full-time, professional DBA to take care of their environment. If you’d like to know more about how we help them keep their data systems healthy, secure, and reliable, let’s set up a short call to discuss.