Latest / DevQuestions with Tim Corey / 037 How Do I Store Images In SQL? In Other Database Types?

Transcript

037 How Do I Store Images In SQL? In Other Database Types? · DevQuestions with Tim Corey

• 0:00
  SPEAKER_00: Welcome to the Dev Questions Podcast with Tim Corey.
• 0:03
  SPEAKER_00: Join us each episode as we tackle the questions you are asking about a career in software development, understanding the industry, and new technology.
• 0:12
  SPEAKER_00: If you're just starting out or you want to grow stronger as a developer, this is the place to get your questions answered.
• 0:19
  SPEAKER_00: Now, here's your host, expert developer and online educator, Tim Corey.
• 0:24
  SPEAKER_01: How do I store images in SQL?
• 0:27
  SPEAKER_01: How about in other database types?
• 0:29
  SPEAKER_01: This is a question that comes up kind of consistently over the past few years.
• 0:34
  SPEAKER_01: And so I just picked out a couple people, uh, Marcos and Eamon, that asked this recently.
• 0:38
  SPEAKER_01: So thank you for asking that question.
• 0:40
  SPEAKER_01: And we're gonna dive into what my thoughts are on the topic.
• 0:44
  SPEAKER_01: And like with most topics, the answer is gonna be it depends.
• 0:48
  SPEAKER_01: Okay, but I am gonna give you some thoughts about the way I lean typically when it comes to storing uh images.
• 0:56
  SPEAKER_01: And so let's talk about first what you can do.
• 0:58
  SPEAKER_01: Let's talk about uh SQL Server specifically.
• 1:02
  SPEAKER_01: There's a type in there called var binary max, and that's how you would store your image inside of SQL.
• 1:10
  SPEAKER_01: You can definitely do this.
• 1:11
  SPEAKER_01: Now, the benefits here are that it's connected right to your data.
• 1:16
  SPEAKER_01: So you have first name, last name, and person's image right there.
• 1:20
  SPEAKER_01: So those three things can be connected directly, and you can even link then to here's all my employees and all their images.
• 1:27
  SPEAKER_01: Well, they're all tied together, so you never put my face on Mary's name.
• 1:33
  SPEAKER_01: Of course, you know, that'd be a bad thing.
• 1:36
  SPEAKER_01: Um, so there's always a connection there between the person's name and their image.
• 1:41
  SPEAKER_01: So just in case we're storing names and photos of people, but there's that connection to the data, and that data is always in sync.
• 1:49
  SPEAKER_01: So those are benefits to storing your image right in the database, but there's also some drawbacks of doing this as well.
• 1:58
  SPEAKER_01: So there's a space issue.
• 2:01
  SPEAKER_01: Images have gotten a lot better over the years.
• 2:03
  SPEAKER_01: It used to be that an image might be 30 kilobytes or 50 kilobytes or 100 kilobytes, and now with our iPhone 12s or whatever is the latest iPhone that's out, you know, you can get a 10 megabyte picture.
• 2:17
  SPEAKER_01: Now, typically, when you upload to a Facebook or something similar, um, they're gonna reduce that image down, compress it down, turn it into a JPEG instead of um, you know, a bitmap or something like that.
• 2:30
  SPEAKER_01: And they're gonna really try and get as much compression as possible on that and shrink down the sides instead of you know 4,000 pixels wide, it's gonna be more like 200 pixels wide or something similar.
• 2:43
  SPEAKER_01: So you can do a lot of things to kind of compress that, but still, images take up a lot of space, a lot more space than text.
• 2:51
  SPEAKER_01: So my first name, my last name, those aren't taking up hardly any space on disk when it comes to your SQL Server.
• 2:58
  SPEAKER_01: If I have a million records of first name and last name, that's no big deal.
• 3:03
  SPEAKER_01: The SQL Server can do that in its sleep on a slow machine.
• 3:07
  SPEAKER_01: But you put a million first name and last names and user images on that hard drive, and now you might have some issues because your SQL Server went from maybe a gigabyte to 10 gigabytes or a hundred gigabytes.
