A poorly maintained or unmaintained database can adversely affect application performance and can even lead to downtime or data loss. The only way to mitigate the risk of such a problem is effective maintenance of the database.
Database Maintenance is a process that helps in the smooth functioning of the database. It includes an array of activities that must be scheduled to be performed on a pre-defined basis.
The following is a checklist of 6 items that should be in place for all of your SQL Server database servers. Some of these items are pretty basic and easy to implement, while others require a higher level of effort to think through what is needed and then to implement the process.
These items are not necessarily written in any priority order, because not any one of these items is a complete database management plan it really requires all of these items to be thought about, addressed and implemented.
This is one of the most basic items to be addressed. Everyone knows that a good solid backup plan should be in place for all databases, but time and time again I run across servers where the wrong or no backup plan is in place.
To get started you need to ask yourself a few questions such as the following:
- What are you trying to recover from when a failure occurs?
- How much data can be lost? A day, one hour, a week, none...
- What kind of processing occurs, transaction based, batch loading, a combination?
- Can the data be easily recreated if there is a failure or is this the only source of this data?
This is just a short list of questions to ask, but once these are addressed this will allow you to determine; 1) the recovery model for your database and 2) the backup processing. Depending on your needs your backup plan may look like one of the following:
- Daily full backups only
- Daily full backups and transaction log backups every hour
- Daily full backups, transaction log backups every 15 minutes and differential backups every 4 hours
Note: If you are unsure what to do, start with at least full backups on a daily basis. If your data is very important and you cannot easily recreate the data, change your database recovery model to FULL and implement both full and transaction log backups.
2. Run Integrity Checks
Another area that should be addressed is the integrity of your data. SQL Server offers a few options to address this such as DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, etc...
These commands check the allocation, structure and logical integrity of all objects in your database. In addition, to running these commands either through maintenance plans, maintenance jobs or via a query window you also need to analyze the data to look for any integrity issues that need to be addressed.
3. Maintain Indexes
Indexes are those helpful pointers that allow you quick access to your data pages in your database. When indexes are newly created the structure is nice and clean and everything works great by accessing your data via the index instead of having to scan the entire table.
Over time these helpful indexes become fragmented and take up unnecessary space and accessing your data pages is not as efficient as it was when the indexes were first created. This is where index maintenance is a critical process that needs to be in place.
4. Review SQL Error Logs regularly
There are several areas where SQL Server logs information about processes that are occurring as well as errors that occur. The most used is probably the SQL Server Error Log. This error log gives you startup information, integrity check information, backup information, etc... as well as any SQL Server errors that occur. In addition to these internal SQL Server logs you should also use the Windows Event Log to find other errors that may be occurring or possibly additional information that is not in the SQL Server logs.
Reviewing the logs should be part of your daily routine for checking the health of your system. The ideal way to handle this is to use some tool that automates the alert process when there is an error, but either way you should keep these error logs on your radar list as something to review each day.
5. Test Backups
A SQL Server backup is only good if the restore works. No matter how many backups you take of your database if you cannot restore the file when needed there is no point in doing backups in the first place. The only way to determine if your backup/restore process will work is to periodically test on another server. This not only gives you peace of mind that the restore was successful, but this also gives you an idea of how long the entire process will take if you ever need to do this on your production server. Having this little insight and the time it will take to recover you database will go along way when you have people breathing down you neck.
In addition to testing, you should also use the RESTORE VERIFY option when creating your backups. It doesn't necessarily tell you that the restore will not have any issues, but it will at least prove that SQL Server can read the entire backup file without a problem.
6. Monitor Performance
This is one area that should be a no-brainer if you are responsible for monitoring your SQL Server environment. The database is usually the last thing people think about when they are working with an application, but when the application is slow the database is usually the first thing that is blamed for the poor performance.
The problem with performances monitoring is not that most people don't do this, it is that they are not sure how to do this. Windows and SQL Server offer built in tools such as Performance Monitor, Profiler, Execution Plans, Index Tuning Wizard, Database Engine Tuning Advisor, etc... In addition, there are a whole bunch of third party tools that allow you to trend performance issues and be alerted when there are issues. Without good data it is very difficult to say when there really is a performance issue and also without this data it is difficult to know where to spend your time fixing issues that will have a big impact versus fixing something that will not have a very big impact.
Now that you have a list of some basis items to address, check off which items you have already implemented and start addressing the other items. If you are not sure where to begin, start with some of the simpler items like, backups, documenting changes, reviewing error logs and testing backups/restores.