Voice of the DBA

Tag: syndicated

  • 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.

  • 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.

  • Mastering Index Tuning–Day 1

    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. I started with the Mastering Index Tuning class.

    I took the class a few weeks ago, but I scheduled this out for when I returned from vacation.

    A Late Start

    The classes started at 9am Eastern time. That’s 7am for me, which is a touch early. I’m usually awake, but not always at my desk. In this case, I was hustling a bit to get through a morning routine, including a shower since I had a meeting during the day.

    I managed to get to my desk and start the GoToWebinar and found a welcome slide up that noted the class will start at 5 minutes after. That’s cool since it meant I could go start coffee without missing anything.

    This also gave me a chance to switch to speakers for the audio. I’m at home, and have my own office, so I was glad to not wear a headset all day. We were on mute, but I muted myself with a hard button, just to be sure there weren’t any disturbances from my end.

    Setup

    I’d gotten the email the night before with a lab VM and credentials. After getting coffee going (priorities are important), I came back, logged in and fired up SSMS, leaving things alone.

    I also popped open the webinar and Slack. That’s 3 things going on at once. Fortunately, I have three monitors, so this worked out well. I put the VM in front of me, since it’s always easier to concentrate straight ahead. The left monitor got Brent and the GoToWebinar. That meant a full screen for slides and a small area for the webcam Brent runs. I find it easier to follow along if I can see someone. I then put Slack up on the right screen, set to the channel for the classes.

    I think that taking this on a laptop would be tougher, but doable. This is especially true if you are watching the recorded items. There isn’t a rapid movement of slides, so you could focus on Slack and listen, or watch and come back to the VM when appropriate. I followed along a few times with Brent, so I was glad for the multiple monitors.

    The Day 1 Flow

    The class opens with introductions of Brent and a few people mentioned from Slack. He does orient us to remember to open Slack for questions and discussion. One nice thing here is there isn’t any time spent chatting with each other, or a limited amount in Slack.

    We introduce the VM and how it’s configured, along with some explanation. It might feel unnecessary, but I expect that most people have questions or would have done one thing differently. Across the class, we each might think about one different thing, so it’s good. I like that Brent also warns us what not to touch, since it’s easy to blow things up. We also start up a workload with SQLQueryStress before a break.

    The first thing is a lecture about how to use indexes and some demos. After some basics, we get into how we get information from the DMVs and elsewhere, and Brent uses various sp_BlitzXX scripts. I think that’s good as he’s put time into these scripts to make them helpful. They’re not necessarily better than those you might build yourself, or get from other speakers, but they are useful.

    We have a first mini lab, which we work on and then Brent reviews what he’d do. Then a little more lecture before a lunch lab. In this one, we have to analyze and post our proposed changes in Slack. Brent then acts as a manager to approve changes.

    I like this, and it’s a good way to sanity check yourself without just doing something. The lab is a 2 hour lab, but it’s designed as a lunch break as well. Good for some, but it’s 9:30ish for me. That’s early. Still, I do the lab and then spend time on other work.

    After lunch, we review the lab with Brent, watch what he does. That’s good, and it helps people learn, without picking on their solution. The afternoon passes with more lecture and then a final lab that we can tackle then or do in the morning. Since my time is offset and the class starts at 7, I decide to just do it then. Again, propose changes and get them approved.

    One nice thing is that Brent is watching Slack while presenting, so he can respond to comments or questions inline with what he’s teaching. This also makes for some fun distractions with comments and questions, and things aren’t scripted. Brent will pop something open in a browser to teach.

    A long day, made longer since I still need to do some SQLServerCentral stuff, but it was 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.