Voice of the DBA

Author: way0utwest

  • FILESTREAM v FileTable –#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was really interested in the FILESTREAM technology when it came out. Unfortunately, the access from only Win32 APIs made this a pain for many people. I get why this is needed, but without having some tooling to make it easy to add/edit/delete blobs.

    In SQL Server 2012, we got FileTable, which simplified development and allowed everyone to easily get data in and out of SQL Server.

    This post looks at a few differences between the technologies.

    FILESTREAM is essentially a folder surfaced as a column in SQL Server. You can create a filegroup to hold FILESTREAM files, and these are paths. The path is managed by SQL Server, and when you designate a column as containing FILESTREAM data, a new folder is created.

    None of that matters, as you should NEVER go to these folders, but this is also how some other things in SQL Server  (like In-Memory OLTP) also work.

    Access to this data is with a Win32 API, and it’s semi-complex. Not hard, and really you can use the sample code (with some additional error handling) to do the work you need. On the application side, you need to do some stream handling and manipulation, but that’s really not difficult. It can be cumbersome, but it’s easy.

    FileTable was an additional layer that gives you SMB, or Windows Explorer, access to the data in the FILESTREAM column. Except, you can’t make a FileTable column, you create a FileTable, which has some restrictions, limitations, and required columns. You can add some things to the table, but really you want your FileTable to be a vertical partition of your regular OLTP data. Separate out the blob stuff from other items in your entity.

    Both save your data in database backups, both are more efficient for semi-large blobs moving in and out of SQL Server.

    Neither works in the PaaS cloud, though they could. I’d hope that FileTable especially would be added, with some enhancements to smooth the way a FileTable is structured, ubt I think with the focus on database level technologies (contained in a db), this technology is likely never getting any enhancement.

  • Do You Have Big Data?

    Data sizes are always growing. Stats on world data are astounding, as are the stats many of us experience in our lives. Plenty of us have moved from MB management to GBs, and I see plenty of people dealing with TB storage at home. Most of that data is likely from images and video, but I wouldn’t be surprised to find some people capturing lots of IoT and other random data about their lives.

    As data professionals, likely we don’t quite have the explosion of data inside our organizations, but we certainly do have increasing database sizes. I see this with customers all the time, often with continued or expanded collection of data and no archival plans. The number of customers with > 1TB databases increases every year.

    Overall data volumes are growing at a faster rate than our growth in processing capabilities. That is nothing new, as we’ve always collected more data than we can easily process. Even if you avoid image, video, and audio data, likely you have more CSVs, extracts, and export files than you know what to do with. Maintaining this data is hard, and dealing with all the associated systems is a challenge.

    I wonder today how many people think you have big data issues. No matter whether you deal with GBs, TBs, or something larger, do you feel that managing all your data is a big part of someone’s job? Can you analyze all your data, at least at the rate your customers would like? Can you keep up with queries and extracts?

    This article talks about some of the challenges, and how the problems with big data are going to to continue to plague us for some time. Does the article resonate with you? Or perhaps you feel that you have everything under control. I’d be curious in either case. Leave me a comment and let me know.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Daily Coping 9 Sep 2020

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip is to forgive yourself when things go wrong. We all make mistakes.

    Things go wrong all the time. Especially at the ranch, where many things break, or get broken.

    Recently I took a day off to work on some baseboard trim in the house. We had some work done in a couple rooms, and the old trim was taken out and tossed. It didn’t match the rest of the house, and it’s been something I wanted to replace. At $6 a foot for a contractor, who wasn’t thrilled with a 2 room job, and a material cost of $1.42 a foot for about 120ft, I decided to do this myself.

    If you’ve done this, you know it’s not hard, but it’s not a piece of cake. Some attention to detail is needed, and some care. As I worked my way around the two rooms, I find the flor wasn’t quite level, and so sometimes the trim needed to bend over a slight rise or meet a dip. This resulted in a few joints not matching perfectly. Add to this that I cut a few pieces incorrectly as I got them down.

    I know some imperfections get covered with paint or caulk, but as I got close to finished, I wasn’t happy with two pieces. Rather than get upset, I just pulled them out, cut new pieces, one I cut 2 or 3 times, and made things slightly better.

    A small thing, but it’s a lesson I’ve learned from work and life that I will make mistakes. I should aim for, and work to, limit mistakes and try not to repeat them, but sometimes I do, and it’s not the end of the world. Move on, correct things as best I can, and forget about them.

  • The Best Security for Database Administrators

    A short while ago, Twitter got hacked. I wrote about this, as did Denny Cherry. I think Denny’s piece was more interesting, as he speculates about the security measures that Twitter may, or may not, have had in place.

    One of the things Denny brings up is air-gapping administrative machines. I’ve rarely seen this in organizations, and perhaps see this less and less in the pandemic world. There are some high security places that do this, but could your organization do this? How many of us use cloud or co-location facilities where we can’t even physically enter the premises?

    I suppose we can use some strong network security controls, perhaps even requiring static IP addresses for people at home and specific routes for certain administrators. I do know some companies that do require specific laptops for access, with limited software, but this certainly isn’t the norm. Too often a general laptop used for most work performs double duty as an administrative workstation with access to production data.

    Another thing Denny mentions is jump hosts, without any cut/copy/paste functionality from the remote machine to the host. This is something I am starting to see from customers, even smaller ones, as a way of limiting the chance of ransomware or some security breach. Multi-factor authentication gets an administrator onto a remote desktop session on a jump host, from which they can access production systems with limited tools. This certainly isn’t perfect, and it is annoying for administrators, but it is a good security layer, and it forces organizations to use good, compliant, database DevOps practices to deploy changes.

    Perhaps the best part of Denny’s article is the title of the last section: good security shouldn’t be user friendly. It shouldn’t be for administrators. While we might make things slightly easier for average users, anyone that can access bulk amounts of data, especially in a privileged fashion, should have strong security, which is a bit cumbersome. I think the hassles of strong security would be a good thing for more of us to have to deal with. Hopefully more organizations will start taking better precautions and reduce the chance of attack.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.