{"id":28143,"date":"2023-10-06T22:44:06","date_gmt":"2023-10-06T20:44:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=28143"},"modified":"2025-03-04T11:03:46","modified_gmt":"2025-03-04T10:03:46","slug":"the-case-of-filetable-with-basic-availability-groups","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/","title":{"rendered":"The Case of FileTable with Basic Availability Groups"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"h-\"><\/h2>\n\n\n\n<p>SQL Server&#8217;s FileTable feature provides the integration of the SQL Server database engine with the file system. It lets you store files and documents in special tables called FileTables within SQL Server, while still maintaining Windows file namespace semantics.<\/p>\n\n\n\n<p>However, when it comes to SQL Server Availability Groups (AG), there are specificities you need to be aware of.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Why use FileTables?<\/h2>\n\n\n\n<p>FileTables can be incredibly useful because:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>They bridge the gap between traditional relational data and unstructured files.<\/li>\n\n\n\n<li>Allow for complex queries that combine structured and unstructured data.<\/li>\n\n\n\n<li>Files can be accessed and managed using regular Windows APIs or T-SQL.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">2. Basic Availability Groups and FileTables<\/h2>\n\n\n\n<p><\/p>\n\n\n\n<p>Basic Availability Groups (available in SQL Server Standard Edition) offer a reduced set of features in comparison to Advanced Availability Groups. One of the limitations is that they support only one database per group. When using FileTables in the context of Basic AGs, you need to be sure that all FileTable data is contained within a single database.<\/p>\n\n\n\n<p><a href=\"https:\/\/learn.microsoft.com\/en-us\/previous-versions\/sql\/sql-server-2012\/gg492086(v=sql.110)?redirectedfrom=MSDN\">FileTable Compatibility with Other SQL Server Features | Microsoft Learn<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-3-lab-demo\">3. LAB DEMO<\/h2>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3.1 Setup<\/h3>\n\n\n\n<p>In the following demo we will work on a 2 nodes (nodeA and NodeB) server configuration of SQL Server Always with an AG named &#8220;AG1&#8221; and a listener named &#8220;aglist&#8221;.<br>The database AdventureWorks2022 is part of this AG.<br>Those servers will not be using a share in order to host the data, both nodes will have independent local storage for the FileTable.<\/p>\n\n\n\n<p>In order to create a FileTable we need to first activate FILESTREAMING on both nodes as such ( a restart of the service is required, good thing that we are doing that on an AlwaysOn!)<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"413\" height=\"492\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-30.png\" alt=\"\" class=\"wp-image-28144\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-30.png 413w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-30-252x300.png 252w\" sizes=\"auto, (max-width: 413px) 100vw, 413px\" \/><\/figure>\n\n\n\n<p>Then we can run the following code :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE AdventureWorks2022\nGO\n\nUSE &#091;master]\nGO\nALTER DATABASE &#091;AdventureWorks2022] ADD FILEGROUP &#091;FileTableFG] CONTAINS FILESTREAM \nGO\nUSE &#091;AdventureWorks2022]\nGO\nIF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FileTableFG') ALTER DATABASE &#091;AdventureWorks2022] MODIFY FILEGROUP &#091;FileTableFG] DEFAULT\n\n\nALTER DATABASE &#091;AdventureWorks2022] ADD FILE ( NAME = N'FT', FILENAME = N'F:\\data\\FT' ) TO FILEGROUP &#091;FileTableFG]\nGO\n\nuse &#091;master];\nGO\nALTER DATABASE &#091;AdventureWorks2022] SET FILESTREAM( DIRECTORY_NAME = N'FTdirectory' ) WITH NO_WAIT\nGO\n\nUSE AdventureWorks2022\ngo\nCREATE TABLE demo AS FILETABLE\n  WITH\n  (\n    FILETABLE_DIRECTORY = 'FTdirectory',\n    FILETABLE_COLLATE_FILENAME = database_default\n  )\nGO<\/code><\/pre>\n\n\n\n<p>Then we also can activate the database option FULL Non-Transacted Access for FILESTREAM :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"666\" height=\"509\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-31.png\" alt=\"\" class=\"wp-image-28145\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-31.png 666w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-31-300x229.png 300w\" sizes=\"auto, (max-width: 666px) 100vw, 666px\" \/><\/figure>\n\n\n\n<p>This will require an exclusive lock on the DB and allow us to use the filesystem in addition to T-SQL queries to alter the files.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3.2 Inserting files using T-SQL<\/h3>\n\n\n\n<p>We can see on both nodes that a folder has been created to host the incoming files :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1008\" height=\"246\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-32.png\" alt=\"\" class=\"wp-image-28146\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-32.png 1008w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-32-300x73.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-32-768x187.png 768w\" sizes=\"auto, (max-width: 1008px) 100vw, 1008px\" \/><\/figure>\n\n\n\n<p>We can then also browse the data by using the &#8220;Explore FileTable Directory&#8221; option on the table in SSMS.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"382\" height=\"317\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-33.png\" alt=\"\" class=\"wp-image-28147\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-33.png 382w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-33-300x249.png 300w\" sizes=\"auto, (max-width: 382px) 100vw, 382px\" \/><\/figure>\n\n\n\n<p>Let&#8217;s insert a file :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE AdventureWorks2022\n\nINSERT INTO demo (name, file_stream) SELECT 'document.txt', * FROM OPENROWSET(BULK N'F:\\data\\FT\\document.txt', SINGLE_BLOB) as FileData;\n\n-- get the fullpath of the file\nUSE AdventureWorks2022;  \nDECLARE @root nvarchar(100);  \nDECLARE @fullpath nvarchar(1000);  \n\nSELECT @root = FileTableRootPath();  \nSELECT @fullpath = @root + file_stream.GetFileNamespacePath()  \n    FROM demo  \n    WHERE name = N'document.txt';  \n\nPRINT @fullpath;  \nGO <\/code><\/pre>\n\n\n\n<p>Here the full path will be :<\/p>\n\n\n\n<p><code><strong>\\\\aglist\\nodeA\\FTdirectory\\FTdirectory\\document.txt<\/strong><\/code><\/p>\n\n\n\n<p>In this case the document.txt was only 64KB. <\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Let&#8217;s push the system a bit by creating and inserting a 10GB file :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"593\" height=\"44\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-34.png\" alt=\"\" class=\"wp-image-28148\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-34.png 593w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-34-300x22.png 300w\" sizes=\"auto, (max-width: 593px) 100vw, 593px\" \/><\/figure>\n\n\n\n<p>During this insert, NodeA is working hard on CPU and IO :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1007\" height=\"499\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-35.png\" alt=\"\" class=\"wp-image-28149\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-35.png 1007w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-35-300x149.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-35-768x381.png 768w\" sizes=\"auto, (max-width: 1007px) 100vw, 1007px\" \/><\/figure>\n\n\n\n<p>While NodeB is barely working :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"969\" height=\"694\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-36.png\" alt=\"\" class=\"wp-image-28150\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-36.png 969w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-36-300x215.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-36-768x550.png 768w\" sizes=\"auto, (max-width: 969px) 100vw, 969px\" \/><\/figure>\n\n\n\n<p>The resource usage on nodeA seems to impact the select statement on the table (24s for a simple select):<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"532\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37-1024x532.png\" alt=\"\" class=\"wp-image-28151\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37-1024x532.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37-300x156.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37-768x399.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37-1536x798.png 1536w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-37.png 1542w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>In total in took 10min to insert this file using a SQL Statement :<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"436\" height=\"114\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-38.png\" alt=\"\" class=\"wp-image-28152\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-38.png 436w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-38-300x78.png 300w\" sizes=\"auto, (max-width: 436px) 100vw, 436px\" \/><\/figure>\n\n\n\n<p>After this insert we have both files present on both nodes and if we failover it is working as expected.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">3.3 Inserting file using the File System<\/h3>\n\n\n\n<p>What about the file system performance?<br>If we copy the 10GB file directly in Explorer the bandwidth seems bigger, but the last 5s last for another 4min in full Windows fashion \ud83d\ude42<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"952\" height=\"630\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-39.png\" alt=\"\" class=\"wp-image-28153\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-39.png 952w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-39-300x199.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-39-768x508.png 768w\" sizes=\"auto, (max-width: 952px) 100vw, 952px\" \/><\/figure>\n\n\n\n<p>The reason for that is the same as in T-SQL, the data has to be copied on the replica as well although using the file system in that case is faster. If we had used a single shared folder it would have been even faster.<\/p>\n\n\n\n<p>But now my select statements on the table are really slow, I only have 3 rows in it\u2026 why?<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"591\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-40-1024x591.png\" alt=\"\" class=\"wp-image-28154\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-40-1024x591.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-40-300x173.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-40-768x443.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-40.png 1415w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>What is the execution plan saying?<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-41.png\" alt=\"\" class=\"wp-image-28155\" style=\"width:576px;height:244px\" width=\"576\" height=\"244\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-41.png 574w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-41-300x127.png 300w\" sizes=\"auto, (max-width: 576px) 100vw, 576px\" \/><\/figure>\n\n\n\n<p>Nothing wrong with this plan. Stats?<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL Server parse and compile time: \n   CPU time = 0 ms, elapsed time = 0 ms.\n\n SQL Server Execution Times:\n   CPU time = 0 ms,  elapsed time = 0 ms.\nSQL Server parse and compile time: \n   CPU time = 0 ms, elapsed time = 0 ms.\n\n(3 rows affected)\nTable &#039;demo&#039;. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.\n\n(1 row affected)\n\n SQL Server Execution Times:\n   CPU time = 2219 ms,  elapsed time = 13262 ms.\nSQL Server parse and compile time: \n   CPU time = 0 ms, elapsed time = 0 ms.\n\n SQL Server Execution Times:\n   CPU time = 0 ms,  elapsed time = 0 ms.\n\n<\/pre><\/div>\n\n\n<p>So the estimation is correct, the indexes created by the system are working, it is only the CPU time that seems high but why? <\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"334\" height=\"426\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-42.png\" alt=\"\" class=\"wp-image-28156\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-42.png 334w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/image-42-235x300.png 235w\" sizes=\"auto, (max-width: 334px) 100vw, 334px\" \/><\/figure>\n\n\n\n<p>PREEMPTIVE_OS_FILEOPS Wait is related to the fact that SQL Server is waiting for OS file system to complete a task. In my case, I am using Azure HDD, so the performance is off in an extreme way but it shows how the size of a file can impact SQL Queries. Whenever it is possible in your design, choose a filesystem over any SQL Server or any database feature, file systems are much faster because they are created to store\u2026 FILES \ud83d\ude42 <\/p>\n\n\n\n<p>Another point is that the SQL Server license is quite expensive compared to a free file system that can do the job faster in a more scalable way. <\/p>\n\n\n\n<p>I am not saying that it is not a nice feature and that it should never be used, I am saying that it will never scale.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">4. Conclusion<\/h2>\n\n\n\n<p>In the case of a Basic Availability Group (BAG) without a shared folder, the data integrity is handled by FILESTREAM and FILETABLE features. The data is safe thanks to BAG but the insert update time will suffer. If we use large files (compared to the system capabilities) this has an impact on transaction logs, redo and also on the failover time, which in all can lead to data corruption. Here my transaction lasted for several minutes, which means that a rollback of that redo in case of failover, might not really be &#8220;AlwaysOn like&#8221; for the users. Failure of the system during an undo-redo time frame will increase the risk of data corruption. <br>With the use of big files, the system tends to use streaming instead of buffering to minimize memory consumption. If there is a failure during the streaming process, it can also result in data corruption. Additionally and lastly, concurrency on the Filetable between non-transactional through the file system and Filetable feature can create data inconsistencies. <br>BAG also has the inconvenience of not allowing Read-Only replicas. This means that in my case I need to failover to one node to have access to the data of that node and that I cannot scale the reads by using multiple file systems. <br>The use of a shared folder can avoid those issues but create additional complexities such as network latency and increased management. In that case, having a single point of failure in an AG means that you have to rely on filesystem replication such as DFS or SAN-level redundancy <\/p>\n\n\n\n<p>In conclusion, FileTables in SQL Server Availability Groups work in much the same way as other tables during a failover. However, due to their integration with the file system, there are a few additional considerations to keep in mind to ensure smooth operations during and after the failover. Always ensure to test the failover process thoroughly in a non-production environment before implementing it in a live environment. <br>Combining FileTable with AlwaysOn Availability Groups can offer a robust solution for storing non-relational data within SQL Server while benefiting from high availability. However, it requires diligent monitoring  ( IO, network glitches, transaction durations&#8230;) and management to ensure optimal operation and avoid time windows where the system is more likely to produce data corruption.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Using FileTables with Basic Availability Groups in SQL Server offers a robust solution for storing non-relational data but requires careful management due to potential risks like data corruption during long transactions and failovers. While BAG ensures data integrity, challenges arise from large file handling, streaming, and the absence of Read-Only replicas.<\/p>\n","protected":false},"author":153,"featured_media":28165,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[99],"tags":[921,3093,3092,51],"type_dbi":[],"class_list":["post-28143","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-basic-availability-groups","tag-filestream","tag-filetable","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>The Case of FileTable with Basic Availability Groups - dbi 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.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Case of FileTable with Basic Availability Groups\" \/>\n<meta property=\"og:description\" content=\"Using FileTables with Basic Availability Groups in SQL Server offers a robust solution for storing non-relational data but requires careful management due to potential risks like data corruption during long transactions and failovers. While BAG ensures data integrity, challenges arise from large file handling, streaming, and the absence of Read-Only replicas.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-06T20:44:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-04T10:03:46+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"1024\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Adrien Obernesser\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Adrien Obernesser\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/\"},\"author\":{\"name\":\"Adrien Obernesser\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/fd2ab917212ce0200c7618afaa7fdbcd\"},\"headline\":\"The Case of FileTable with Basic Availability Groups\",\"datePublished\":\"2023-10-06T20:44:06+00:00\",\"dateModified\":\"2025-03-04T10:03:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/\"},\"wordCount\":1119,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/09\\\/Pasted-image-20230925140500.png\",\"keywords\":[\"Basic availability groups\",\"FILESTREAM\",\"FileTable\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/\",\"name\":\"The Case of FileTable with Basic Availability Groups - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/09\\\/Pasted-image-20230925140500.png\",\"datePublished\":\"2023-10-06T20:44:06+00:00\",\"dateModified\":\"2025-03-04T10:03:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/fd2ab917212ce0200c7618afaa7fdbcd\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/09\\\/Pasted-image-20230925140500.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/09\\\/Pasted-image-20230925140500.png\",\"width\":1024,\"height\":1024},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/the-case-of-filetable-with-basic-availability-groups\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Case of FileTable with Basic Availability Groups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/fd2ab917212ce0200c7618afaa7fdbcd\",\"name\":\"Adrien Obernesser\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g\",\"caption\":\"Adrien Obernesser\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/adrienobernesser\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"The Case of FileTable with Basic Availability Groups - dbi 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.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/","og_locale":"en_US","og_type":"article","og_title":"The Case of FileTable with Basic Availability Groups","og_description":"Using FileTables with Basic Availability Groups in SQL Server offers a robust solution for storing non-relational data but requires careful management due to potential risks like data corruption during long transactions and failovers. While BAG ensures data integrity, challenges arise from large file handling, streaming, and the absence of Read-Only replicas.","og_url":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/","og_site_name":"dbi Blog","article_published_time":"2023-10-06T20:44:06+00:00","article_modified_time":"2025-03-04T10:03:46+00:00","og_image":[{"width":1024,"height":1024,"url":"http:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png","type":"image\/png"}],"author":"Adrien Obernesser","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Adrien Obernesser","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/"},"author":{"name":"Adrien Obernesser","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2ab917212ce0200c7618afaa7fdbcd"},"headline":"The Case of FileTable with Basic Availability Groups","datePublished":"2023-10-06T20:44:06+00:00","dateModified":"2025-03-04T10:03:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/"},"wordCount":1119,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png","keywords":["Basic availability groups","FILESTREAM","FileTable","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/","url":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/","name":"The Case of FileTable with Basic Availability Groups - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png","datePublished":"2023-10-06T20:44:06+00:00","dateModified":"2025-03-04T10:03:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2ab917212ce0200c7618afaa7fdbcd"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/09\/Pasted-image-20230925140500.png","width":1024,"height":1024},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/the-case-of-filetable-with-basic-availability-groups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"The Case of FileTable with Basic Availability Groups"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2ab917212ce0200c7618afaa7fdbcd","name":"Adrien Obernesser","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dc9316c729e50107159e0a1e631b9c1742ce8898576887d0103c83b1ca3bc9e6?s=96&d=mm&r=g","caption":"Adrien Obernesser"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/adrienobernesser\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/153"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=28143"}],"version-history":[{"count":22,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28143\/revisions"}],"predecessor-version":[{"id":28538,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28143\/revisions\/28538"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/28165"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=28143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=28143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=28143"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=28143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}