Storing Images in a Database Tips and Techniques

Storing Images in a Database – Tips and Techniques By Todd Kleinhans todd @toddkleinhans. com https: //toddkleinhans. wordpress. com/

About Me

Me, Me… • Been working with SQL Server since 1999 • Volunteer in Denver • • Denver SQL Server User Group Denver/Boulder Unreal Engine • Career 1. 5 – Data Scientist, Image Processing • Career 2. 0 – Neuroscientist • Virtual Reality (VR) is my addiction/hobby…

Overview • Background on Binary Large Objects (BLOBs) • Cloud Storage Costs • Pros and Cons • Demos • varbinary(max) in Adventure. Works • Filestream and File. Table • Theoretical storage using RGB pixel values • VR tour using Unreal Engine (UE 4)

Background on Binary Large Objects (BLOBs) • BLOB = unstructured data like digital files • Pictures, PDF(s), anything really! • BLOBs generally exceed 8060 bytes • Old guidance: • IF file is <1 MB, use a db ELSE use a filesystem • Performance • SSD and Flash storage

Background on Binary Large Objects (BLOBs)

Cloud Storage Costs Size and Yearly Costs 50 GB One. Drive/Office 365 $23. 88 100 GB 200 GB 1 TB 2 TB Google One 10 TB 20 TB 30 TB $69. 99 Drop. Box i. Cloud 3 TB $150 $49. 50 $35. 88 $19. 99 $29. 99 $119. 88 $99. 99 $1, 200 $2, 400 $3, 588

Pros and Cons SQL Server BLOB Filesystem Backups . bak files raw or compressed Backup Timing Frequently during the day 24 hr or weekly Security Tightly controlled Loosely controlled Fragmentation DBA IT Transaction (ACID) native NOT native

Demo Time! • varbinary(max) in Adventure. Works • Filestream and File. Table • Theoretical storage using RGB pixel values

Links To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem? https: //www. microsoft. com/en-us/research/wp-content/uploads/2006/04/tr-2006 -45. pdf The Art of SQL Server FILESTREAM http: //assets. red-gate. com/community/books/art-of-ss-filestream. pdf Python ODBC Driver https: //docs. microsoft. com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc? view=sqlserver-2017 …

Thank the sponsors!! Global Partner Gold Silver Bronze Pass Venue

Thank You!!! By Todd Kleinhans todd @toddkleinhans. com https: //toddkleinhans. wordpress. com/