Voice of the DBA

Tag: Database Weekly

  • The Art of Commenting

    This week I noticed an article on comments in PoSh over at Simple Talk. It’s a nice look at the topic from Greg Moore and discusses the various ways that you can comment in the language. Since these scripts are often shared in a corporate environment and because they may outlast your tenure, it’s a good idea to include comments in your scripts and functions.

    The idea with PowerShell is similar to what you get from docstrings in Python. Since many people can script and build modules, being able to get some help and understanding of the code is important. Even for the modules I write, I may use them for some time and then put them down. When I go to use the same cmdlet or function again, I might not remember all the parameters or what I was thinking. If you’ve ever had to dig into code to understand what is happening, you quickly learn to appreciate those well documenting help strings.

    You may quickly learn to despise those that don’t write them well or even forget to include them at all.

    Writing a useful comment is a bit of an art. The author often needs to put themselves in the shoes of a less skilled individual, or maybe one that is context switching and needs to quickly understand what the code is doing. Not necessarily exactly how it works, but what it is supposed to do. This helps us decide if we can use the code quickly, or if we might need to dig in further.

    In the article, Greg points out some nice additional comment items, such as requirements for the module to run. These are the types of quick enhancements to code that greatly improve its useability for others. I’d highly recommend everyone learn about comment based help in PoSh, docstrings in Python, and other valuable commenting techniques in your language of choice.

    Learning to write good comments is a valuable skill, one that your team will appreciate. Since most of us work on teams these days, those skills just might make you more desirable for that next promotion or even a new position when word gets out. Practice becoming a good comment writer.

    Steve Jones

  • Put Your Data in a Box

    A few years ago I was attending a keynote talk from one of the scientists that works CERN’s Large HADRON Collider (LHC). The talk was about data and how they deal with a large volume of data from experiments run on the machine. If you’re wondering what large is, the scientist talked about peak experimental data being over 1PB/s. I don’t know how much data you capture, but that’s a lot.

    In fact, it’s so much, that if they tried to analyze all that data, they’d never get around to running experiments. Instead, they depend on some pre-processing of data in sensor hardware as well as some early aggregation to get the data to a manageable level. It’s a good idea, and they have spent a lot of time and effort learning how to do this and still capture meaningful data for their work.

    The capability to remotely process data before sending it on is coming to all of us in a pre-packaged container. The Azure Data Box Edge was announced this week from Microsoft. This is a data processing device that is cloud managed and has FPGAs that you can program. It can run on batteries and is ruggedized for the field. There are more docs at Microsoft on the specifics.

    I don’t know how many companies want this, but I suspect that some who have remote or portable operations might think about it. Certainly if this can gather some data and then upload when a connection is available, it might be a good fit for places that don’t have good network connections and need a device that can handle some adverse conditions. I know sourcing and putting together a system for field offices is a pain. Off the shelf systems don’t always have reliability, and can be finicky to manage remotely.

    Recently I hosted a webinar with Abel Wang, and he talked about AI and ML being technologies whose use will grow dramatically in the next ten years. Perhaps a prediction without merit, but it does seem more and more companies and vendors are putting efforts into finding ways to deploy and operate ML systems. The Data Box Edge has capabilities in that area, which could be useful if you want to locally, and quickly, process data.

    This doesn’t appear to run SQL Server, or it’s not mentioned, but it does seem useful. If you doubt this, I know this is exactly what I would have wanted over a decade ago. I had to install systems in a warehouse to visual inspect some products. We used crude AI-ish systems that were set up to watch evaluation by humans. Eventually, the computer took over part of the job, but with humans randomly verifying its results. Keeping that system running was a pain. A Data Box Edge would have been a much better choice, and I’m sure we would have purchased one.

    There are likely plenty of customers that might be able to use this. It will be interesting to see if Microsoft can find them and sell many of these devices.

    Steve Jones

  • Fantasy SQL Server

    This past week was the 118th T-SQL Tuesday (hosted by Kevin Chant) and it was a great one. Lots of people participated, with some really interesting entries. Kevin asked people to post their fantast T-SQL (or SQL Server) feature that they wish Microsoft would build. From better defaults and hints to a performance rating to CCI improvements to better partitioning, there are lots of creative solutions. Look for the recap this coming week.

    I didn’t write mine, mostly because I was out of the country and busy with SQL in the City Streamed and then some customer visits, as well as a mini-vacation in London with my wife. As a result, this slipped my mind, but my feature would be two phase authentication for a batch. I’d like a user to be able to submit a batch, have that held in a queue inside SQL Server until another user approved this feature somehow. The implementation doesn’t matter, but requiring two admins (or users) to run something would be fantastic for limiting rogue admins.

    What’s more, I’d use it to schedule something for the future that needed to be done, but I wasn’t sure when, like cleanup of some deployment. I’d write the trigger delete or other cleanup, leave it in queue and then have a job that reminds me of work that’s out there. When I’m ready, another account approves something.

    There were some good features submitted. I like Brent Ozar’s, restoring a single table, which is based on this suggestion with lots of votes. Not likely to happen because the backup process doesn’t know what’s on the pages, it just restores them. However, I’d think this could be added somehow with a scan of system tables inside the backup. Another simple one is better logging of job results. We’ve needed that for a long time, and that seems doable.

    If you didn’t participate, you can still write something, and even submit a suggestion to Microsoft. Doing a T-SQL Tuesday post is a great way to think and exercise your mind a bit. You might even have a great idea that someone notices and Microsoft picks up. You can still write your post and leave a comment on the invitation post.

    Steve Jones

  • 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