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 database problems. Enact these often-neglected maintenance items to help keep your SQL Server 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 those tasks that they've been neglecting.

Obviously, every organization has different needs, but here are five SQL Server maintenance steps that you should enact sooner rather than later in 2018 -- 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. Of course, database backups are meant to guard against such catastrophes.

The problem, however, 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 those requirements and then treat that process as though it will be viable for an indefinite period of time.

Business, however, 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.

2. Perform an exhaustive SQL Server audit. Another SQL Server maintenance priority for the new year is to do a comprehensive audit of your database environment. Such an audit should include tasks 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 in a situation where you're supporting several different SQL Server versions. Doing so can make the SQL Server management process more difficult and can 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 being 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 performance 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 things to do for SQL Server maintenance 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 to the data growth model. 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. Even if the changes that result from the steps outlined here aren't monumental, 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 -- by taking some time now to take care of long-neglected SQL Server maintenance tasks.

This was last published in January 2018

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What steps toward better SQL Server administration and maintenance has your organization taken recently?