• 3:23
  SPEAKER_01: Now you've got a real database size issue on your hands.
• 3:29
  SPEAKER_01: That then leads to the next um drawback of storing images on the disk or on your um your database, and that is backup time and restore time.
• 3:42
  SPEAKER_01: So you should be backing up your database on a fairly regular basis, maybe nightly as well as you know, weekly and monthly.
• 3:50
  SPEAKER_01: Have a kind of a schedule where you're backing up partial backups and doing full backups and and so on to make sure you always have a current backup or several backups on hand in case something goes wrong.
• 4:05
  SPEAKER_01: Well, the larger your database is, the larger your backup will be, the longer it will take to create that backup.
• 4:16
  SPEAKER_01: And usually backups can cause some performance issues on your database.
• 4:22
  SPEAKER_01: And that's why you do it at night.
• 4:23
  SPEAKER_01: We do it at night usually.
• 4:25
  SPEAKER_01: Um if you have certain low times, you know, midnight to 1 a.m., your time might be the slow time for you.
• 4:33
  SPEAKER_01: Well, then you schedule the backup in that window in order to um cause the least disruption.
• 4:41
  SPEAKER_01: But if you have a very, very large backup, it may exceed that backup window.
• 4:47
  SPEAKER_01: I've had I've seen servers where a backup took more time than there was in the day.
• 4:55
  SPEAKER_01: So a backup would take 26 hours, but you want a daily backup.
• 5:00
  SPEAKER_01: So the next backup's already trying to kick off when the first backup's not done.
• 5:05
  SPEAKER_01: And so that's a problem.
• 5:06
  SPEAKER_01: And it only gets worse because you usually add data to a database, you don't usually take data away.
• 5:12
  SPEAKER_01: So that's gonna be an issue.
• 5:15
  SPEAKER_01: Well, then it comes time to restore your database.
• 5:19
  SPEAKER_01: Let's just say something awful happened, your database got corrupted.
• 5:23
  SPEAKER_01: Um, it happens.
• 5:24
  SPEAKER_01: You know, a an intern came in and said delete from uh person table instead of having a WHERE clause, and all of a sudden every record's gone from our person table or our user table.
• 5:36
  SPEAKER_01: And now, you know, there's chaos.
• 5:38
  SPEAKER_01: Well, no problem, we have backups.
• 5:40
  SPEAKER_01: Cool.
• 5:40
  SPEAKER_01: How long does it take to restore that massive backup?
• 5:44
  SPEAKER_01: It takes a long time.
• 5:46
  SPEAKER_01: And so you have to wait until that's done restoring before your database is available again.
• 5:53
  SPEAKER_01: And until your database is available, you don't have the ability to provide service to your customers, whether those are internal customers or whether they are external customers.
• 6:05
  SPEAKER_01: So now everyone's waiting on you to get that database to restore.
• 6:09
  SPEAKER_01: That database that took 26 hours to back up, it was not restoring in an hour.
• 6:15
  SPEAKER_01: Okay, that was a very long restore process as well, which means that now you're talking about days of downtime instead of hours or minutes of downtime.
• 6:27
  SPEAKER_01: You really want that downtime to be as small as possible.
• 6:30
  SPEAKER_01: Well, that means you have to have a way to have quicker backups and restores.
• 6:37
  SPEAKER_01: So images kind of hurt that.
• 6:40
  SPEAKER_01: That's one of the drawbacks of storing images in your database.
• 6:43
  SPEAKER_01: The next one is cost.
• 6:46
  SPEAKER_01: You typically pay based upon the size of your hard drive.
• 6:50
  SPEAKER_01: Now, if you own your own copy of SQL Server and you're doing it locally, you're not paying in the same way you would in, say, Azure.
• 6:59
  SPEAKER_01: If you're on Azure, you pay per gigabyte.
• 7:02
  SPEAKER_01: And users caps and limits.
• 7:04
  SPEAKER_01: For example, on the$5 database, I believe you can get uh one gigabyte maybe of storage, which is a lot of storage, but it's kind of capped there.
