Taking care of your health is incredibly important (says the marathon runner). Eating right, exercising, and getting plenty of rest help you stay healthy and perform optimally.
Your database server’s health is important to your company’s health. You need to take regular backups, check for consistency, and keep indexes tuned for optimal performance. Here are a few tips to make sure your database stays in top shape.
Back up System Databases
The master, model, and msdb databases on every SQL Server are integral parts of the system.
Master contains information such as logins, linked servers, and information about all other databases on the server. Without this database, SQL Server can’t start up. If you don’t have a current backup of this database and a disaster occurs, you lose this information.
Model is used as a template for every new database that is created on your system. There are certain default settings you may adjust, such as file sizes, autogrowth, and the recovery model. If you don’t take a backup of this and a disaster occurs, when you set up the server again you have to remember to reconfigure all of these settings, rather than restoring them.
Msdb contains all of the information about SQL Server Agent operators, jobs, and alerts. It also holds backup and restore history tables. If this isn’t backed up and a disaster occurs, you will need to recreate all of your Agent items. I had to do this once – it was one of the worst weeks in my first DBA job.
Ensure that your backup job covers these databases, because in the event of a disaster, you want to restore them rather than rebuilding all of that information.
Need to know more about backups and restores? Check out my Backup & Recovery Step By Step training.
Run DBCC CHECKDB On All of Your Databases
You need to run DBCC CHECKDB to check for consistency errors in your database –yes, corruption. If you’re not running this on system databases, or only running it on “important” databases, you run the risk of not finding corruption in a database.
As already explained, the system databases are incredibly important. Combine not running DBCC CHECKDB with not taking a backup, and you can have a disaster without a hardware failure of any sort.
Only checking those databases you feel are “important” is dangerous. Who defines “important”? Is archived data as important as live data? To the person that uses that information for forecasting and trending, it is. Is a system used for reporting once a quarter as important as production data? To the person that has to use those reports to file taxes, it is. If a database is truly “not important”, and isn’t being used, it should be backed up and removed from the server – not sitting out there, taking up space and resources.
Indexes are the closest thing to a turbo button that SQL Server has. Proper indexes on tables will help your select queries run faster, and won’t hinder the performance of inserts, updates, and deletes. But putting an index or four on a table when it is released to production, then doing nothing further, is not productive. Table structure, the data in the tables, and the queries from users and applications change over time.
As data is added, updated, and removed, the pages in the index will become fragmented. Fragmentation leads to poor performance. The more scattered across the disk the data is, the harder storage has to work to retrieve it. Regular, consistent reorganization and rebuilding of the indexes can combat this.
Are you wondering where to start? Ola Hallengren has a brilliant set of maintenance scripts, including index maintenance, which can help you out!
Keep Your Server Healthy
By performing regular maintenance on your SQL Servers, you’ll ensure that your data is secure and it is performing optimally. Much like you don’t want to wait until you suffer a heart attack to see a doctor, you don’t want to wait until a disaster to find the weak points in your server.