SQL Spring Cleaning: You KNOW You Need it!

Tis the Season of Spring Cleaning! While washing your car and scrubbing the window sills, be sure to include your SQL Server environment in your whirlwind of activity. Yes, much like the base boards, your SQL Server environment needs a little extra ‘maintenance’ attention now again.


Upgrading seems obvious, but we see a significant number of SQL Server installations running on the 2008 and 2012 versions. These SQL Server versions were all reasonably solid, so the “if it isn’t broke, don’t fix it” mentality has created a lag behind. Upgrades do more than solve problems however – they also provide new functionality and address issues that exist, but my not be obvious. Additionally, support for SQL Server 2008 R2 ends July 9, 2019, and SQL Server 2012 is scheduled to reach the end of its lifecycle in only three years. This may seem like a lot of time, but depending upon your other projects and your environment, 36 months can come around quickly. If you are on a SQL Server version below SQL Server 106, we recommend you upgrade to SQL Server 2017. Please review the Supported Version and Edition Upgrades for SQL Server 2017 and the Upgrade to SQL 2017 articles, published by Microsoft, to plan and execute your upgrade.

Since many of our clients leverage SQL Server Reporting Services, we wanted to highlight two items specific to this feature. First, the look and feel of Reporting Services changed significantly in SQL Server 2016, along with adding some long-awaited functionality, such as the ability to control parameter layout, mobile reports, and KPIs in the SSRS Web Portal. Second, the Reporting Services installation is now a separate download and install, rather than a feature component of the SQL Server installation.

Evaluate Licensing

SQL Server 2017 Standard Edition can be licensed on a Server + CAL or a Per Core basis. Your Edition and current license agreement will impact your options. Take a moment to touch base with your Microsoft Software Licensing Provider for a period review of your licensing costs.

Perform an Access Review

Databases move, turnover occurs, leaving orphaned users and old windows accounts straggling as a result. Take a moment to run the sp_validateusers stored procedure to identify an Windows accounts that need attention. SQL orphaned users take a bit more research: the MSSQL Tips article, Different Ways to Find SQL Server Orphaned Users, defines scripts for locating and addressing these.

Review the Basics

The turn of the seasons is a good way to schedule a review of your database health, which should be performed multiple times a year. Review your indexes for fragmentation, your databases for overgrown log files or outdated backups. Schedule and perform a “test drive” of a database restore. It is better to identify problems during a test drive than find out your backups are no good in the middle of a real-life issue. Check your SQL Agent Jobs to make sure they are working as desired and retire any jobs that are no longer needed. Evaluate your Reporting Services Subscriptions and confirm their distribution list is current. Confirm your SQL Server Maintenance Plans are performing tasks against all appropriate databases.

Touch Base with Your Team

Ask the users of the applications that depend upon SQL Server how those applications are performing. Users are the eyes and ears of your back-office system performance, so reach out and solicit their feedback. Team members are often the first to feel the impact of a missing or fragmented index or suffer the delays of a poorly written query. While the performance issue may not be something that can be resolved immediately, often times it can be resolved in time or mitigated. Healthy environments make happy users!