• 7:15
  SPEAKER_01: Well, as you get larger database, you can get uh a larger pool of storage, but you don't have unlimited storage.
• 7:22
  SPEAKER_01: You pay for it.
• 7:24
  SPEAKER_01: On your personal server, if you have SQL Server locally, well, you have a little more freedom there, but you also have the problem of you're still gonna run out of storage at some point.
• 7:36
  SPEAKER_01: If if you have, let's just say, a terabyte of storage, but you are using 800 gigabytes of it, that's a problem.
• 7:46
  SPEAKER_01: You're getting close to your max storage issue.
• 7:50
  SPEAKER_01: And yes, you're gonna have to pay for either new storage or you're gonna have to pay for some other way to get that off of your server.
• 8:01
  SPEAKER_01: So it can be expensive there as well.
• 8:04
  SPEAKER_01: And then lastly, we have complexity.
• 8:07
  SPEAKER_01: When you're storing an image in SQL, you're typically not, you're not just saying, here's a file, store it.
• 8:16
  SPEAKER_01: You have to convert that into the var binary format, and then on the other end, you have to take that blob and convert it into an image.
• 8:27
  SPEAKER_01: It's not just a um take this and and work process, like it is with say a first name or last name.
• 8:35
  SPEAKER_01: First name or last name, we just say, here's a first name, T I M.
• 8:39
  SPEAKER_01: Boom, store that.
• 8:40
  SPEAKER_01: And if you open up the database table, you can read T I M because it's stored it that way.
• 8:45
  SPEAKER_01: It's not the same way with images because they're a blob.
• 8:50
  SPEAKER_01: So there's a lot of drawbacks to storing data images, especially in a database.
• 8:59
  SPEAKER_01: File or text data, no problem.
• 9:01
  SPEAKER_01: That's what it's designed for.
• 9:03
  SPEAKER_01: But when we're talking about files, they're a lot more difficult to store in a database well.
• 9:09
  SPEAKER_01: Now, if you make them small enough, sure, maybe that's not a problem.
• 9:12
  SPEAKER_01: You know, five kilobytes, ten kilobytes, thirty kilobytes, maybe even anything under a megabyte might be okay.
• 9:19
  SPEAKER_01: But again, if we're talking about you know a thousand records, now you have a gigabyte of storage that's just dedicated to your images.
• 9:29
  SPEAKER_01: So there's a lot of drawbacks to storing in the database.
• 9:33
  SPEAKER_01: And this is true for other database types as well.
• 9:35
  SPEAKER_01: It's not just SQL, it's the same as MySQL or uh MongoDB or anything else that you want to try and store blobs in, you're gonna have these drawbacks.
• 9:46
  SPEAKER_01: So, what's the alternative?
• 9:49
  SPEAKER_01: Well, the alternative is to store the files on disk.
• 9:53
  SPEAKER_01: And what you do is essentially you have a network share or something similar where you put the files.
• 10:01
  SPEAKER_01: And then you just store the file name, typically just a file name, inside of SQL and say this file is in the storage location.
• 10:11
  SPEAKER_01: And so that way you have kind of the best of both worlds.
• 10:17
  SPEAKER_01: You have the place to store it that that is designed to store files, is storing your files.
• 10:24
  SPEAKER_01: That's the file system.
• 10:26
  SPEAKER_01: And the place that's designed to store relational data is storing your relational data.
• 10:32
  SPEAKER_01: And then you just kind of connect the two with that pointer and say, okay, this is the path to this file.
• 10:41
  SPEAKER_01: So the benefits here, that's really fast because the file system knows how to deal with files.
• 10:47
  SPEAKER_01: Now you may have to rename the file.
• 10:49
  SPEAKER_01: And you know, if you if you have um, let's say user profile images, so you wouldn't necessarily want to even name it timcore.jpg.
• 10:59
  SPEAKER_01: You might want to name it all after a GUID and put that GUID.jpg is the file name because that way you don't have a conflict if there's two Tim Coreys.
• 11:09
  SPEAKER_01: So um otherwise you don't have you know Tim Corey and real TimCorey.
