Voice of the DBA

Category: Editorial

  • Republish: Surfing

    I wish I was surfing, but no, just traveling for work. You get Surfing.

  • 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

  • Do You Deal with UTF-8?

    Microsoft is adding UTF-8 support in Azure SQL Database, and it will be coming in SQL Server 2019. If you don’t know what this is, perhaps you want to read a bit about it, as it can save space if you have the need to use Unicode characters. This format uses a variable number of bytes to encode characters, and this is often used on the web and email. My question today is:

    Are you looking to store data in UTF-8?

    The way this works with SQL Server can be complex. In fact, not everyone thinks this is really done well, as there are some bugs in the initial versions. As I’ve watched some people try to work with this, it is a very confusing and complex topic. I thought this might be a simple “SQL Server handles everything” collation, but it doesn’t appear that this will be the case. Calculating space needed for data isn’t as simple as I might expect. Not having to prefix strings with N is nice, but I’m not sure that this will actually work in practice.

    I’ve seen some discussions of how to work with this, and it’s complicated. In fact, it’s not easy to tell how much storage you might need for characters. The storage differences can be confusing, depending on the code range you work with. Since most of us know that our users will try to add data we would never expect to our database, and we might run into issues with not enough space. For those of us specifying the size for our columns, we now need to know how many bytes are in use, not characters.

    Likely this is easy for those of us that work in the English world and stick with varchar, but maybe not. I’m curious today how many of you will attempt to work with UTF-8 (or are waiting for it). It would also be good to know about any challenges or issues you’ve had working with the encoding in other systems or languages.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Grounded

    Technology has brought some challenges to travel. There is the bomb threat, of which I have no way to gauge how much of a potential problem this is on a daily basis. Certainly security at airports has changed over the years, and there is better scanning technology, but would anyone really be able to detect a bomb in a bag? Do we check all bags? I doubt anyone does, but perhaps I’m wrong. Either way, I hope this isn’t anything I need to worry about in my travels.

    I do carry electronics, usually a laptop, though sometimes two, along with the various supporting plugs and wires for charging it and my mobile. There was a time when I thought about getting a BRIX for demos, but with the requirements that security might make me turn it on, I decided not to get one. These days, with all the cloud options, I’m less worried about carrying a large amount of hardware, but I still need a laptop. When I saw a story about laptops being banned from flights, I wondered what I’d do.

    In this case, Macbooks are supposedly being banned if they have a certain battery issue. This came about quickly, and it’s likely that there were some business people in the middle of a trip when the ban was announced. That might have prevented them from flying, which would be quite a hassle. I remember when the Note was banned from some flights. I never met anyone that was inconvenienced, and likely plenty of people flew with their devices hidden in bags, so perhaps it wasn’t a big deal.

    I wonder about two things here. One, are we building new devices and technology that aren’t designed well from a safety standpoint? Most of us don’t have large servers in our offices anymore, so we might not care, but our other devices, which we use to get work done, perhaps might not be robust. In that case, I hope that businesses might have programs to help employees quickly replace devices if there are issues. Being on call and not having a device would be a nightmare.

    The other item is one I wonder about both for software and hardware. Do we make it easy enough to know what version of a device we have? I’ve owned Macbooks, and trying to determine what model you have is a chore. I can’t imagine that TSA or other security agencies could actually a) determine if you had a banned device, and b) know if you’ve had it repaired.

    I’d hate to think that companies will just build devices without making it easy to understand which generation of a device you own. This can already be a problem in not only computers, but other products like autos, where changes might be implemented in the middle of a model year. With the move to cloud and other rented software, are we going to have issues here? Azure SQL Database hasn’t changed the version in some time, despite there being changes to the code.

    Maybe it doesn’t matter, especially if old features don’t disappear. It does seem like something that we ought to do a better job of tracking, especially as it can be easy for us to have a model, but tag on a version number to easily allow a user to discern one from the other.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.