{"id":6483,"date":"2008-04-14T18:15:00","date_gmt":"2008-04-15T01:15:00","guid":{"rendered":""},"modified":"2024-01-22T22:51:47","modified_gmt":"2024-01-23T06:51:47","slug":"files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/","title":{"rendered":"Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!"},"content":{"rendered":"

One of the most exciting new features in SQL Server 2008 is the ability to store files and BLOBs directly in the file system, while maintaining transactional consistency with a SQL Server 2008 database. SQL Server 2008\u2019s new FILESTREAM attribute for VARBINARY data type solves the age old dilemma facing developers and IT Pros: Is it better to store files directly in a database or store them in the file system with path and filenames stored back in tables to maintain the relationship with the database. <\/span><\/p>\n

Both approaches have pros and cons: Storing files directly in a database secures information using database permissions and enables this data to participate in transactions and backups. This approach, however, does not provide the best performance and storing large files can add significant size to a database. On the other hand, storing files in the file system while using tables and columns to link them to a database is less taxing on a database and provides more flexibility and performance since NTFS is very efficient at managing files. This approach, however, leaves files vulnerable to corruption or deletion, plus files cannot participate in transactions and backups which can leave a database unstable. Another issue in storing files outside the database is that the logic for linking these files to the database must be maintained (and duplicated) in any application that uses this data. This introduces maintenance issues that can become burdensome and complex as the number of applications connecting to the database increases and as the number of people or teams involved in managing this data increases.<\/span><\/p>\n

SQL Server 2008 solves this dilemma elegantly. VARBINARY’s new FILESTREAM attribute provides the best of both worlds. By simply specifying FILESTREAM for VARBINARY data types, SQL Server 2008 shackles data (i.e. files or BLOBs) for that column to the table, while leveraging Windows NTFS to actually store and retrieve the data outside the database. This shackled data resides in the file system yet is managed entirely by SQL Server 2008. This data is protected by the database permissions and participates fully in transactions and backups as expected. Outside the database, users see this data as regular files in the file system that cannot be modified or deleted without admin rights since they are owned by SQL Server.
<\/span><\/p>\n

Here are a few interesting links discussing <\/span><\/span>FILESTREAM:<\/span><\/p>\n

Paper: Managing Unstructured Data with SQL Server 2008 (Whitepaper)<\/a><\/p>\n

FileStream Data Type: SQL Server 2008<\/a><\/p>\n

SQL Server 2008 – FILESTREAM, WPF and HTTP<\/a>\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the most exciting new features in SQL Server 2008 is the ability to store files and BLOBs directly in the file system, while maintaining transactional consistency with a SQL Server 2008 database.<\/p>\n","protected":false},"author":1457,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","footnotes":""},"post_tag":[],"product":[],"content-type":[2445],"topic":[],"coauthors":[],"class_list":["post-6483","post","type-post","status-publish","format-standard","hentry","content-type-thought-leadership"],"yoast_head":"\nFiles, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Files, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog\" \/>\n<meta property=\"og:description\" content=\"One of the most exciting new features in SQL Server 2008 is the ability to store files and BLOBs directly in the file system, while maintaining transactional consistency with a SQL Server 2008 database.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft SQL Server Blog\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/sqlserver\" \/>\n<meta property=\"article:published_time\" content=\"2008-04-15T01:15:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-01-23T06:51:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"SQL Server Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:site\" content=\"@SQLServer\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"SQL Server Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/\",\"@type\":\"Person\",\"@name\":\"SQL Server Team\"}],\"headline\":\"Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!\",\"datePublished\":\"2008-04-15T01:15:00+00:00\",\"dateModified\":\"2024-01-23T06:51:47+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\"},\"wordCount\":417,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\",\"name\":\"Files, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\"},\"datePublished\":\"2008-04-15T01:15:00+00:00\",\"dateModified\":\"2024-01-23T06:51:47+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"name\":\"Microsoft SQL Server Blog\",\"description\":\"Official News from Microsoft\u2019s Information Platform\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization\",\"name\":\"Microsoft SQL Server Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png\",\"width\":259,\"height\":194,\"caption\":\"Microsoft SQL Server Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/www.facebook.com\/sqlserver\",\"https:\/\/x.com\/SQLServer\",\"https:\/\/www.youtube.com\/user\/MSCloudOS\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Files, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/","og_locale":"en_US","og_type":"article","og_title":"Files, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog","og_description":"One of the most exciting new features in SQL Server 2008 is the ability to store files and BLOBs directly in the file system, while maintaining transactional consistency with a SQL Server 2008 database.","og_url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/","og_site_name":"Microsoft SQL Server Blog","article_publisher":"http:\/\/www.facebook.com\/sqlserver","article_published_time":"2008-04-15T01:15:00+00:00","article_modified_time":"2024-01-23T06:51:47+00:00","og_image":[{"width":512,"height":512,"url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2018\/08\/cropped-microsoft_logo_element.png","type":"image\/png"}],"author":"SQL Server Team","twitter_card":"summary_large_image","twitter_creator":"@SQLServer","twitter_site":"@SQLServer","twitter_misc":{"Written by":"SQL Server Team","Est. reading time":"2 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/author\/sql-server-team\/","@type":"Person","@name":"SQL Server Team"}],"headline":"Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!","datePublished":"2008-04-15T01:15:00+00:00","dateModified":"2024-01-23T06:51:47+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/"},"wordCount":417,"commentCount":3,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/","name":"Files, BLOBs, NTFS and SQL Server 2008 - Age old database dilemma solved! - Microsoft SQL Server Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website"},"datePublished":"2008-04-15T01:15:00+00:00","dateModified":"2024-01-23T06:51:47+00:00","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2008\/04\/14\/files-blobs-ntfs-and-sql-server-2008-age-old-database-dilemma-solved\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/"},{"@type":"ListItem","position":2,"name":"Files, BLOBs, NTFS and SQL Server 2008 – Age old database dilemma solved!"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","name":"Microsoft SQL Server Blog","description":"Official News from Microsoft\u2019s Information Platform","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#organization","name":"Microsoft SQL Server Blog","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-content\/uploads\/2019\/08\/Microsoft-Logo.png","width":259,"height":194,"caption":"Microsoft SQL Server Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/www.facebook.com\/sqlserver","https:\/\/x.com\/SQLServer","https:\/\/www.youtube.com\/user\/MSCloudOS"]}]}},"msxcm_display_generated_audio":false,"msxcm_animated_featured_image":null,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/6483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/users\/1457"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/comments?post=6483"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/posts\/6483\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/media?parent=6483"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/post_tag?post=6483"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/product?post=6483"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/content-type?post=6483"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/topic?post=6483"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/wp-json\/wp\/v2\/coauthors?post=6483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}