For organisations with cloud-based data platforms, most repetitive database-related tasks are automated. But organisations that have their database environment on-premises – and, for legacy and company-specific security reasons, many still do – most likely have an in-house database administrator (DBA). The DBA role is essential to the smooth running of company systems and applications but also involves many mundane and easy-to-miss maintenance tasks. Without automated processes in place, these tasks are likely to be costing you between 5-20 hours per week, if done properly. But it doesn’t need to be this way – many of these tasks can be easily automated.
These vital SQL Server DBA maintenance tasks include:
Backups
Backups need to be executed in a timely manner and be able to meet an organisation’s recovery objectives (RTO and RPO). In the event of a failure, these objectives dictate how long an organisation can be without access to the database. So an organisation’s backup strategy needs to deliver on that – and that means running enough tests to ensure that the backup processes actually work.
It is possible to automate testing to ensure that the back-up processes are working as they should. We recommend testing your backups regularly by restoring them into a lower environment and ensuring the database is operational. Depending on your RPO requirements, you can simply test restoring the full backups only, or the full and log backups. This will also help ensure you meet your RTO by providing restore time metrics.
Index rebuilds
This task involves defragmenting indexes to improve performance for both read and write operations. Index rebuilds also update statistics, which helps with query performance. The SQL Server version and edition controls the availability of certain features, such as online rebuilding, which can drastically reduce the impact of rebuilding indexes. Without Enterprise Edition, or below SQL Server 2005, you must build indexes offline, which makes them unavailable for queries. That means the index is not available during the time of the rebuild.
We recommend implementing a maintenance solution that will automatically assess fragmentation and target only those indexes that will benefit from being defragmented. This reduces the overhead on your server during your maintenance work.
Integrity checks
DBAs need to ensure data integrity on a physical level and that there are no page-level errors that would prevent the database or application from operating. This resource-intensive work should be undertaken during low utilisation periods. At a minimum, integrity checks should be performed every week, however, ideally, they should run on the same frequency as your full backups to ensure the validity of your most recent backup.
Managing Availability Groups
Availability Groups is a powerful feature of recent SQL Server editions starting from version 2012. With Availability Groups, data is written to the primary replica and is then copied over to the secondary replica(s), so if one goes down, the other takes over. There are still issues with this, it’s important to ensure that the instance level configurations are copied as well. Unfortunately, this doesn’t happen automatically. Instance-level objects include logins, agent jobs, audits etc.
So if you haven’t synchronised these instance-level objects and the primary replica goes down, there’s a problem. The data may be available in the secondary replica but no-one can access it without the corresponding log-in information.
Automating alerts
No IT team wants to receive customers calls of “My application is frozen.” But if you are not actively monitoring your databases, this often is the first sign of a problem.
While organisations can implement a monitoring strategy for deadlocks, ideally you should automate as many alerts as possible, especially those that are high severity, like a database being offline. From here you can also decide what action you want the system to take once an event occurs – and at which point human intervention may be required.
Automation is great, but nothing beats delegation
Automating maintenance tasks delivers direct benefits to the DBA and the organisation. It puts the DBA in control and shifts the focus away from always fighting fires. WARDY IT Solutions can help your DBA get this process right.
Better still – we can take care of all the database monitoring, maintenance and management tasks for you. Our Virtual DBA Service provides scalable SQL Server support delivered remotely by world-class Microsoft data platform experts, available to you 24 x 7 x 365. We can help free-up your DBA to focus on what’s important or – if you don’t have an in-house DBA – take full ownership of managing your SQL Server data estate for you.
Interested in learning more? Get in touch today or learn more about our Virtual DBA Service here.
