In some professions, individuals are expected to wait patiently until called upon to perform their functions. There are no opportunities for people engaged in these jobs to take any type of proactive action that will impact the activities they will need to carry out when the time comes for their expertise to take center stage. While they can make plans on how to handle the eventual call to action, there are no concrete steps that can be taken until that time arrives.
A case in point that should be familiar to many readers is that of the replay officials who verify close calls that occur during professional sports events. Another example is the emergency-room physicians and nurses who are only needed to address patients who have been impacted by an unexpected accident or illness. There is nothing that can be done to minimize the pressure to perform that will ensue once an action is required. It’s a matter of waiting for a variable amount of time and then having to immediately provide maximum effort and efficiency.
DBAs are not faced with this kind of predicament. Most DBAs have multiple opportunities to look for signs of trouble in their databases before they rise to a level that impacts users or the organization. A mindset of finding trouble before trouble finds you is a viable strategy for professional self-preservation if you are a database administrator.
Places Where Trouble May be Lurking
The point is to identify issues that may not appear to be a problem, but upon deeper inspection, may prove to have serious ramifications for the health of the SQL Server environment or the business that it supports. Undertaking this kind of investigation may involve taking a detailed look at processes and items that appear to be working fine but may have underlying issues that can lead to disastrous consequences. It may also involve opening avenues of communication with other departments or management to address issues that you discover.
There are a lot of potential areas related to an organization’s SQL Servers that may present a trove of undiscovered problems just waiting for the right moment to make their presence felt. Many of these involve activities that may be referred to as routine database maintenance tasks. That is an innocuous way of describing some very important responsibilities. Here are two places where you might conduct a very fruitful search for trouble.
Backup and recovery
One of the most critical tasks of a DBA involves the backup and recovery procedures that are used with their SQL Servers. The ability to restore critical systems in the event of a disaster can be the difference between the success and failure of an enterprise. When developing a backup and recovery strategy, the vital first point to consider is the amount of data an organization can stand to lose.
Backing up once a day means that you can afford to lose a day’s worth of data. If a system crashed right before the daily backup started, the most recent backup would have been from almost 24 hours ago. This is certainly too long a duration for critical financial systems that are being continuously updated.
It might be above your pay-grade to determine the amount of data that the business can risk when developing backup strategies. It is up to you to see how your systems are currently being backed up and communicating your findings to those who will make those determinations. Failure to do so may leave you in the unenviable position of having to tell a roomful of stressed executives that the best we can do is 24-hour old data. Good luck with that.
Backups are great but their real value is when it’s time to perform a recovery. In addition to ensuring that you are carrying out a viable backup strategy with your SQL Servers, you need to be conducting regular testing of the recovery procedures. You need to know where the backups are stored, how to access them, and how long it will take to perform the recovery.
Index analysis
Proper use of indexes is key to good SQL Server performance. Performing index analysis and monitoring to determine if indexes are used, unused, missing, or fragmented should be done regularly. Once index information is gathered, you can begin to look at specific items that may need to be addressed so they can be tuned for improved performance.
You may find that many indexes are used infrequently or not at all and can be eliminated. Other indexes may be used extensively but be poorly constructed. Revamping these indexes can result in faster database operations. Identifying and developing missing indexes is another way to boost performance. The insights you gain into how indexes are used in the environment should be shared with database developers so better use of these important constructs can be fostered throughout the enterprise.
What’s In Your Toolbox?
If you plan on looking for trouble in your role as a DBA, it is advised that you begin the search armed with the right tools for the job. SQL Diagnostic Manager for SQL Server is a great tool for monitoring your SQL Server environment so you can be alerted to issues that may eventually rise to the level of problems. The application works with physical, virtual, and cloud instances and provides the ability to collect performance metrics based on customizable baselines.
The insights provided by the tool’s reporting capabilities will point out areas that deserve further investigation. Prescriptive analysis and automated alert responses help a DBA team handle recurring issues that do not require a manual response. Tailor alerts so that the right people get notified when necessary and avoid alert overload.
An IDERA Webcast that goes into further detail on the steps a DBA can take to proactively address SQL Server issues is available and well worth a watch. It contains a wealth of information and many links to further resources that can assist you in optimizing your SQL Server environment.