Voice of the DBA

Tag: archiving

  • Archive to the Lake

    Microsoft Fabric was announced at Build in May 2023. This is the next evolution of data warehousing from Microsoft, folding in Synapse and a number of other technologies to create a simpler location for storing and analyzing data. We’ve published some articles on the platform and there’s a great presentation from Mr. Paul Andrew on Linked In. It’s worth listening to, even in the background. Paul has a nice style and a great voice.

    Part of this platform is OneLake. This is a data lake for your org, just one of them, and while it’s able to store data in many formats, it’s mainly optimized to read tabular data in the delta parquet format. This is essentially a compressed text file that allows for some transactional changes to the original data in parquet format.

    I don’t do a lot of work with text files, and I’ve been suspicious of using lots of CSV or other text files in a warehouse environment, which is what a lot of people were advocating a few years ago. Exporting tables into lots of files split on some field, like date, while easy, didn’t seem like the best way to move data for reporting.

    Fabric, however, is optimized for reading delta files. A few presentations I’ve seen from people have advocated for exporting your data from SQL Server (or other platforms) into parquet. While I don’t know there’s a native way to do this (yet), I suspect one is coming. I’ve seen lots of articles (one, two, three, more) about how to do this now. We also have SQL Server able to read these files with external file formats already, so I’m sure we’ll have an easy way to write them soon.

    Many of us struggle with large systems, especially with query performance. We’d love to archive off data, though that’s often impractical. However, in an amazing, wonderful world, maybe we’ll get lots of people doing this, writing about it in the media, and our bosses will start to let us establish an archive in the lake. We could move some data there, especially old, unchanging data. We could delete that from source systems. We could have all our users happy.

    I don’t know if I see lots of data moving to the lake, but I certainly expect lots of it to be copied. If you haven’t thought about archives, data lakes, and text formats, it’s an area that seems to have a lot of growth. Perhaps it’s of interest to you and you might find a new career.

    Or maybe you just hope it gets widely adopted to relieve some pressure on your OLTP server.

    Steve Jones

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

  • The Challenge of Deleting Data

    We collect a lot of data in our databases. Not as much in bytes as a lot of the video/audio/TikTok/Instagram sites, but still enough that many of us are constantly adding storage to our systems. All this data is not only a challenge to manage, but it also means that we are regularly dealing with query tuning issues. Better code, indexes, and more become regular challenges with large volumes of data.

    I am a big fan of trying to reduce the data you manage where possible. Archive, delete, remove older data, do something. This not only makes your systems easier to manage and improves performance, but it reduces your risk. Any PII data you have that might store is an ongoing risk in the event of a data breach. I don’t pretend this is easy to do in any way, but it’s a good idea.

    If you can remove data (or must because of a regulation like the GDPR), how do you ensure that data is deleted? Most of us know how to submit a DELETE statement, but that just removes the data from an online system. What if you restored or recovered this database tomorrow, would you remember to delete the data again? What about losing a copy of the data or log backup? What about older dev/test systems that were refreshed from production? The data might be in there. If you work through the possible problems, deleting data from a system isn’t as simple as you might expect.

    This might be even more complex in the age of cloud computing, where we don’t control the hardware for primary systems, or for backups. There is an article on deleting data in the cloud that talks about the government standards that require that you not only delete data, but that you overwrite the physical hardware to ensure it can’t be recovered. This still doesn’t address backup systems, but it does help to clarify that many of us might start to demand cloud vendors not only de-allocate the disks we use (or the backup storage), but they also overwrite the storage with zeros.

    Data security and the risks of not taking this seriously is becoming a bigger issue all the time. I don’t know that poor security will cause your organization to fail, but there can be significant costs and possibly reduced employment opportunities. While you might not want to be overly paranoid or concerned about every possible issue, it is worth asking questions of vendors, working through likely scenarios, and trying to quantify risk.

    More and more systems are regularly under attack from malicious groups, which means we want to minimize simple mistakes, reduce human error, and limit the exposure we have from the data we have by storing only the data we need.

    Steve Jones

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

  • Long Term Storage

    Data storage has always been a concern for data professionals. Early on in my career, we dealt with large ESDI, IDE, and SCSI drives, all of which would fail unexpectedly in servers. Sometimes after a few years, sometimes after a few weeks. We learned to use RAID and tape backups to ensure that our data was recoverable.

    In many places tape was the long term storage medium used. These days, I know many people have moved to secondary disk storage of some sort, often rotating data across a few disk types that give you recovery for days, weeks, or longer. I don’t know how long term storage work in Azure or AWS, but I assume some sort of combination of technologies are in use. I also know I don’t trust them completely to be readily available and recoverable after a few years.

    For most of us, database backups aren’t really relevant after some number of weeks or months. We usually just don’t need to recover things from long term storage. The exception might be for some types of data that do need to archived for legal or financial purposes. I know we used to keep a end of year tape for 7 years after we’d closed the financial records at one company. I don’t know if that would be the case today, especially with so many “digital records” of transactions. Would we really need to recreate a system as it looked on a particular day from 5 years ago?

    However, there are types of data that we might want to archive for a long time. An example might be the arts, where we have lots of music and video that can preserved. There might be other records, such as historical government records, which are suitable for WORM (write-once-read-many) systems.

    A new type of recording uses glass and may provide archival storage for thousands of years. Obviously we don’t know this is the case as we haven’t been recording digital information for thousands of years, but it’s an interesting medium. It also doesn’t require the algorithms to be maintained as the idea is machine learning systems can read back the data and learn to interpret it.

    To me, that might be the most interesting part of this project. Using computers to learn to read the data rather than requiring us to know have an MP3 player, a database system like SQL Server, or any other particular technology. Instead, we can let the computer learn how to read the data and then play back that recording of Prince in the year 3510.

    Steve Jones

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

  • DNA for Data Storage

    In all my years of working with data, there’s one think I’ve learned: it always grow. Over the years, I’ve gone from having to store data in these sizes:

    • A few KB on cassette tapes as a middle school kid
    • Using 360k floppies to 720k for programming, then 1.44MB to 100MB ZIP drives to 600MB CDs to 4GB DVDs
    • 10MB HDDs that grew quickly to be GBs and currently are TB sized in my desktop
    • A flip phone that could handle dozens of contacts with a SIM card to MBs of data in a T-Mobile G1 to a 64GB phone today

    In that time, I’ve often filled up many of those devices and needed more space. I am looking forward to seeing capacities grow again. My 64GB phone is full of pictures and video, and I need to manage the space periodically. The same thing for the 500GB in my laptop and the 2TB on my desktop. I need to add storage, and archive off some older information, but I certainly don’t want to lose any data, especially my pictures. Those might be the most valuable bits I have.

    I am alternately disappointed and amazed by storage technology advances. Capacities have grown tremendously, but speed and latency have had fits and starts. SSDs were a great improvement, and I do look forward to the newer 3D flash for laptops and high performance servers. I am glad there are these large capacity HDDs, but maybe there’s a better way to store lots of data for the long term.

    I ran cross this piece the shows the University of Washington and Microsoft working together to store data in synthetic DNA. They managed to store 200MB, which breaks the old record of 22MB. If they can improve the technology and make a commercial version, the theoretical storage could hold 1EB in a cubic inch. Even if we got 10% of this, 100TB in a little cube would be amazing. You can read more about the project at Microsoft Research.

    I have no idea what the write and read latencies are, but this could provide a nice backup for lots of data that we want to keep around, especially for images, video, perhaps even old auditing or other data that needs cheap, reliable storage that lasts years. I know I’d like to have this capability as my collection of data continues to grow, sometimes at amazing rates. I just hope this is cheap enough for general use, since as a data guy, I’d likely want 3 copies of everything. You know, just in case.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.5MB) podcast or subscribe to the feed at iTunes and Libsyn.