• 11:13
  SPEAKER_01: Um, you don't want that.
• 11:14
  SPEAKER_01: You want something simpler to do.
• 11:16
  SPEAKER_01: But whatever system you figure out to do that, storing files on disk is fast.
• 11:22
  SPEAKER_01: Not only that, it makes your SQL server leaner because it's not storing that file, it's just storing Tim Corey or you know, just that text information.
• 11:33
  SPEAKER_01: And so your backups are a lot faster, which means your restores will be a lot faster.
• 11:42
  SPEAKER_01: Now, it's also a lot cheaper.
• 11:45
  SPEAKER_01: If you go to Azure, okay, let's let's use Azure as um kind of our our measuring stick here, because the same is true for on-prem as it is for in the cloud.
• 11:57
  SPEAKER_01: It's just easier to see the cost in Azure.
• 12:01
  SPEAKER_01: So with Azure, we could get a smaller database, which would be cheaper.
• 12:06
  SPEAKER_01: The backups will be much easier to manage, much faster to restore, and we can get Azure storage, which is very cheap.
• 12:15
  SPEAKER_01: And we can put our files there.
• 12:16
  SPEAKER_01: So now we have a cheaper SQL Server and very cheap file storage.
• 12:21
  SPEAKER_01: We link the two, we're good to go.
• 12:24
  SPEAKER_01: They're both backed up, they're both capped in sync, and they're very quick on both sides.
• 12:31
  SPEAKER_01: So it's a lot cheaper solution as well, and it's simpler.
• 12:36
  SPEAKER_01: So if you store a file on disk, how easy is it to use that file, to access that file, to look at that file?
• 12:45
  SPEAKER_01: It's very easy.
• 12:46
  SPEAKER_01: You can even browse it manually by going to the file system and double-clicking and opening it.
• 12:50
  SPEAKER_01: You can't do that in SQL Server with a blob.
• 12:53
  SPEAKER_01: It's just it doesn't work that way.
• 12:55
  SPEAKER_01: It's in binary.
• 12:56
  SPEAKER_01: You've got to convert it out using some kind of conversion system.
• 13:00
  SPEAKER_01: So it's really simple to even review the files on the file system.
• 13:04
  SPEAKER_01: Now, what are the drawbacks though of doing this?
• 13:09
  SPEAKER_01: The the big drawback here is you've got two systems, not one.
• 13:15
  SPEAKER_01: So with two systems, you have to keep those in sync through discipline, not through an automated system.
• 13:24
  SPEAKER_01: So when you're adding files to the file system, you have to add them and add the record at the same time in SQL.
• 13:32
  SPEAKER_01: If you're deleting files, you have to also delete the records.
• 13:37
  SPEAKER_01: If you delete records, you have to delete the files.
• 13:39
  SPEAKER_01: So you keep those two in sync.
• 13:41
  SPEAKER_01: And that is a manual process.
• 13:44
  SPEAKER_01: And we all know that whenever it's a manual process, there can be issues.
• 13:49
  SPEAKER_01: Now, when I say manual, I don't necessarily mean that you have to physically go and delete the file and physically update the record.
• 13:57
  SPEAKER_01: You can create, you know, if your C sharp application is inserting new records for your user table, well, it can insert both the name in the database and the image location in the database and make sure those two sync up.
• 14:12
  SPEAKER_01: And if you delete a record, it can make sure it does both.
• 14:15
  SPEAKER_01: But you have to remember to do that.
• 14:17
  SPEAKER_01: You have to code it to do that.
• 14:19
  SPEAKER_01: And if you have a system that doesn't do that, maybe that glitch, well, then you're out of sync a little bit, and that could be an issue.
• 14:26
  SPEAKER_01: So it's not as easy when it comes to keeping those in sync as if they're in one record.
• 14:34
  SPEAKER_01: But there's a lot of benefits here.
• 14:37
  SPEAKER_01: So I say it depends because it does depend on your situation.
• 14:42
  SPEAKER_01: But in general, the place that I start from, if I have to default, if I'm not sure what the right answer is, I default to storing images on the disk.
