Voice of the DBA

Tag: Database Weekly

  • Always Check on the Basics

    I’ve been working with SQL Server for a long time, and one of the things I’ve learned is to not assume others view the platform and its administration needs in the same way that I do. I have usually started examining new instances with the same skepticism I’d use if my Mom told me she’d installed the software. I’m sure she could do it, and likely use some wizard and Google to get some backup scheme implemented, but I don’t know that it would be the schema I’d want to use.

    This week I noticed a piece from Lori Brown, of SQLRx, which talked about a few basic settings that I’d always want running on my systems. One of these is the CHECKSUM setting. It’s a checkbox in the SSMS dialog, and an option in T-SQL. Most third party tools, like SQL Backup Pro, include similar settings. To me, this ought not to be a setting, but rather a default that always runs. NO_CHECKSUM is the default, which is silly in 2019.

    In any case, I’ve seen more than a few presentations on the backup process in SQL Server. They always seem to be beginner sessions, always have more people than I expect, and remind me that this process, which is solid and stable, still has a lot that people don’t think about. There are certainly nuances to performing backups, and restores, in a manner that doesn’t generate any RGEs.

    I don’t usually use the VERIFYONLY option, as to me the file isn’t really tested until it’s restore. This is one reason I recommend having a process to regularly restore your backup files on a test system. Not for use, though you can certainly use them, but more just to ensure your file system, your storage network, all the hardware involved hasn’t caused any issues with the backup file. If you build a server for this process, make sure you add enough RAM, as someone recently learned.

    My feeling is that backup and restore is the most critical aspect of managing your SQL Server instances. This is the first thing I get working, and the number one ongoing concern I have to ensuring data is available. Second would be security, and everything else follows from there, but having a solid backup and restore process is the foundation of all other system administration.

    There are lots of ways you can learn more. We have articles, a free ebook, and more at SQLServerCentral. The best way, however, is what Lori has done. Do some testing. Run through some scenarios, check how long things take in your environment, and ensure that your backups are capable of meeting the RTO and RPO needs of your organization.

    Steve Jones

  • Lots of data in RAM

    I remember buying my first computer after college. At the time I was working part time and in graduate studies part time. My aging high school era Apple II wasn’t capable of handling the load, not to mention its 300baud modem wasn’t a lot of fun. I could likely have gotten a faster modem with a serial connection, but most of the modern Internet providers wanted Windows or a Macintosh OS.

    I decided to just spend the money and get a computer. Being a bit of a geek, I wanted to customize things a bit and get the most bang for my buck. At that time, I paged through Computer Shopper, looking for deals on parts and trying to find the cheapest 486 CPU I could. I made various choices, one of which was the RAM. I remember thinking hard at that time about whether I could get by with 2 1MB modules or did I need to go ahead and max out the motherboard and get 4. These days I think about GB, not MB, and 2 or 4 isn’t a good number.

    One of the first servers I built at one job was with 8MB chips, getting 32MB into a machine, which felt like a lot. Certainly more than I’d ever had in a computer before. I also remember helping write a PO for a Netware server that cost US$250,000, one with 256MB of RAM. It was the size of a washing machine and filled with 32MB HDDs, making it by far the largest machine I’d seen to date.

    That’s a nostalgic view back to 1992, when I was working on my CNE certification. I always think about that large, very expensive machine when I see some leap of hardware. This past week, it was a DD4 Gen-Z memory module that has 256GB of RAM in a stick. That’s not only a larger scalar value than the RAM sticks I started working with, but it’s an order of magnitude more memory. Imagine 4 of these sticks giving you a terabyte of RAM in a desktop. Who among us wouldn’t want this for our desktop SQL Server development?

    This level of tech isn’t ready for most of us, but it’s not that far off. 32GB laptops aren’t common, but they are easy to find. You can even get 64GB of RAM now if you want to. How long before we see 128GB+ desktop and laptop machines? I have no idea, as we seem to be in a bit of a hardware lull. Most developers run 8GB or 16GB, and it seems like this has been the standard for quite a few years.

    I don’t know if we’ll start to see some vendors pushing to add more RAM. Certainly the cloud has changed things with lots of processing and storage occurring off the local system. At this point I’d expect that three years after buying my last laptop, I’d be able to get 32GB in the same form factor for the price I paid a few years ago, but that’s not the case. The machine supports 32GB, but it’s pricey, the same price it was 3 years ago.

    I wonder how many of you are still power hungry, wanting more powerful machines. Or has the level of performance you get from your i7 on Windows or OSX been good enough the last few years? I think this machine works great for me, whether running VMs or containers. 16GB does the job well, and I’d look to get the same level of hardware when I replace this laptop.

    Steve Jones

  • Changing Hardware

    I’ve been an Intel user for most of my life. While I have owned Mac machines, and I did once have an AMD desktop for the kids, I have pretty much stuck with Intel as my CPU of choice. Certainly I’ve had other processors in mobile devices, but for work, I have almost always purchased servers, desktops, or laptops with Intel CPUs.

    I remember the time in the 80s and 90s when there were lawsuits, advertisements, and innuendo put out by the companies, as well as fans of either side. I knew people that were sure Intel was the only way to go, fervently claiming that I’d have all sorts of issues trying to run my software on anything other than an Intel CPU. Just as insistent where others that assured me AMD worked fine and Intel was an overpriced waste of money.

    Over the years, I’ve seen Glenn Berry note that SQL Server runs better on Intel, because of the superior single-threaded performance on Intel machines. He’s changed his mind recently, and talks about why in a post this week. Glenn does advocate moving to new hardware, which often can give you better performance over older hardware at a lower consumption. If you are stuck on an older 2008/2012/2014 system, perhaps a hardware refresh is worth considering.

    Would any of you consider moving to AMD CPUs for your SQL Server instances? Is a hardware refresh something you might consider instead of upgrading software versions? If you got a 30-40% performance increase from a modern processor over a 2012/2013 era one, would that be worth the cost of changing hardware?

    We know that changing hardware isn’t as simple or easy as replacing one part with another. Likely you’d have a fair amount of labor to change out a bit of server hardware, even if you didn’t have to change any of your storage. With the growth and advantages of the cloud, is this something to even consider these days?

    I don’t know, though I tend to think that for most organizations, it’s not worth the time and hassle to update hardware without updating software. Perhaps you feel differently, and if so, tell us why.

    Some of you might be like me, not even really thinking about what hardware choices are out there. You let someone else worry about the physical machines and you just ask for a certain level of RAM, a number of cores, and a certain amount of storage performance. That’s how I’ve built SQL Servers in the past, which is really what I do now in the cloud.

    Steve Jones

  • More Linux than Windows

    Not for me. I definitely have more Windows running than Linux. Well, at least if you count minutes. If we’re counting instances of kernels, there might be times I have more running with Linux kernels in containers. However, on most days I have 1 or 2 Linux systems running and 3-4 Windows ones.
    That’s not the case in Azure, where the majority of instances are on Linux, not Windows. Microsoft announced this recently, noting that the use of Linux is growing for them. If you follow their work in the development area, this isn’t surprising. They are doing more and more OSS integration with their products or with other work. We have VS Code and Azure Data Studio, as well as .NET Core, running on Linux.
    We’ve had PowerShell on the Windows platform for over a decade. In that time, it has improved and grown, and become a default way for many people to manage their systems at scale, or certainly, in a consistent way. I’ve been surprised with how pervasive PowerShell has become. That’s why I’ve been surprised in the last year at //build/ and other conferences when I see Microsoft employees promoting bash as the new thing.
    I have nothing against bash, though I was more a korn shell person in University. I do like working in it at times, and I find myself doing that more and more. What I don’t like is a push away from PowerShell, especially as I’ve just started to get comfortable with -eq and -gt. I hope that as Microsoft moves towards more Linux versions of tools that they continue to keep PoSh as a first class citizen, on all platforms.
    Linux lost the desktop war. I’m pretty sure that’s the case, as I haven’t seen a Linux laptop or desktop in years. I see lots of MacOS, but more Windows, especially with the amazing hardware advances manufacturers have made in the last 4 years with the 2-in-1 format. However, I start to wonder if Linux will win the server war, or at least, become a much larger percentage of the back ends in the next ten years. I think it might, handling .NET and SQL Server loads in many cases.
    Steve Jones