I have a new piece published over at the Redgate Hub: Building Better Test Data with SQL Provision. Part of my job is helping people learn to use our products better, and provide not only solutions to issues, but also ideas that might help them think about new ways to use products.
I’m a big believer in testing and having good test cases in your data. This piece gives a technique I’ve used in the past to ensure we test everything we need.
This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went.
This post looks at the Mastering Index Tuning class. Other posts are:
As with Day 2, we begin with reviewing the labs from yesterday. These were harder labs, and Brent spent time looking at how he solved the labs, referencing parts of solutions some people had. This took awhile, with a break in the middle.
As usual, we can ask questions and discuss the solutions in Slack, which Brent keeps an eye on.
We start the lectures with artisanal food, which Brent does enjoy. Hand crafted items from the chef, which felt funny since my car killed something and left an organ of some sort in the bathroom.
The analogy is that there are artisanal indexes, like those on computed columns, indexed views, and filtered indexes. These are items that can help in specific situations, but in general we don’t want to use them.
I like that Brent brings in the experience they’ve had with clients, noting that some of these features don’t work well.
The afternoon lab is fix some really bad reporting procedures with indexes (regular or artisanal) or changing code. I know I can’t always change code in databases, but this gives us a chance to try things. I ended up changing some code, but not much. The lab review after lunch was interesting, as Brent had a strange result with the last proc. Looking forward to seeing his debugging of this later.
The lecture after lunch moves to the end of D.E.A.T.H, heaps. I hate heaps, so this was interesting. Brent agrees with me, you really need a CI on the table. Maybe there are some reasons to not use one in a situation, but most of you need to just add a key.
The last part of the afternoon looks at the impact of CIs and then constraints and FKs. The CI part is interesting. I see lots of people talking about how to decide on this. I tend to lean towards Brent’s view, which he’s presented on and it’s in the class. Take the class if you want to learn (I don’t want to republish here).
For FKs/constraints, the module had lots of discussion. People think about FKs in interesting ways. I’ll have to re-watch this as I got busy in the middle with other stuff and missed some lecture.
The final lab is a big one. Use all the skills from the three days of the class. Restore the db, run a setup that messes up indexes, then fix things. It was a challenge, and I burned about 12 minutes deduping and eliminating indexes, then about 20 coming up with more to add. The creation took quite some time, so I never really got around to tuning, and since this is only part of my day, I had to stop. I do have some real work to do.
The final lab solution goes up the day after, and what Brent came up with was interesting. I liked watching the videos later to see how he approached the issues and solved them. I like that there wasn’t “one” solution, and he talks about how we might solve the lab that would be different than production.
That’s important, and it’s something that I appreciated in this class. I know better, but it’s always good to be reminded that the class is a game, a model of what could happen, but in the real world, these are just tools that might help, but could hurt. Judgment is still needed.
One thing I like about this class, which I’ve missed in some live classes, is that I can re watch sections of the class later. The class page has a list of all the lectures and labs, with each containing a video. Some might be from my class, some from previous ones. Since this is delivered and recorded in a modular fashion, Brent can update sections over time.
I went back to watch the first Artisanal index module, as I was distracted that morning by something at work. That was a nice benefit.
This was a great class. I haven’t been to a real class across multiple days in awhile, and I think the format of some lecture, a lab (with interactivity), and then a review of the lab, was great.
The lectures were interesting, and I learned a few things. The labs were challenging, designed to force you to work within constraints to tune something. Indexing is often a place where you can make changes and rapidly affect your system. The effects could be good or bad, so you need to be sure you are proceeding in a methodical fashion and also capturing metrics on the changes.
If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.
This is a short series of posts on the courses I took with Brent Ozar. I actually completed the courses in the past, but I wrote notes and wanted to revisit the way things went.
This post looks at the Mastering Index Tuning class. Other posts are:
The day starts by looking at homework from last lab. How does Brent do this? One nice thing is that Brent limits the time here for himself. He solves the indexing lab, but stops before some people would. He explains this as he tackles indexing like this. Make some changes, but set a time limit. Then see deploy them and evaluate again after some time.
We get to watch how he’d solve the lab, and I popped open my VM to check what I’d done. After all, it had been like 16 hours. I saved each lab work in a file on my VM, which was good. I could reference the order in which I’d done things and since I’d save the before/after stats, I could compare with Brent.
The rest of the day was similar to day 1, with a lecture, a short lab, more lecture, and a long lab over lunch. During this day, we looked at blocking, which is one of those areas where many people have issues. Brent has built a lab that creates blocking, so we can see it happening in our instances.
While there are different ways you can clear blocking, the challenge here is to use indexes to get rid of blocking. This isn’t the best way to clear blocking, but it is an option, and again, the challenge here is to focus on indexes.
This was a better day for me, getting into the swing and rhythm of the class as well as starting to feel challenged. I focused more on the labs here, which I wish I’d done a bit more of this on the first day.
The lab at the end of the day was more complex, though I didn’t have time to get it all done. I got a first pass that seemed to solve some of the issues, and I had to stop since other work was calling. Still, a good day.
If you’re interested in the class, you can visit the Mastering Index Tuning page to learn more and purchase the class.
I used to use Crashplan. This was about $150 a year, but I could to 5 machines. I used to do 5. I had
This changed as my boy decided he didn’t like his data with ours. My laptop died and had to be rebuilt, so I use OneDrive/Dropbox for stuff I need and assume everything else will die. I essentially keep nothing on the laptop I need. My daughter has also gone to the cloud for things she cares about, so we’re down to:
I’m going to assume 2TB of storage needed. I’m sure I have > 1TB already and I’m not taking less pictures.
This post looks at choices and evaluation of the options. Your process may be different, but hopefully this helps.
I asked for recommendations and got these.
These are a combination of software and services. The software will just back up your machine, but you need to arrange storage separately. The service backs up your data to some vendor that manages it, which is what Crashplan did.
I’m torn here. I’m not sure which I want. As much as I’d like the control, I also like the convenience, especially for my wife. I don’t want to be helping her find files on S3 or the Google Cloud and restore them. While she’s technically savvy, she doesn’t want a hassle here.
Let’s break them down first.
There are two choices that are software: Arq and Cloudberry. These systems work by running on your machine as a process and performing a backup at regular intervals. It’s essentially a server software, but one that runs on your desktop or laptop.
I looked at Cloudberry years ago, as they have a SQL Server module that will move your database backups to the cloud.
Arq Backup was recommended by a few friends, and it has some nice features. It keeps multiple versions of files, and backs up whatever you want, as long as your computer can see it. Arq works with Amazon cloud, S3, Glacier, Backblaze B2, Google Cloud storage, Dropbox, basically anything. Arq also lets you hold encryption keys, so you have control of your data.
If you use your own software, then you need to pay for storage separately. Since I have a lot of images and video, I need lots of space. If I look, I see these vendors as choices for me:
The cheapest storage isn’t really quick access or online. It’s colder storage that can be slow to restore. That’s fine. It’s what I need. All of the storage tends to be /GB/mo, so let’s look at 2TB as a round number. I have close to that in pictures and video now. If I look at this, I get:
| Provider | Monthly cost 2TB | Ingress |
| $14 | Free | |
| Glacier | $8 | Free |
| Backblaze | $10 | Free |
This is similar. Egress costs money, but the first few requests are low, so that’s not a big deal. In a crisis, I can spread out retrieval.
Arq costs $50 per machine, so that’s $100 for me.
Cloudberry costs $49.99/user, so that’s $99.98. However, this is for a 1TB limit. If I want to go to 4TB, then I’m $300/user or some complex, move stuff from one machine to the other.
Cloudberry is out here. They manage the files in the storage (as an image), and they limit this to 1TB.
If I were to store 2TB with Arq, I’m looking at this for a 1 year cost:
| Provider | Monthly cost 2TB | Total, software + 12 months |
| $14 | $268 | |
| Glacier | $8 | $196 |
| Backblaze | $10 | $220 |
These aren’t bad, and having Backblaze use on line, not cold storage is tempting. So far, Arq + Backblaze is running.
I guess they’re all the cloud, but two of these are service providers. Carbonite and BackBlaze, offer a service. I setup an agent on my machine, it backs things up, manages storage, and I just pay the vendor. That’s what I have with Crashplan and I’ve been happy. Let’s look at these two.
I used Carbonite at one point. They are popular, lots of people like them, and they offer all the items I’d get from the software. They say unlimited space, so that’s good, and they offer encryption. I’m dependent on them because I don’t get external access to the files, just through their interface.
What I dislike is they try to make the service simple, but they don’t provide a lot of details. I feel like I’m running around, trying to understand more about the service. That’s annoying.
Backblaze is similar, offering a plan with unlimited backup, and I assume, their own storage location. I can get a zip download for restore, or a flash or hard drive mailed. The HDD can be up to 4TB. That’s cool. It’s $200, but still. Backblaze also offers a “missing or stolen” computer location. That’s interesting. I can also file share items that I’ve backed up.
At first glance, Backblaze is better.
Carbonite has a deal with people leaving Crashplan. They’ll move you over for $30/year rather than the normal $72.
Backblaze has no real offer, though in a blog post they say I can try this for free. After that, it’s $5/month or $50/yr.
Let’s compare:
<
| Provider | Monthly cost 2TB | Total, software + first 12 months | 2nd Year | Two year total |
| Arq + Google | $14 | $268 | $168 | $436 |
| Arq + Glacier | $8 | $196 | $96 | $292 |
| Arq + Backblaze B2 | $10 | $220 | $120 | $340 |
| Backblaze Backup | $10 (2 users) | $100 (discount) | $100 | $200 |
| Carbonite | $5 (2 users) | $60 (discount) | $144 | $204 |
In this scenario, if I look, I see Backblaze as the cheapest option, and potentially the cheapest over time. It also has the advantage of convenience.
I’m going to go with Backblaze and set up an account. I get a 15 day trial, and I’ll see how things go during this time. I plan to let the backup run, also do some restores, and some file shares to see how things are working. I’ll also set up my wife and then report back.
Voice of the DBA 