Ola Hallengrens SQL Server Maintenance Solution is designed by Ola Hallengren, an MCITP database administrator and database developer, who has been working with SQL Server since 2001. The solution is specifically designed to perform maintenance tasks on any Microsoft SQL Server database and consists of backup, integrity check and maintenance of indexes and statistics. In this blog, we tell you what in our opinion are the seven benefits of this solution.
In brief: what is Ola Hallengren?
Ola Hallengren's solution is known and appreciated by all SQL Server DBAs around the world. He has created a very accessible solution that allows any database administrator to manage their environment efficiently and effectively. It consists of a number of "sub-solutions": database backup, integrity check, and maintenance of indexes and statistics. The solution is available for free on the Internet and also has a number of other advantages: it is fast, reduces the probability of error, uses a clear naming convention, is version-independent, is efficient, modular and integrates with other solutions. Let's zoom in on these advantages.
The speed at which Ola Hallengren's scripts can be brought into production in a new environment is unprecedented. Whereas a standard environment of about ten database servers would take you about two days to configure Microsoft SQL Server's built-in Maintenance Plan, Ola Hallengren's solution gets you up and running in five minutes. That alone provides huge gains for administrators.
Small chance of errors
Not only does the fact that the solution is scripted make it fast, it also dramatically lowers the chance of error. After all, work that is automated ensures that fewer mistakes can be made. Furthermore, Maintenance Solution's procedures can recognize common errors and automatically perform an appropriate remedial action. This eliminates the need to report the identified error. The administrator is left with the time for useful things!
Ola scripts also offer a specific naming convention. The result of the scripts - a log file or a backup - is stored with a name such that it can be easily understood by administrators. With standard solutions like Microsoft's, the administrator has to keep reinventing that wheel, whereas naming convention is a very important aspect of standardizing management processes. If another administrator comes in, for whatever reason, that person must also be able to understand the names easily. At Ola, for example, the name of a backup consists of the server name, the database name, the type of backup and the time of the backup. This makes it clear to the administrator at a glance what it is all about.
Database environments have grown over the years. That often means it consists of different versions of software, of different ages. Each of these requires a specific approach if you want to manage them with a standard solution. Maintenance Solution from Ola is version-independent and is periodically updated and improved. Because it is built in Transact-SQL it can be run on any version of Microsoft SQL Server, it works on software from years ago but just as well on a version released yesterday.
Ola's efficiency lies mainly in the way it optimizes indexes. It updates only the indexes that need it, that can benefit from it. Most other solutions update all indexes, regardless of optimization needs. Maintenance Solution from Ola first checks and analyzes which indexes need to be optimized and only then starts the actual optimization. This reduces management task turnaround time, reduces CPU utilization and disk I/O, which favors overall server performance. This comes in especially handy in cloud-driven solutions, such as Microsoft Azure or AWS environments, where costs depend on usage.
Scrips from Ola are modular. That means you can use only the parts that are needed. Of course integrity checking and backup belong together, after all, a backup with integrity errors isn't much use. But an administrator can choose to use, for example, the integrity check and someone else's backup solution. Or you use only Ola's database backup and integrity check and another software for index optimization.
In addition to Microsoft SQL Server, Maintenance Solution functionality from Ola integrates seamlessly with all other software solutions. It is possible to pick out a specific part of the maintenance solution and integrate it into your own environment, because the code is readable by any administrator. You can use the desired functionality to enrich and complement your existing management environment. In this way, you can take full advantage of the aforementioned benefits such as naming convention, version independence, error reduction, efficiency or modularity.
Of course, Ola Hallengren's solution is not a magic panacea. You still have to think for yourself, for example, about your disaster-recovery strategy and periodic performance maintenance. What steps should your organization take if your data is no longer usable or has been accidentally deleted? What type of backup do you make at what point, where do you store the backup files, at what point should you do an integrity check? What actions are there to promote overall database performance? Who performs the checks to identify these actions? Are such decisions defined in a policy? We can advise you, with all our experience, which management solution is best for your database environment and which functionalities you can best deploy.
Want to know more?
Are you curious what Ola Hallengren's solution can bring to your organization? Feel free to contact us without obligation! We would love to think along with you.