• 14:53
  SPEAKER_01: It's faster, it's cheaper, it is quicker to access, and it has a lot of benefits.
• 15:00
  SPEAKER_01: You just need to know there are drawbacks and you need to plan for those.
• 15:04
  SPEAKER_01: But in general, that's what I recommend.
• 15:07
  SPEAKER_01: And if you're not quite sure, I would definitely say lean towards storing them on disk.
• 15:13
  SPEAKER_01: Just be careful, make sure that you are backing up both your database and your images.
• 15:19
  SPEAKER_01: If you forget one, a restore is gonna do you do you no good.
• 15:23
  SPEAKER_01: Now, questions that come up from this are one, well, what if I had to restore a different machine?
• 15:28
  SPEAKER_01: Well, then you would say, okay, cool, create that folder over again and put the images in that same path.
• 15:35
  SPEAKER_01: Well, the path's not there.
• 15:37
  SPEAKER_01: No problem.
• 15:38
  SPEAKER_01: Typically, what I do in a SQL Server is I don't put c colon slash temp slash image one dot JPEG.
• 15:46
  SPEAKER_01: I just put image one dot JPEG, and I would know what path to look at because that's the image path.
• 15:52
  SPEAKER_01: So you can change that in your one setting in app settings.json or wherever it is, and I say, okay, the new image path is c colon slash demos instead of c colon slash temp.
• 16:05
  SPEAKER_01: Cool, you're done.
• 16:06
  SPEAKER_01: You've renamed everything because they're all relative paths, not absolute paths.
• 16:11
  SPEAKER_01: So things like that you can do.
• 16:13
  SPEAKER_01: Um, again, when you talk about on-prem, which is you know your own server versus the cloud, yes, things get a little fuzzier because you're not being charged per gigabyte or charged based upon a size of your database.
• 16:29
  SPEAKER_01: But you really are.
• 16:30
  SPEAKER_01: You are incurring expenses.
• 16:32
  SPEAKER_01: So you have to be careful how you look at those things that you don't just think, oh, it's all free.
• 16:38
  SPEAKER_01: Because if your database takes too long to back up and restore, that's an issue.
• 16:43
  SPEAKER_01: Your time to recover from a failure gets longer and longer, that's a problem.
• 16:48
  SPEAKER_01: Um, you want to make sure you look at those things.
• 16:51
  SPEAKER_01: And, and here's a real key here when it comes to backups, test out your backups.
• 16:57
  SPEAKER_01: Test the restore, restore a new machine, see if it works.
• 17:01
  SPEAKER_01: And do that often.
• 17:03
  SPEAKER_01: Because if you don't test your backups, you don't have backups.
• 17:06
  SPEAKER_01: That's just a freebie.
• 17:07
  SPEAKER_01: So thanks for asking the question, Marcos and Amy or Eamon.
• 17:11
  SPEAKER_01: Um, if you'd like your question answered in this series, either use a form or um go to imtimcore.com.
• 17:18
  SPEAKER_01: There's a podcast page there.
• 17:20
  SPEAKER_01: Use a form there or leave a comment on the YouTube video.
• 17:24
  SPEAKER_01: Now, if you found this episode helpful, I love if you'd share it on your social networks.
• 17:29
  SPEAKER_01: Thanks for listening.
• 17:30
  SPEAKER_01: As always, I am Tim Corey.
• 17:33
  SPEAKER_00: Thank you for joining us for this episode of Dev Questions.
• 17:35
  SPEAKER_00: Tim is committed to making it easier for you to become a developer.
• 17:39
  SPEAKER_00: If you would like to help make more content like this possible, please like, subscribe, rate, and share Dev Questions.
• 17:46
  SPEAKER_00: You can also send your questions to questions at IamtimCorey.com.
• 17:51
  SPEAKER_00: Until next time, remember, you are too smart and your time too valuable to waste it making all the mistakes Tim did.
• 17:58
  SPEAKER_00: When you're ready to learn to think and code like a professional developer, head over to IamtimCorey.com and enroll in a course.