No one ever said managing a database was easy. Last week we covered several steps that are often overlooked in database administration, this week we’re turning our attention to common database management missteps.
Mistakes happen. It’s a fact of life and business. Strong IT leaders identify the root cause, remediate those mistakes quickly, learn from them, and make sure to avoid them moving forward. But what about the mistakes you don’t know that you are making, and won’t know until they become a real problem for you and your organization?
If your database management is being pushed off as a secondary priority, or to a resource as a secondary responsibility, your organization might be falling victim to this scenario. And while on their own these mistakes may seem small, repeating them can lead to problems.
Here are a few database mistakes that we have come across while working with clients that can be avoided with a few simple steps.
Giving too many security privileges to individuals or processes.
When you or your database administrator is adding new users to the system, it’s easy to give everyone the same level of permissions, or role, but being deliberate at this stage is well worth the time. You should be giving users just enough access for what they need to do their jobs. Allowing too broad of access for users who don’t require it opens up a world of (likely inadvertent) missteps that could spell issues for your company.
You can make the task of database access management less tedious by setting up specific roles for each type of user, and providing the focused functionality that each of those roles requires; no more, no less. Yes, it sounds like micromanagement, but this is a place where it’s necessary. It is a little extra effort that can spare you big problems down the road.
Relying solely on complete environment backups.
Here we have another in-the-moment timesaver that could cause problems for your organization. On the surface, there is a certain appeal to relying only on complete environment backups. The thought being that you have everything covered, but here is the problem: If you do have to recover, you will have to recover/reboot EVERYTHING, including databases that weren’t affected, sending all of your data back to that latest backed up version.
The solution? Database-level backups aka system-level backs up. This will backup all of your databases, individually, allowing you to restore them individually without touching your other, unaffected databases. It’s an easy step that can save your company time, and improve your ability to deliver an improved recovery time objective.
Regularly shrinking databases to free up operating system space.
Step away from the shrink button! This is a truly baffling practice that needs to stop now. Shrinking databases is not the way you should be freeing up space in your system. There is no benefit. In fact, shrinking your databases can cause fragmentation and performance problems you can easily avoid. It’s not necessary, SQL Server knows the proper size, and the shrinking is hurting your database.
Before we get onto the solutions, a quick note on why a shrinking feature even exists, given it can be a detriment to your data. The feature does have its purposes in certain situations. For example, if you need to complete a large data cleanup that can justify shrinking the database to an appropriate size and releasing disc space to the operating system. Alternatively, in an emergency situation, if there’s not enough free disc space on the drive and more space can't be added in a timely manner, you may need to shrink a database. Another situation where you may need to shrink your database is if your autogrowth settings are too large and your database is growing out of control. But again, I remind you, proper planning can help you avoid all of these situations.
The best way to avoid even the mere temptation of this feature is to size your databases properly in the beginning. Track the growth trends of the databases and assess the amount of free space. There are settings that allow the database to grow in increments that are efficient and reduce fragmentation while keeping the performance optimal.
We all make mistakes, but it’s how we recover and respond to those mistakes that matters. If you have a member of your IT staff overseeing your databases as a secondary duty, you may be making these mistakes repeatedly and not even know it. If that’s the case, it may be time to call in a process driven expert, whether that’s introducing remote database management or simply starting with a database health check, ManageForce can help you get started to correcting those mistakes with a 15-minute (free!) consultation: Schedule it Here
And learn more about how regular database health checks can help keep your database management on track in this complimentary strategy brief: