Voice of the DBA

Author: way0utwest

  • Mastering Index Tuning–Day 2

    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:

    Day 2

    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.

  • How are you recharging this year?

    This is the end of the first half of the year. I’m hoping it’s been a good half year for you, as it has for me. I’ve traveled less, done a few more things outside of work, but had a good time at work as well. I’ve had to work on some projects and content that are engaging and fun. I’m looking forward to the next half of the year.

    No matter how much we enjoy our career, we all need to get away from work at some point. It’s important. We would love the perfect work-life balance, though I don’t know what that means or if it’s possible. You can make your job a little better taking some time to get away from work and getting refreshed and recharged. Today, I wanted to ask about your plans this year.

    How are you recharging?

    I just got back from a vacation with my wife. I had to travel for work and she came with me, putting up with a couple days of my work commitments before we took a few days to tour and enjoy the west coast of Ireland together. We are trying to do that more as my kids get older and more independent. We’ve taken a few trips over the last couple years and have another one planned later, when she’ll travel with me to Norway.

    It’s not just work trips. We also have a long family vacation planned in July, with two weeks of camping as we drive up to Glacier National Park and try to keep everyone mostly unwired. We’ll see how it goes, and certainly my middle son will need some connectivity as he’s in the middle of Linear Algebra and C++ programming classes, but hopefully the time will be minimal. Maybe I’ll get to watch him code some C++ and remember how glad I am that I don’t have to do that anymore.

    Let me know today if you have any plans for this year (or if you’ve already competed them). It’s always neat to hear the ways in which people look to spend their spare time. If you haven’t made any plans, I’d suggest you stop for a few minutes and think about how you might recharge away from work and try to get some plans in place.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Cloud Backup Comparison

    I used to use Crashplan. This was about $150 a year, but I could to 5 machines. I used to do 5. I had

    • My desktop
    • My wife’s laptop
    • My daughter’s laptop
    • My laptop
    • My son’s laptop

    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:

    • My desktop
    • My wife’s laptop

    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.

    The Choices

    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.

    Software

    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.

    Cloud Storage Pricing

    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
    Google $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.

    Pricing

    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
    Google $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.

    Using the Cloud

    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.

    Pricing

    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.

  • Learning Goals –June 2018 Update

    The June update for T-SQL Tuesday #97. I made a plan and it’s hard to stick to.

    tl;dr – Very little done.

    I traveled the first three weeks of June here and there. Two short trips from 1-2 and 8-10, then a long trip from 16-22 and holiday from 22-27. That means I had a class on June 4-6, with a webinar in there as well. That means I really had just June 7, 28, 29 to do anything. There was a little bit of time on the 18th and 19th, but really it’s been a busy work month, with time outside of  work being spent traveling or with family.

    Extended Events

    Nada.

    Well, maybe almost nada. I did do some testing of Jonathan Kehayias’ conversion script from trace to XE session. I started this in May, but finished off the article and rechecked the work, even building some load scripts to help test things. Article published on SQLServerCentral, so something done.

    Python

    Very little. I worked on some of Project Euler with my kid, getting a few more items done, but got stuck when my Prime script ended before the answer on number 7. I saved the work and answer, thinking I might look at it on the plane, but no go.

    I may try to get to a little today, as I’ll be offline for a bit getting my car looked at and writing.

    Overall

    Not a good career growth month, but a good month for my career as I attended 5 events, delivering 6 sessions. I also got most of a week of holiday, so we’ll call this a maintenance month.