Database housekeeping: 7 all too frequently forgotten tasks that you should stop postponing.

By James
12th February 2020

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.

 

Get in touch

 

Share

<< Back to resources

Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
I have worked with the WellData team for many years now and they have always delivered. I would highly recommend them to any business looking for best-value outsourced Expert DBA’s. Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment. Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19