lassedesignen - Fotolia

Manage Learn to apply best practices and optimize your operations.

Five SQL Server maintenance steps you should take -- ASAP

Putting off SQL Server administration tasks can lead to problems. Do these often-neglected database maintenance items to help keep your systems running smoothly.

As IT professionals, we're all busy and, inevitably, there are tasks and projects that get pushed to the back burner...

because others take on a higher priority. But with the start of a new year upon us, now is a great time for SQL Server database administrators to prioritize some of the database maintenance tasks they've been neglecting.

Obviously, every organization has different needs, but here are five SQL Server maintenance steps you should take sooner rather than later -- especially if you can't remember the last time you did them.

1. Re-evaluate your backup strategy. Data losses can do irreparable harm to a business, as countless IT disasters have shown over the years. Database backups are meant to guard against such catastrophes. The problem is that backup architectures rarely keep pace with the evolution of SQL Server infrastructures. All too often, organizations establish a set of data protection requirements, design a backup process that meets them and then treat that process as though it will remain viable indefinitely.

However, business is anything but static. Over the course of a year, databases and other IT resources may be added or retired. Likewise, data recovery objectives may change in response to business needs.

As such, it's important to take the time at the beginning of the year to make sure that the database backup tools and procedures you have in place still adequately meet the organization's needs. Whether you use maintenance scripts based on stored procedures or the database software's automated SQL Server maintenance plan functionality, assess what you're doing to back up your databases and transaction log files -- and modify things if necessary.

2. Perform an exhaustive SQL Server audit. Another SQL Server maintenance task to prioritize for the new year is to do a comprehensive audit of your database environment. The audit should include things such as making sure that SQL Server systems are fully patched with the latest security fixes, that the correct permissions have been assigned to various SQL Server resources, that database indexes are being used appropriately and that all the databases are being properly backed up.

Adhering to proper database administration procedures throughout the year theoretically mitigates the need for such checks. However, in a hectic IT environment, details occasionally have a way of slipping between the cracks, which can lead to shortcomings that a SQL Server audit would reveal. Without one, you might not know about issues until problems arise.

3. Establish version consistency. One of the priorities I'm setting for my own environment in 2018 is to standardize software versions -- something that database administrators, in general, should think about doing.

New processing workloads added by organizations are commonly built on the latest versions of SQL Server and Windows Server. At the same time, existing workloads may still run on older software. Given the required cost and effort, there isn't always a compelling reason to upgrade a database server just because a new version of SQL Server was released.

Over time, though, you can end up having to support several SQL Server versions. Doing so can make the SQL Server management process more difficult and increase costs. An organization may be able to reduce some of these costs, while also taking advantage of better security features and other technology improvements, by standardizing on the latest SQL Server version. And because doing so can be a large financial undertaking, the best time to plan such an endeavor is often at the beginning of the year when annual budgets are established.

4. Seek out performance issues. Organizations that expose online applications to their customers typically put a tremendous amount of effort into making sure that public-facing systems perform optimally. However, workloads for internal users might not receive the same degree of scrutiny. By taking some time to identify SQL Server performance problems and optimize internal workloads, both operational efficiency and the lives of end users can be improved.

5. Update your capacity planning projections. One of the most important SQL Server maintenance items at the start of a new year is to update your database capacity planning projections for the foreseeable future. Data continues to grow at an eye-popping rate, and it's becoming more and more central to business operations. Earlier projections of SQL Server capacity requirements should be revisited to see if adjustments need to be made. You may also have to budget for additional storage and processing costs based on your findings.

The start of a new year marks an opportunity to make changes within an organization. The changes that result from the steps outlined here may not be monumental. Even so, by taking some time now to take care of long-neglected SQL Server maintenance tasks, you may be able to improve database operations and reduce costs -- to the benefit of business users, the IT staff and the company's bottom line.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning