Voice of the DBA

Category: Editorial

  • Data Cleanup

    The end of the year is when I do a little data cleanup. Not a lot, but some. Work slows down, with no major deadlines, so I’m able to spend a bit of time organizing myself. I usually go through my downloads and documents folders, deleting files I don’t expect to use. I ensure that my jobs removing older log and temp files are running, especially on the laptops. A lot of disuse in 2021 had a few of them filling disks with more files than I had expected.

    In a few positions I’ve had as a DBA, this was a time when we’d look to archive away some older databases, and even some data. It was rare, but always invigorating because we knew queries would run faster in systems when we could archive off data. I haven’t ever started with clean databases in a new year, but in a couple of jobs we’d archive off all data that was more than one or two years old into another database. It was available if someone needed it, but the main OLTP databases would contain only one or two years’ worth of data, helping queries perform quicker at the start of the year.

    Last month I was at the SQL Server and Azure SQL Conference, and someone asked about the future plans for archive and migration of SQL Server data. The person noted they wished they could easily archive off data, specifically using a feature like Stretch Database. That feature wasn’t cost-effective, but it might be if you could stretch to another instance rather than Azure. No good answer from Microsoft, but that is something I’d like to see.

    Archiving older data, often data that is less queried is a good way to speed up systems. However, doing this seamlessly, and with easy access from applications, is cumbersome. Software should make this easy. I set up a database and then point the archive process to that location. The process manages moving data from a table based on a column value I’ve specified. Ideally, I could also decide if I allow queries to span the two databases automatically or I require some switch to allow querying of the archived data.

    There are plenty of possibilities here, but I suspect we won’t see any of them soon. There isn’t a lot of extra money to be made by allowing customers to manage their own archival systems, and these days it seems every software vendor is trying to make money from renting features rather than selling them. Archival isn’t one I see many customers willing to pay a premium for, so I suspect the solution for most of us is to write better and code and ensure queries perform well, even when we have terabytes of data.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The End of 2021

    Today is the last day of 2021. I’m on vacation, and I expect most of you are as well today. If you’re working, I hope it’s a light day and nothing breaks. At least, I hope it’s better than my first Dec 31 with SQL Server. After a month of exposure to the platform in 1991, I worked on Dec 31 to deploy a new application. I started at 5pm Dec 31, as most people were heading home from work, I was getting started.

    I left the office, actually, the power plant, at 9 or 10 am on Jan 2. Needless to say, things didn’t go well. Hopefully, your day of work goes smoother.

    Enjoy the end of the year, be safe and careful tonight, and I wish you a Happy New Year.

    Steve Jones

  • Republish: Change Approvals

    I’m out of town, spending the last week of the year in Las Vegas with my family. You get Change Approvals to read.

  • Looking Back at 2021

    Today is my last day of work for 2021. I’m off to a family vacation for the rest of the week to enjoy time together and celebrate the New Year. I wanted to take a look back at the year before I leave and give you a few thoughts on various data-related topics.

    The big news for those Microsoft data platform people is that SQL Server 2022 is coming. No idea when, and without public CTPs, I think it might be a 2022 H2 release, but Microsoft committed to the naming. There have been a number of talks, at Ignite, the PASS Data Community Summit, and the SQL Server and Azure SQL Conference. I had the chance to talk with Bob Ward and Pedro Lopes, and they are excited and proud of the upcoming release.

    I don’t think it’s a massive change, but there are a few nice things, and I look forward to the continued query processor improvements. It’s also nice to have a new version coming out after a few years. I’m guessing there are plenty of people working with 2016 or prior versions that might look to upgrade in the next couple of years.

    The other area that occupies a bunch of my time is the event scene. I am have spent a lot of my time in the last decade going to events and helping promote them. SQL Saturday has been an incredible boon to bringing together data professionals at local events, and I look forward to that continuing. 2020 and 2021 were hard years, with relatively few events, either virtual or physical. Concerns over people getting together have kept many organizers and venues from running anything.

    Many of us are vaccinated and there are areas trying to return to some sense of normalcy. I don’t know where the future will go, but I was lucky enough to go to a few events this year. Dataminds Connect in Belgium, SQL Saturday Orlando 2021, and the SQL Server and Azure SQL Conference were highlights for me. Different rules and restrictions at each, and compliance with those was easy for me. I loved getting to see people.

    There were few virtual events, and many of the events that did take place saw big declines from the attendance and engagement in 2020. While some user groups and smaller events have thrived, many have not. I hope that we continue to see some virtual events in the future, but I am looking forward to more physical interactions in 2022.

    One interesting data item from 2021 was the change in how healthcare has gone virtual. I’ve seen more visits taking place, and more devices coming that help smaller facilities run diagnostics for remote doctors. I’ve seen some home devices as well, and I love the growth in accessibility, and I worry about the security of data and privacy. The push to do reduce contact with others has led to some innovation for sure.

    Many of us have worked with data for a long time, and we know that companies have usually embraced the collection and analysis of data related to their businesses. Knowing that, I’ve been surprised how often I find companies struggling with supply chain issues and not knowing the status of their orders. Perhaps they do and prefer not to let consumers know, but in the past I’ve often seen estimates of when something will arrive. Even Amazon has had a lot of “out of stock and no idea” posts on products. I find it interesting that companies either don’t have the data or won’t disclose it. Either way, a lack of transparency wasn’t the data change I expected in 2021.

    I haven’t dabbled in Bitcoin or cryptocurrency at all, and I’m not sure what to think of Blockchain. It’s an interesting mathematical idea, but not sure I like it as a practical item. It’s coming in SQL Server 2022, and it’s also becoming more mainstream. The thing that made me think we should understand this better was the renaming of the Staples Center to the Crypto.com arena. No matter what you think of the tech, spending the money on advertising means there is plenty of revenue and profit in this area. From a data perspective, worth knowing something about how this works.

    For most of 2021, it felt like an extended 2020. Vaccination opened some things up, but the world didn’t return to normal. Many companies continued to avoid forcing people back into offices, but not all. I have some friends back in the office every day and even more that are required to go every week. I don’t know where 2022 will bring us, but I hope it’s a flexible blend of in-person and remote work. It might not work for everyone, but for many of us working with data, we can certainly get a lot done remotely. We proved that in 2020 and again in 2021.

    This was a good year for me, and I hope you can say the same thing. I’m off for the rest of the year, but I wish you a Happy New Year and look forward to seeing more of you in 2022.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.