For some of you, the headline is self-explanatory, and you just want the URL, so here it is: https://github.com/olahallengren/sql-server-maintenance-solution
For the rest of you, here’s what it means.
What are Ola Hallengren’s maintenance scripts?
Ola Hallengren’s maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are powerful and flexible. The whole thing is open sourced with the MIT License, which means you can use them at work or even bundle them with paid products. (For example, kCura ships a modified version with Relativity.)
Ola’s site has phenomenal documentation, but if you prefer video format, or if you just wanna see & hear Ola himself, here’s his session from the 2014 PASS Summit.
I highly recommend his scripts for backups and index maintenance.
Why do I care that they’re on Github?
If you use Ola’s scripts by downloading them from Ola.Hallengren.com, configure them once, and just rely on ’em, then you don’t care. You can keep using them exactly the same way – and for most people, that’s the right answer. Ola’s download process is a piece of cake and his documentation is great.
But 3 kinds of people care:
- People who modify the scripts for their own use, but want to sync their versions with Ola’s latest changes
- People who want to give their own code to Ola, but want to make it as easy as possible for him to see the differences, and decide whether to put their code in his main branch
- People who deploy servers via automation scripts, and want to just fetch Ola’s latest version from Github automatically during deployment
If you’re not in one of those 3 categories, ignore this blog post. You simply don’t need to do anything at all. I can’t emphasize this enough – the stuff I’m about to describe isn’t intuitive or straightforward for most database professionals. Just go on about your day.
But in the Faux PaaS project, we happen to fall in all 3 categories – more about that soon – so Scott Ellis and I asked Ola if he’d be willing to host his scripts in Github. (The way they’re licensed, we could have hosted them ourselves, but I believe Ola should still be the Commander in Chief for these. He’s got the best vision for what makes them successful.)
How do I get started with Github?
- Create a free account
- Go to the sql-server-maintenance-solution repository (aka repo, which means software project, basically)
- At the top right, click Watch, Star, or Fork depending on what you wanna do:
Watch means you’re going to get an email every time he makes a change.
Star is like bookmarking it as one of your favorite Github repos. For example, here’s my starred repos.
Fork means create a copy of Ola’s scripts in your own Github account. It’s kinda like clicking File, Save As, and putting the project in your own home directory. For example, I’ve forked Ola’s repo, so it shows up in my own Github profile, but I’m only linking to it so I can explain how this will look on your own account.
How do I make changes to Ola’s scripts?
The easy way is to simply contact Ola. That works just as well as it ever has – he’s really responsive for a guy who must get a gazillion emails, especially all the thank-you emails that you folks are sending him because you rely on his work every day to save your job. (You DO send him thank-you emails, right? Right?)
Right now, Ola’s using Github as a mirror for his download page. It’s not his main development workflow. However, if you, dear reader, are an excellent open source citizen and you do a great job of submitting clean, easy-to-test pull requests, we might be able to make his life easier with Github.
If you haven’t used Github at first, seriously, stop here. It’s not easy or intuitive. I’m going to explain the big picture only to talk about why it’s tougher than emailing Ola directly.
Github is confusing at first. On Ola’s repo, there’s a “Clone or download” button, and if you install the Github app on your machine, it’ll open and let you edit Ola’s scripts. However, when you try to do submit your changes to Ola, you’ll get permission-denied errors.
In summary, you have to:
- Fork Ola’s repo into your own account (so you’re working independently)
- Create a branch for the specific change you want to give back
- Check your code into your branch in your repo
- Submit a pull request back to Ola explaining what you changed, and why
This stuff is way, way harder than it looks. Here’s learning resources to get started:
- An Intro to Git and GitHub for Beginners by Meghan Nelson
- How the Heck Do I Use Github? by Adam Dachis
- Fork a Repo by Github
After you step through this grind – and I’m not gonna lie, it took me months to get vaguely comfortable with Github – you might have a little bit of a grumpy attitude. (I certainly did.)
As a result, when I checked in changes to somebody else’s stuff, my pull requests had an attitude. I had put in so much work that I felt like I was doing the code author a favor. “Look,” I said, “I’ve gone through all this work to give you my code – the least you can do is just click Merge to accept my changes.”
Don’t be that person.
If already you’re a Github pro, great – but then again, you probably stopped at the first line of the post. If you’re new to Github, I just wanted to explain why the rest of us are excited. You don’t need to use Github to be a good DBA. But if you do start down the Github path, keep an upbeat, thankful attitude because the code authors on the other side are doing this in their spare time for the love of the community.
And take a moment to thank Ola.
He’s at firstname.lastname@example.org, and he’s been making DBAs’ lives easier for almost ten years. Now, he’s taking things to the next level again, and that’s awesome.
And if you run into Ola at a conference, Scott tells me Ola’s a fan of Johnnie Walker Blue Label, so buy him some.
That’s a really cool idea. Hat’s off again to Ola for making it easy for people to do the all important things that a lot of people either don’t do or aren’t aware of.
As for index maintenance scripts, I’m very happy to report that the great experiment continues on all of my dev, stage, and prod boxes… I’ve not done any index maintenance on any of the databases since 17 Jan 2016 (a year and a half now) and the systems have suffered no ill effects. As I’ve posted elsewhere, performance actually got better over the first 3 months due to the “natural Fill Factors” that each index developed as they were inserted/updated. I have, however, become absolutely religious about rebuilding stats on the boxes and, with the help of some friends, have developed a system that keeps track of stats that have been used and will use that as a springboard to delete stats that aren’t used. For example, there are more than 17,000 stats on one of my databases (bad legacy maintenance plan not of my design built stats on every bloody column of every table). It takes almost 13 hours just to rebuild the stats that have changed just for that one database. Over the last 3 weeks, it turns out that there are only about 8,000 stats being used for ALL of the databases on my 2+TB prod system. That can lead to huge problems when auto-stats kicks in on one of the larger tables. And, yeah… putting an article together on all of this.
Awww, thanks sir! I’m looking forward to seeing the post on that, too. Oh wow, how do you know which stats aren’t used?