T-SQL Tuesday #157 – Monthly DBA Checklist

Thank you Garry Bargsley for hosting the December month of T-SQL Tuesday!

Garry wanted us to write about the year end checklist for the Database administrators. I would like to write about the checklist I prefer to follow every month of the year.

Some of the important monthly checklist are as follows:

1. Backup validation: Making sure your database backups are valid. You can do that by adding CHECKSUM while taking the backups, verify the backups regularly using VERIFYONLY option and also test the restore if it works properly by actually restoring the backup on different environment. Make sure you run the DBCC CHECKDB to be extra safe in making sure your backups are in consistent state.
2. Database monitoring: Making sure the monitoring tool you use are not missing any of the instances. Making sure the alerts are triggered properly and email filters are in place to receive those alerts.
3. SQL Server Security updates: Making sure your Servers are patched properly and all the services are working properly after the patching. I mention this because we recently found an issue with Master data services unable to connect to the database after SQL Server Security patching on either SQL Server Server where MDS database is hosted or on the MDS application server. We need to manually go ahead and update the database from the MDS configuration manager. To run this step of updating the database after patching, the user who runs this update needs to have super user permissions to MDS. If you are planning to take the vacation, make sure these servers specific to MDS are patched and properly updated. If you are in vacation and other DBA’s doesn’t have enough permissions to update the database or if they don’t have proper documentation to the procedures, it would be a disaster.
4. Documentation, please: No matter how small the task and the resolution steps might be, having proper documentation of steps for troubleshooting any issue helps the DBA team members have peace of mind while resolving the issue. Make sure documentation is in place and the location to these documents are shared with all team members.
5. Job failure notifications and please no success notifications: Make sure you only receive failure notifications and disable if you have any success notifications setup on any of the servers you built. Make sure the job owners are valid.
6. Check for any password expirations: If any of the AD accounts are the owners of any jobs from task scheduler at OS level and if those accounts has password expired, you will get continuous notifications for those failed jobs due to password expiration. Making sure changing those passwords before going to vacation helps. I mentioned this example because I have seen cases in my past company where emails are bombarded with the failure alerts due to password expiration.
7. On-Call: If you plan to take vacation and if your DBA team is small, make sure there is a backup for the On-Call person just in case. Having the updated contact information in outlook teams where everyone can access your contact information is crucial.

These are some of my monthly activities to check. I am looking forward to read the posts from other SQL family members on the same topic. I am sure there will be plenty of activities to take a note of.

Thanks for reading!