As a DBA or Consultant, Database Maintenance is one of the most important tasks that needs to be implemented.
What is Database Maintenance?
What does it do for our systems that we are supporting?
Should we consider implementing something?
Is it worth the effort to implement?
These are just some questions that float around out there around Database Maintenance.
So what does Database Maintenance consist of? In this blog I will discuss what makes up Database Maintenance. All of this is not specific to any Version (SQL Server 2005, 2008, 2008 R2) or Edition of SQL Server. Having said that the Edition could potentially impact how you implement your database maintenance. Ie SQL Express does not come with an Agent tool to allow you to schedule your jobs. If you are using SQL Express at all then you can still get around this issue. Have a read of a previous blog – Installing SQL Scheduler.
Database Backups
Database backups are by far the most important action to be setup and implemented. This is your get out of jail free card if and when an issue happens. Having said that this is only true if you implement a solution that is appropriate for your requirements. Some things to consider when you are looking implementing a backup strategy are:
-
Size of your database
-
Frequency of change in your data
-
Recovery Point Objective (RPO)
-
Recovery Time Objective (RTO)
When you are looking at determining your backup strategy you should step back and look at your recovery strategy. Your recovery strategy should give you the ability to restore your database as close to the point in time that you need to, taking into consideration that you may lose some data, in the easiest and shortest process possible. What I mean by this is that it is no good if you are taking a full backup on say a Sunday night and then taking transaction log backups every 15 minutes. Your recovery time and process is going to be quite long. So take your time in understanding your environment and come up with your restore process and this will give you how to configure your backup process.
The types of backups available to you to consider are:
- Full Database Backups
- Differential Backups
- Transaction Log Backups
For more information on database backups and how to perform them, read more at How to perform database backups in Management Studio.
Database Integrity Checks
To ensure that your database’s Integrity and Consistency is valid you need to regularly check all of your user databases. This is a very simple and easy task for you to be able to implement and schedule. The use of DBCC CheckDB on a regular basis is the simplest and easiest way for you to achieve this. DBCC CheckDB calls and runs the following 3 DBCC commands:
The frequency of running this command is dependent on the number of database’s you have and the size of these databases. Ideally you should run these in non-peak times. You could look at testing your backups you have been taking and perform a restore. This gives you peace of mind that the backup is valid and can be restored. On this restored copy you can run the DBCC CheckDB to confirm that the database is consistent. This does however only give you the consistency at that point in time from when the backup was taken. This option allows you to reduce the workload on your production system.
Index Optimisation
Index Optimisation covers the process of Rebuilding or ReOrganising your Indexes as well as updating the statistics. These are achieved by using the following commands:
By regularly performing these functions help improve your performance.
Implementing Database Maintenance
Now that we have covered off what makes up database maintenance we need to look at implementing these tasks in our environment. There are a few different ways you can implement these:
-
Scripts
Which one you choose is dependent on your environment and your skill level to support and make changes moving forward. Fortunately from a script point of view Ola Hallengren has produced a set of maintenance scripts that you can download and use in your environment. Ola’s scripts have been recognised and awarded a Gold Star in the SQL Server magazine 2011 community choice awards. Have a look and download Ola’s scripts into your environment to help you with your database maintenance.
Leave a Reply