Lack of time and workload demands often get in the way of carrying out proper database housekeeping and, while the impact of these delays might not be immediately apparent, the end results might be very risky indeed.
A professional, well-trained DBA knows that there are basic housekeeping tasks that need to be scripted, automated and/or scheduled, including:
- Regular database backups
- Regular database maintenance
- Monitoring
In addition, there are other tasks that should ideally be practiced regularly, such as:
- DR recovery scenarios
- Failovers
- Health checks
- Source control and versioning of scripts
However, there is a further list of key activities that too often end up under the radar, when they really shouldn’t be forgotten.
The following is a list compiled by James Newton-Brady, one of our most experienced DBAs, to help ensure that your database housekeeping is up to standard:
Restore Tests
While everybody knows that databases should be backed up, a less common practice is to perform restore tests from the backups. Simple database backups mean that in theory you can recover the database if it becomes no longer available. But how do you know where the backup file can be restored from? And how can you be sure that the backup file is ok? Obviously, there are things like backup checksum and backup verification for SQL Server, and RMAN validations for Oracle that can give you a pretty good idea that your backup file is ok – but the real assurance only comes from actually running restore tests from the backups to make certain that, should the worst come to the worst, you will be able to effectively recover your data. Obviously to run the restore tests you will need additional resources, like a server dedicated to backup tests, the end result is worth the investment.
Security Reviews
User permission reviews should be part of a regular database health check, but in our experience, clients rarely actually carry this out. In fact, we have seen many instances where employees have left but still have user access and enabled accounts. Businesses can be wary of deleting the user, fearing possible dependencies on that account, but they don’t realise the potential threat that leaving access to their mission critical databases to an ex-employee can mean to their business.
Also, users can have higher privileges than required – it can take some effort to establish exactly what permissions are required for a particular account, so often system administrators will just elevate permissions in order to solve a problem, but this is not the safest approach. Permissions should be reviewed and adhere to the Principle of Least Privilege (i.e. accounts have only the necessary permissions and no more).
Database Usage
Identifying databases that are no longer in use is harder than it sounds. It would involve closely monitoring the databases, taking them offline, ensuring that this hasn’t caused any disruption, and only when you are absolutely certain that nothing will break down, the database can be removed. This is why all too often this task isn’t done and databases that are no longer used remain online; the problem? Being online means they still require maintenance and backups, using unnecessary system resources and capacity.
Job Clean-up
Similar to databases being left in service when they are no longer used, we often come across scheduled jobs that have been disabled or are no longer running. These should be deleted, but once again a certain level of effort is required to ensure the job is definitely not required, and that’s why it can get delayed and consequently ‘forgotten’.
Application to Database Mappings
Often there is little or no documentation on which applications use which databases and, particularly for larger businesses, this can cause severe delays in solving application issues. This lack of mapping makes it hard to track down the database that needs to be addressed if there is a problem with a particular application. It can also lead to databases remaining online even when the application has long since been decommissioned, once again wasting precious system resources and capacity.
Defining RPOs and RTOs
While this a business decision rather than a DBA’s prerogative, having Recovery Point Objectives and Recovery Time Objectives is critical. Unfortunately, too often we come across clients who don’t know theirs. In a nutshell, having RPOs and RTOs mean that in the event of a database becoming unavailable there is clear direction on how much data can be lost, and how quickly the database should be back online, streamlining the recovery process and the decisions required in terms of resources to utilise for the recovery and recovery strategy.
Planning for Upgrades
Every database version will reach end-of-life at some point and that’s why it is important that you begin upgrade plans, including tasks such as checking for breaking changes in newer editions, and begin working on fixes.
If you are reading this and realise that you or your team have not been quite as proactive on managing these tasks as you maybe should have, the time has come for you to put in place a schedule to ensure that they no longer get postponed.
At WellData we can also help you. As part of the unique level of service we offer our clients, we cover all of these tasks through our daily management, so you don’t need to worry about it anymore.
If you are considering outsourcing your database management, contact us now and we will go through your specific requirements and identify how WellData can help you.
<< Back to resources
