If you are like most businesses you have data somewhere in your company that is run on Microsoft SQL Server; again if you are like most small to mid sized businesses a full time DBA is either cost prohibitive or a luxury in these economic times. If it is not broken it doesn’t need fixing right? With a lot of things this may be true but with database maintenance it is not. A database that is not properly maintained can develop slow response times, downtime and even potential data loss. Database maintenance tasks should be performed as regularly scheduled events by someone who is a professional to ensure your data is safe and your system is running at peak performance.
So what should your DBA be doing daily, weekly and monthly at a minimum to ensure your database is running efficiently? A DBA’s job falls into a number of categories, backup and recovery, performance tuning, daily job monitoring, capacity planning and patching.
Backup and recovery is the strategy used to ensure your data is safe in the event of a data loss, data corruption or server down. It is surprising how many businesses believe they have themselves covered in the event of a database or server failure but don’t. One client we visited insisted he was covered; he had backed up every transaction log daily for the last four years on tape. We had to inform him since he did not do a full database backup in the last four years that if anything were to go wrong he would need to restore the original backup from four years ago then apply each transaction log individually. If any one of the transaction log restores were to fail (over 1000 logs) he would have lost his data. Besides the high probability of failure restoring 1000+ transaction logs would be very time consuming. Another customer backed up all their SQL data to a different directory on the same physical disk. A big no-no, one of the reasons we have backups is in case of disk or server failure. Not only is it important you have the correct backup regime and that you store backups at a secure secondary location but that you check your backups actually work. Take the time to do periodic test restores to make sure you have the data you need, your backup jobs are running and not failing and the backups actually work, i.e. You can perform a restore.
Assuming your DBA has setup your database correctly with an appropriate backup scheme and they are testing and checking your backups a second area they will need expertise in is performance tuning. Over time with fragmentation your database may start slowing down and consuming more space. Log file fragmentation can have a significant impact on performance, there are two types of fragmentation significant to SQL Server. One type is where there is a lot of empty space in a “page”. Most commonly, this type of fragmentation results from inserts, updates, and deletes which leave empty space on a page. The second type of fragmentation is caused when a record has to be inserted on a specific index page but there is not enough space on the page to fit the data being inserted. The page is split in half and some records are moved to a new page. This new page is usually not physically contiguous with the old page and therefore is fragmented. Your DBA should be periodically re-indexing and defragmenting your database to ensure peak performance. It is amazing how many users complain about a slow database when a regular maintenance plan would solve many of their issues.
The largest part of your DBAs job is to review your SQL Server logs daily. If you did not fully get that last sentence please read it again. Looking over your database event logs or better yet implementing alerts will save you a lot of headaches. Get familiar with the server’s event logs, SQL Server related events are recorded in the Application log. This includes, by default, major activities that potentially affect the system, such as SQL Server startup, shutdown, backups, restores and change of configuration options. Reviewing these logs on a regular basis can help you identify and correct issues in a timely manner. One customer who called in a panic because their database had stopped working had ignored all the alerts in their event log that they were running out of space. SQL Server will just stop running if you are out of disk space.
If you have a solid DBA they will have a capacity plan in place this will include projected needs for storage for the various database logs and files, acceptable server memory, acceptable processor limits and performance benchmarks. There are many programs out there that can be utilized to track key server statistics and performance allowing your DBA to project when you will need that additional storage capacity or when you should upgrade your server. As part of our database management program we track many historical statistics so you can identify storage trends and key performance indicators for your database system. Too often companies do not actively plan capacity issues such as database growth often leaving default settings like auto-grow on increasing fragmentation.
Finally your DBA should be in charge of ensuring your database is up to date with the latest patches. This is a critical database maintenance issue, back in 2003 when the SQL slammer worm came out 75,000 computers were infected within 10 minutes because people did not have their servers patched. The traffic was so heavy that the internet was actually significantly slowed. Even though it has been many years since slammer I still run across servers running SQL 2000 at pre-slammer patch levels. Yes, sad but true.
For many companies your data is your business. If you have any concerns about your database or you just want to ask a question please feel free to Contact Us.
Brett Stone, Senior Database Consultant
Northwest Computer Support