Are you doing what you must to ensure that your SQL Server environment is behaving as you, or your manager, expects that it is? Whether you are veteran, involuntary, or junior DBA, there are common things that we are required to do for our SQL Servers on a regular basis. These tasks may seem like they are relatively low level items, but they are in fact critical in maintaining the health and security of our SQL Server instances. This article includes the top 10 tasks that should be done each month in your SQL Server environment. This list is maintained monthly, so come back each month for the current list of updates and items to take a look at. You can find information on monthly changes to this list in the Monthly Checklist category.
1. Update Windows Server
As important as maintaining the SQL Server environment is maintaining the operating system that hosts SQL Server. If the operating system is not up to date, then issues related to issues in the operating system could affect the performance and stability of the your environment. Since the release of Windows Server 2012, Microsoft has shifted from releasing Service Packs for Windows Server to releasing Monthly Update Rollups, be sure to keep your server up to date with these patches.
- Windows Server 2012 R2 (Evaluation)
- Windows Server 2012 (Evaluation)
- Windows Server 2008 R2 SP 1
- Windows Server 2008 SP 2
2. Update SQL Server Instances
Is your SQL Server environment up-to-date? Check each of your instances and review the most recent releases of SQL Server. Make a plan to determine when the most recent updates will be applied. Also, be aware that support for SQL Server releases do end at some point. Support for the version of SQL Server that you are currently using may no longer be supported.
SQL Server Mainstream Support
- SQL Server 2016 RC0 (Evaluation)
- SQL Server 2014 SP1 CU5
- SQL Server 2014 SP 1
- SQL Server 2014 CU12
- SQL Server 2014 (Evaluation)
- SQL Server 2012 SP3 CU1
- SQL Server 2012 SP3
- SQL Server 2012 SP2 CU10
- SQL Server 2012 SP2
- SQL Server 2012 SP1 CU 15
- SQL Server 2012 SP1
- SQL Server 2012 CU 12
- SQL Server 2012 RTM (Evaluation)
SQL Server Versions Outside Mainstream Support
- SQL Server 2008 R2 SP 3 – Mainstream support ended 7/8/2014
- SQL Server 2008 R2 SP 2 CU 13 – Mainstream support ended 7/8/2014
- SQL Server 2008 R2 SP 2 – Mainstream support ended 7/8/2014
- SQL Server 2008 R2 SP 1 CU 13 – Mainstream support ended 7/26/2013
- SQL Server 2008 R2 SP 1 – Mainstream support ended 7/26/2013
- SQL Server 2008 R2 CU 14 – Mainstream support ended 7/12/2012
- SQL Server 2008 SP 4 – Mainstream support ended 7/8/2014
- SQL Server 2008 SP 3 CU 17 – Mainstream support ended 7/8/2014
- SQL Server 2008 SP 3 – Mainstream support ended 7/8/2014
- SQL Server 2008 SP 2 CU 11 – Mainstream support ended 10/6/2012
- SQL Server 2008 SP 2 – Mainstream support ended 10/6/2012
- SQL Server 2008 SP 1 CU 16 – Mainstream support ended 9/24/2011
- SQL Server 2008 SP 1 – Mainstream support ended 9/24/2011
- SQL Server 2008 CU 10 – Mainstream support ended 3/31/2010
- SQL Server 2005 SP 4 CU 3 – Mainstream support ended 4/12/2011
- SQL Server 2005 SP 4 – Mainstream support ended 4/12/2011
Beyond the items above, there are a few others things to pay attention to when checking the updates applied to your SQL Server instances. Do any of the following apply to your environment?
- On SQL Server 2012 SP2 or SQL Server 2014 RTM, you may encounter a corruption issue with online index rebuild. There is a fix for this issue detailed in KB2969896.
- SQL Server 2012 at all, there are a number of performance and stability related fixes that should be paid special attention too. Check out Glenn Berry’s (Blog | @GlennAlanBerry) blog post Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds for great information.
- If you are using SQL Server Reporting Services, you need to check to see if the MS12-070 applies to your environment. The bulletin identifies and patches a vulnerability related to cross-site-scripting.
- If you are running SQL Server 2012 SP1 prior to Cumulative Update #2, there is a fairly important hotfix available that fixes an msiexec issue. (KB2793634)
3. Validate Backups
Check everything involved in the backup process. Are your backups executing as desired? Are the monitoring jobs properly alerting to failures? Have their been any unexpected failures? Have backup duration times changed?
4. Validate Recovery Strategy
You have your database backups, but do you have everything else required for recovery? Is there detailed documentation on how to recovery all of your databases? Have you practiced restoring at least one of your SQL Server databases from production in the last month? If you don’t take the time to practice before there is a fire, you can’t be sure you know what to do. Would you trust a fireman that never practiced?
5. Verify SQL Server and Windows Configuration
Should your server have hyper-threading enabled? Is it? Are there any trace flags that you think are running on the server? Have you verified that? What are your expected MAX DOP and Cost Threshold for Parallelism setting? What about other SQL Server configuration settings?
6. Check Server Performance and Health
Were there unexpected outages or failures for the server in the last month? Were there any unexpected items in the server error event log? How does everything compare to the last baseline?
7. Check Database And Instance Performance and Health
Check the performance statistics for your SQL Server instance and database(s). Are there any variances on the performance counters off of the baseline? Are spikes or valleys in performance statistics explainable? Were there any unexpected items in your SQL Server logs?
- Have you checked wait stats.
- Did you analyze your indexes
- SQL Server 2005 – [download id=”65″]
- SQL Server 2008 – [download id=”64″]
- SQL Server 2012 – [download id=”63″]
8. Review and Adjust Your Baselines
During the performance and health reviews, were there any differences in baselines that justify a change in expected values? Are all baselining processes running as expected? Any new information needed as part of performance and health review?
9. Validate Capacity Plan
If you have a capacity plan in place for your environment, check to see that what you had planned for June matches the actuals. Any threshold violations that may require adding capacity?
10. Submit Status Report
What do you need to get done before next month? What did you get done this month? After the other tasks, write this all down and send it to your manager. Was there an outage in the last month? Write a postmortem for the team. Was there a team success? Write that down for management. Be certain you are proving your value to the team each month by communicating with them.
Something Missing?
Is there something missing in this list that you think should be included? Leave a comment and I’ll add it in for next month.
Strate SQL 
I took some ideas of your post and made a PostgreSQL version, I hope you have no problem.
Thanks for sharing.
LikeLike
Are these daily, weekly or monthly? Can you categorize them?
LikeLike
great summary, thanks for sharing
LikeLike
Your welcome. Glad that people find this useful.
LikeLike