Direct naar content

Ola Hallengren What Why and How

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.

Dimitri Choustov

DBA Consultant en Senior Database Reliability Engineer
Dimitri Choustov - DBA Consultant en Senior Database Reliability Engineer

In short, what is Ola Hallengren?

Swede 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 his 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 for a moment.

Speed

The speed with 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 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 benefits for administrators.

Small chance of errors

Not only does the fact that the solution is scripted ensure that it is fast, it also dramatically lowers the chance of error. After all, work that is automated ensures that fewer errors 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!

Naming convention

Ola scripts also provide a specific naming convention. The result of the scripts – a log file or a backup – is stored with a name that is easy for administrators to understand. 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 is involved.

Version independent

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.

Efficiency

Ola’s efficiency is 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 the optimization need. 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 is especially useful in cloud-driven solutions, such as Microsoft Azure or AWS environments, where costs depend on usage.

Modularity

Scrips from Ola are modular. That means you can use only those parts that are needed. Of course integrity checking and backup belong together, after all, a backup with integrity errors is of little use to you. But an administrator can choose to use integrity checking and someone else’s backup solution. Or use only the database backup and integrity checking of Ola and another software for index optimization.

Integration

In addition to Microsoft SQL Server, the functionality of Maintenance Solution from Ola integrates seamlessly with all other software solutions. It is possible to pick out a specific part of Maintenance Solution and integrate it into your own environment, as the code is readable by any administrator.

You can use the desired functionality to enrich and complement your existing management environment. This allows you to take full advantage of the aforementioned benefits such as naming convention, version independence, error reduction, efficiency or modularity.

Opportunities

Of course, Ola Hallengren’s solution is not a magic panacea. You still have to think about your disaster-recovery strategy and periodic performance maintenance, for example. 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! Feel free to contact us.