As the year ends, we tend to look at the year ahead as a new beginning. It is a time when we can put aside bad habits, or take on a new hobby. For some, it may even mean making updates to your SQL Server environment. The changes may be big or small, but the general idea is that this next year will be better. Better health, better relationships, improved skills, and new opportunities are only some of the great personal resolutions that will be made for the coming year. This post will look at possible resolutions to improve your SQL Servers this year.
Reel In Account Permissions
It’s not uncommon to see user or service accounts in SQL Server that have access to much more than is necessary. This could simply be a service account that needs read/write access to a single table having this access to all tables or it could be an account that needs very little access that is assigned to the sysadmin role. This is common and most of the time the user may not even be aware of their own additional access. In other cases, a bug in the code or a disgruntled employee could disclose or delete your data. A user aware of the access may try to make improvements without “bothering the DBAs” that could be innocent enough but may not be the right solution. In one such case, a user had created an index on each individual column on their tables and then setup a job to regularly shrink the database which resulted in fragmentation of every single newly created index. This wasn’t done with malicious intent but would have been avoided if the proper permissions were in place.
Remove Unnecessary Applications
Your SQL Server wants to use all the resources available to it to do what it does best. Many times, other applications are loaded on these servers either by policy or users that could be installed somewhere else. Common applications are Microsoft Office, multiple web browsers, Visual Studio, IIS, and similar applications. These are typically installed for convenience but often they are opened and left open holding resources while sessions are disconnected preventing the engine from being able to use them. In the case of CPU intensive applications, you may be tempted to add more cores to the server to assist with the application load which also increases your SQL Server licensing cost though your SQL Server instance didn’t need the additional CPU.
Clean Up Your Unused Objects
You know what I mean. That feature that was removed from the application but still has supporting tables and procedures unused by anything else. The 20 tables that are labeled something like table_backup_20120618. The job created to capture data at an interval to help identify that problem you resolved five years ago. It’s time to clean those up. They may not be affecting much in resources but sometimes these long-forgotten objects can cause additional work in the future of identifying whether it is even in use or worse migrated to a new system just to be safe.
Document Your Database
Everyone’s favorite task. It’s not an exciting task but it helps with the previous section. It’s easier to know what needs to go when a feature is removed. It is much preferred over the “rename it and see if anyone screams” approach. It’s also beneficial as your team grows for new hires to learn the database. This is often skipped in smaller environments, but it is much easier to document as it grows than to go back and document hundreds or even thousands of objects later.
Learn or Expand Your Toolbox
There are several options in SQL Server Management Studio that are not on by default but can improve quality of life. Want additional functionality? Check out an addon like SQL Prompt or SSMSBoost. Already well versed in SSMS? Check out Azure data studio and the Jupyter notebooks. Want to do more with PowerShell scripts? Check out the modules in dbatools.io. There are many tools out there and while some do have a cost, many are free.
Join The SQL Server Community Slack
There is a great place to network with other SQL Server professionals anytime and it’s completely free. Get an instant invite from the dbatools link and join the SQL Server Community Slack. It’s a great place for networking, learning, and discussing many topics regarding SQL Server, related tools, and random topics.
A Better Next Year
This is just a short list out of the many opportunities to improve your SQL Server environment for the coming year. Whether you are looking to improve your SQL Servers directly or your own skillset, your SQL Server environment will benefit from the positive changes that result. If you’d like a helping hand in identifying and resolving issues affecting the health and performance of your SQL Server, reach out to our team of experts at House of Brick today. Happy New Year everyone!
