Have you ever thought about what it would be like to have a super power? If so, what super power would you like to have; super strength, super speed, the ability to fly, invisibility, or something else? Well, if you take the appropriate steps to be proactive in managing your SQL Servers, you can almost attain invisibility, at least to those who are using your systems on a daily basis.
I have always joked that no one ever calls me to tell me everything is running fine. Usually, when I receive a call, it is because something is not working properly. This begins a series of reactive steps to identify the problem, determine, test, and implement the solution. Sometimes the solution is very simple and the issue can be easily resolved. Other times, the issue can be very complex and take a long time to resolve.
Even when a solution is found, getting everything prepared, and approved, to make a change to a production system can be very difficult. You certainly don’t want to make a change to production that corrects one problem only to introduce more problems. During the troubleshooting process, you are probably getting swamped with emails and phone calls from end users, your boss, and anyone else that has been contacted because someone thinks the issue is not being resolved fast enough. We all know that it is very hard to get work done when people are looking over your shoulder or are in your ear.
If, however, you take the appropriate steps to be proactive in your SQL Server management, you can minimize the number of calls you get about your system. The fewer calls you get, the more invisible you become.
This article is not meant to be a technical list of the methods you can use to properly manage your SQL Servers. The purpose is simply to make you aware of the general ideas you need to consider. There are a number of things you can do make sure your system runs as smoothly as possible. Here are a few:
- Be involved with the SQL Server build
- Be involved with the database design
- Work with your developers
- Setup maintenance procedures
- Collect performance data
- Set up alerts
- Get to know your SQL Server and how it works
- Identify the little issues and prevent them from becoming big issues
- Never stop learning
Be Involved with the Server Build
If you are having a new server built, be a part of it. Depending on how your IT department is setup, this may be easier said than done. Your department may have a standard template that is applied to all servers, and your options may be limited. Because of the separation of duties, you may not be directly involved in the setup of your new server. Whatever the case, make it a point to get in touch with whoever is building your server and work with them to make sure those things you can influence are set up properly. If you are adding databases to an existing server, verify the existing resources to make sure they will meet the additional needs of a new database.
A few key things you will want to make sure are set up properly are disk configuration, CPU, and memory. For the disk configuration, if possible, you will want to break out your disk’s configuration into separate drives to achieve maximum performance. If your company uses a SAN, you may be limited in your ability to configure the underlying disks, but it is not a bad idea to create a logical division of drives. Although the drives may all be on the same physical SAN, you can have a separate drive for the data files, log files, and TempDB files. You will also want to make sure the drives are large enough to handle the initial data load and growth for a reasonable amount of time.
Your CPU needs will vary greatly depending on the purpose of your SQL Server. If your server is going to have a large number of connections, or is going to be used for processes such as data warehousing, you may want a higher number of CPUs. If you are unsure, and if you have the ability to easily add more CPUs, start with a lower number and add more as needed. You should be able to quickly identify that need with the proper monitoring.
Make sure you have enough memory! SQL Server loves memory and you can almost never have enough. There is no hard and fast rule on the amount of memory you need, but more is almost always better. You will have to estimate how much data your database(s) will have, the amount of activity, and the number of users. Based on these estimates, and the limits on memory for the SQL Server version you are running, get as much memory as you can. There are tons of articles available to help you really deep dive into how much memory to give to SQL Server.
Be Involved with Database Design
Get things setup right from the start by making sure the database is designed properly. Depending on your IT setup, any number of people may be responsible for the database design. It may be the DBA, the developer, or someone whose primary job responsibility is database design. Whatever your case, stay in contact with those individuals responsible for the design. The exchange of ideas and different thoughts will be beneficial and should lead to better designs.
When working on the database design make sure you know what is needed. Not having a good idea of the system requirements from the beginning is detrimental. Once you have the requirements, try to incorporate some standards. This will make development, maintenance, and troubleshooting much easier.
Be sure to include proper primary keys, and unique constraints to ensure consistent data. Make sure the database is properly normalized to reduce data redundancy. Create proper data relationships to ensure data uniformity. Be sure to create proper indexes. All of these things will increase overall database performance and make the administration of the database a much easier process.
Work with Your Developers
You must work with your developers to ensure proper database performance. You want to help by making sure good programming practices are being followed to provide for easy maintenance and optimal database performance. This is not a one-time process and it is not a one-way street. You need to maintain an open line of communication with the developers. You should also be open to ideas from your developers. As administrators, we may not always be aware of new or better development ideas or solutions. Because of the developers’ daily work, they may be more likely to find new and innovative ways to program. Learn from the developers for your own knowledge and to be able to pass that knowledge on to other developers and administrators.
There are countless documents concerning best practices for database programming. Some key points to keep in mind in any database programming should be in-code comments, programming standards for your company, standardized formatting for maintainability, and proper error handling. All of these things are beneficial to maintenance for both the original developer and someone new.
Another thing to keep in mind, and pass along to developers, is that “new” does not always mean “better”. Database systems are constantly changing and the tools available in these systems are forever changing. Many times we will find a new “trendy” way to perform a task and, too often, that becomes the only way we perform that particular task. It is important to know how the system works and know that sometimes there is more than one way to achieve a goal. The only real way to know which method is best is to test each method.
Set Up Maintenance Procedures
This step is crucial. At the very least, set up a good backup process, ensure database statistics are kept up to date, and use some kind of index maintenance\optimization procedures. There are no hard and fast rules on how these should be setup because every system is different. Implement them based on the best information you have about the system, then monitor them to determine if any modifications are needed.
For your backup procedures, make sure you are running regular backups and that these backups are accessible in a timely manner that meets your company’s needs and requirements. If you need point-in-time recovery for a database, set it up in FULL recovery model and make sure your backup procedure includes backing up transaction log files at the interval required. Also, and this is of utmost importance, test your backups. Test your backups by restoring them somewhere to ensure the backups are good and that both your backup and restore procedures are working properly.
Depending on your database(s)’ activity, set up some procedures that ensure database statistics are kept up-to-date. The more that data changes in your database, the more often this will need to be done. Out-of-date statistics can greatly impact query performance.
Keeping your indexes in good shape is also critical for optimal performance. The frequency with which you employ your index maintenance procedure will depend on the database activity. The type of procedure to be performed, such as reorganizing or rebuilding indexes, will also have to be determined. There will probably be a combination of both and you will need to decide when these procedures should be done. Keep in mind that index maintenance is an intensive process, so it is best to schedule it during times of lower database usage.
Just like everything else, there is a ton of information on how to perform these maintenance procedures, as well as tools you can buy that will help you set these up. If you want a head start in setting up your own procedures, I would recommend visiting https://ola.hallengren.com/ for some excellent information and examples.
Collect Performance Data
This is, unfortunately, an often overlooked area of database administration. Collecting performance data is invaluable in identifying potential problems and being able to address them before they become real problems (and you start getting those dreaded phone calls).
SQL Server gives you the ability to create your own collection procedures through the use of Dynamic Management Views (DMVs), which were introduced with SQL Server 2005. They were introduced to be used instead of querying system tables directly and the number of DMVs continues to grow with each new release of SQL Server. These DMVs are simply views that you can query just like any view you create. These views, however, contain worlds of information about what is going on inside your SQL Server. Some DMVs apply to the entire SQL Server and some are specific to each database. They are all part of the sys schema and they all start with dm_.
Using these views, you can set up jobs that periodically collect information on things such as sessions, connections, query execution, I/O information, OS information, and database information, just to name a few. Storing this information will give you a history of performance and you can use this data to perform ad-hoc queries for troubleshooting or creating reports for periodic review.
Of course there is always the option, if you have some money, to purchase applications that do most of the dirty work for you and provide a ton of pretty reports, graphs, and almost anything else you need. Also, all of those applications provide some level of customization so you can tailor the monitoring to your specific needs.
Set Up Alerts
At this point you have helped design and build the server and the databases. You have also set up procedures to perform routine maintenance on your system and to collect performance data. All of that is great, but now you need to setup some sort of alert system to notify you about any problems or anomalies in your system. At the very least, you will want to know if any of your procedures fail. You may also set up alerts to notify you if certain data has fallen outside of acceptable ranges so you can review your data for any possible issues.
To set up alerts for anything being run through a SQL Server Agent job, simply use the Notification portion of the job setup. You can set these jobs up to e-mail, page, perform a net send, or simply write to the event log.
To set up alerts within a stored procedure, take advantage of the Database Mail function in SQL Server. Once configured, you can use the Database Mail procedures to send e-mails formatted in just about any way you want, including sending the results of queries within the e-mail.
If you are using a purchased application, the application will provide some method of notifying you of any alerts. Most likely it will allow you to configure thresholds for alerts and let you decide how these alerts will be sent, based on their severity.
Get to Know Your SQL Server and How It Works
Get to know your system and how it works so you can identify those things that are out of the norm. If you have the luxury of starting this on a brand new server, you can get some really good baseline data that will provide you a complete history of the server and will make it very easy to identify the impact of new databases, processes, or outside influences, on the server. Even if you don’t have the luxury of starting with a new server, you can still use the suggestions that have been mentioned in this article and learn how your system performs. Over time, it will become easier to identify those anomalies.
Identify the Little Issues and Prevent Them from Becoming Big Issues
Now that you have incorporated all of the maintenance, monitoring, alerting, and learning, use that information. Don’t just collect the information and never access it. Don’t ignore the alerts. Use all of this information to find any issues before the end user finds them. This will allow you to take the appropriate steps to prevent a larger issue.
If you find any of the information you are collecting, or procedures you are running, are not useful, change them or remove them. Database administration is not a one-time thing. Database administration is very fluid and nothing stays the same.
Never Stop Llearning
The most important piece of advice I feel like I could give to any DBA, new or experienced, is to never stop learning. Technology changes so fast that it is impossible to keep up with everything, but always keep an eye out for what is new and what has changed. Pick one or two things at a time and dig into them.
Take care of these things and you are well on your way to becoming an invisible DBA – in a good way.
Finally, although invisibility is a good thing when it comes to your end users, it is not necessarily good to be invisible to everyone. Stay in touch with others in your IT department. Maintain a strong relationship with your database designers and developers. Make sure they have everything they need from you and let them know you are there to help them. Also, stay in touch with your manager(s). Keep them informed of what you are doing. If you’re really good at keeping your systems running smooth, then just like your end users, they may not think about you or your systems very often and they may not realize the hard work it takes to keep the systems running that way.
