By: Jeremy Kadlec | Updated: 2007-11-26 | Comments | Related: More > DBA Best Practices
Problem
Blunders, mistakes, absent mindedness, being over extended - you name, it happens. Whether it happens to you or someone on your team, the repercussions can be severe. Over most people's careers you have seen issues to a varying degree of severity. To me the biggest blunder is knowing that a problem exists and either ignoring it or procrastinating on the implementing the resolution. Do not be upset when the problem strikes and you have to drop everything you are doing to fix an issue that could have been prevented. This tip focuses on SQL Server problems that could have been prevented. See if you can relate to any of these items.
Solution
The reality is that nothing is perfect and as technical professionals we need to build a realistic solution with the time and budget available, then communicate any potential issues to the business so they are aware of them. If the business feels the issue is too risky or unacceptable, then additional time and money can be allocated to develop and implement a solution so the business needs are met. Unfortunately, this small step is overlooked too often. The issues in this tip and others are a reality from not communicating the reality behind building and implementing an enterprise IT solution.
Below outlines problems that have recently been reported to our team. We have split these items into a development or operations category although some of the items overlap or are a cause for other issues. Check these out to see if you can relate to them and put safeguards in place to prevent these types of issues.
Development
- Internet Speed - We have heard the expression 'Internet Speed' by a number of people at different levels in the organization. What we have found is that this loosely translates into no requirements, no design and just jump into the coding. Typically this leads to a number of revisions and a major rush to push incomplete code out. In some environments this is the norm. At others as the organization matures, a more planned process is used to prevent a major maintenance nightmare with any small change that needs to be made. I know I have seen this issue and I work to nail down the business requirements before coding to prevent a significant amount of re-write time on short projects. Just think of the magnitude of changes on larger projects.
- All text all the time - Although the text data type can store a large amount of data, it is probably not the correct data type for 80% of your columns in most applications. The bottom line is to be sure to select the correct data type for the data that will be stored in the table and do not be surprised by the inappropriate data type resulting in performance issues.
- No error handling in your application - Users should not get messages that are bizarre to them and cause them to reduce their productivity or lose faith in the application. Consider the error handling scenarios as a portion of your design and be sure to build your own specification if one is not provided. Although it is impossible to think of all of the errors, at least having an elegant way to capture the error and communicate it to the users can go a long way.
- No load testing - Do not just build a whole new revision of your application where you change tables, indexes, stored procedures, views, functions, etc. and expect the system to continue to perform as it did before. You will be lucky to not have the SQL Server performance grind to a halt. Having one person test an application from a functional perspective could be reasonable, but having that same process qualify as load testing is not realistic unless it is a small system with few users, simple logic and a minimal amount of data.
- Dropping triggers to prevent performance issues - Triggers are created for a reason, whether they are for auditing, compliance, regulatory, reporting or internal business needs, they are built for a reason. As such, do not just drop the triggers because they are in your way during a deployment. With SQL Server 2005 triggers can either be enabled (or disabled) to prevent them from firing. Just be sure making that type of change does not violate your business rules.
- Connections open forever - Be sure to close your connections for your application. At some point, SQL Server can no longer accept connections. If you are lucky, you will be able to connect to the machine to shut down SQL Server cleanly during a reboot. Just by closing the connections will prevent SQL Server from running in a degraded manner as more and more connections are created. This small step can prevent unneeded downtime on systems that are expected to continuously run.
- Hammer vs. Nail Gun - Selecting the correct technology solution and implementing it in the correct tier of the application can be a challenge. With so many options to resolve a problem, build what is necessary with the time and budget available. It is better to have a functional and accurate application rather than having the prettiest interface with the latest and greatest .NET features without a database to support the application.
Operations
- All senior team members on vacation - When you have a major deployment make sure to have your key staff members on site and available to meet the project needs. Do not fool yourself that a junior team member will be able to just push a button and deploy a solution, especially when a minimal amount of testing is conducted. When a problem arises it is the senior team member's knowledge and expertise that is needed to quickly resolve issues. If all of those people are out on the beach and will be back next week, it makes sense to wait a week for the deployment to have your team onsite and available to address any unexpected issues.
- Putting all of your eggs in 1 basket - When you work through an enterprise upgrade whether it is an application or hardware firmware, do not upgrade all of the systems (including the DR site) at once. Take a step back and be sure to have some systems that are out of sync for a short period of time to be migrate to a known stable platform in case an unexpected issue arises.
- Not validating backups on a daily basis - If a serious issue occurs, make sure you have a solid last line of defense. That is a consistent and reliable set of backups on a daily basis. In addition, make sure your backup plan includes retiring tapes on a weekly, monthly or quarterly basis to be able to rollback to some point in time rather than going out of business. Also check-in with the business to ensure backups are not needed for legal or regulatory needs.
- Not changing passwords - As an administrator you have the keys to the kingdom and need to recognize the responsibility that you have. As such, make sure your passwords are complex, change them frequently and do not share your passwords. For the passwords that are shared with a number of individuals, change those passwords when a team member leaves. Disable the account for the person who left, revoke VPN access, etc.
- Password expiration - This is almost the opposite of the previous bullet. With SQL Server 2005 password policies can be setup for standard logins so the passwords expire and accounts get locked out. When this happens your applications will not be accessible if one of these accounts are in use. As such, setting password expiration is a good idea, just be sure to change the password and coordinate the change with your team. Do not have the policy take affect during prime time on Tuesday and then scramble to figure out what is going on.
- Letting the primary file group fill up - With the rate of data growth, be sure to either cap your database size and monitor the size on a daily, weekly or monthly basis or permit your databases to automatically grow. In either circumstance, be sure to watch your disk space so that you do not fill up your disk and then have 2 problems (full file group and full disk drive).
- Hot data centers - High temperatures mean failure for servers. The failure could be a controller card or a disk drive, but one sustained spike in the room temperature could be a critical problem that is not fully realized for a three to six month time period. Make sure your temperature is properly regulated, has a backup conditioning system and can notify your team before an issue arises.
Next Steps
- Since you have read this tip, think about the issues you have experienced in your career and try to not make the same mistake twice. Learning from your mistakes can be hard, painful and unpleasant, but is a way many people learn what not to do (again). So when you start your next project, be sure to have the same or similar safeguards in place based on the project needs.
- As you start your projects be sure to outline the issues of rushing the project, with the project sponsor, so they understand the repercussions. At some point you can only get so much work done in a day. That is just the reality and expectations need to be communicated in a professional manner so the business can move forward with a realistic plan.
- This is certainly not a comprehensive list of issues that have made the press or have been experienced in the trenches. Numerous security related blunders have made the mainstream news. Add your experiences\observations in the forums from issues that you have seen and experienced with the solution for the remainder of the community to benefit from in the long term.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-11-26
