SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping March 5, 2019 by Rajendra Gupta This article is the continuation of the SQL FILESTREAM series. SQL FILESTREAM is an exciting feature to store the objects into the FILESTREAM container but we might need to use the FILESTREAM database along with other SQL Server features. In this article, we are going to explore the SQL FILESTREAM feature compatibility with other SQL Server features. Database Snapshots and SQL FILESTREAM Database Snapshots provide a read-only and static copy of the SQL Server database. They create a blank copy of the source database. If we try to access the data from a Snapshot database, it points to the source database. Once we perform any changes to the source database, the original page is copied to the snapshot database. In the following image, you can see the source and snapshot database. The snapshot database contains the original pages before the modification. In the following example, page 3 is copied to the snapshot database before modification in the source database. In this article, we will focus on the Database Snapshot for a SQL FILESTREAM database. We already have a FILESTREAM database configured in our environment. We can get the information about data and log file using sys.master_files. 123 SELECT name, physical_name, state_descFROM sys.master_filesWHERE database_id = DB_ID(N'FileStreamDemoDB_test') In the FILESTREAM database, we have the primary data file along with the FILESTREAM container. The FILESTREAM container stores the objects in the file system. In the following query, we created the Snapshot database on the Primary data file for the FILESTREAM database. 1234 CREATE DATABASE FileStreamDemoDB_Snapshot ON PRIMARY (NAME = FileStreamDemoDB, FILENAME = 'C:\sqlshack\SQLDB\FileStreamDemoDB_test.snap')AS SNAPSHOT OF FileStreamDemoDB_test;GO We can access the Database Snapshots in a separate folder under Databases. Look at the camera icon in front of a Database Snapshot. It shows that the Database Snapshot is an image of the source database. We can access data from the user table stored in the primary data file. 1234 SELECT TOP (1000) [CustomerID] ,[CustomerName] ,[Country] FROM [FileStreamDemoDB_Snapshot].[dbo].[customers] Now, let us try to access the FILESTREAM table. 1234 SELECT TOP (1000) [FileId] ,[FileName] ,[File] FROM [FileStreamDemoDB_Snapshot].[dbo].[DemoFileStreamTable_1] We received the following error message. It indicates that large object data for table resides on an offline filegroup. It considers the FILESTREAM filegroup as on offline filegroup because we did not include it while creating a database snapshot. SQL FILESTREAM stores the FILESTREAM objects into the FILESTREAM filegroup. It stores the rest of data in primary filegroup. We did not specify FILESTREAM filegroup while creating the Database Snapshot. We can still query the FILESTREAM table without specifying the FILESTREAM column. We have the following FILESTREAM column in this example. Rerun the select statement without specifying a FILESTREAM column. 12 SELECT TOP (1000) [FileId,[FileName]FROM [FileStreamDemoDB_Snapshot].[dbo].[DemoFileStreamTable_1] We get the result as follows. We can create multiple Database Snapshots for a database. We need to provide a unique name for the Database Snapshot regardless it is pointing to a similar database. Let us create another Database Snapshot with FILESTREAM filegroup as well. Execute the following query to create a Database Snapshot on FILESTREAM filegroup as well. 1234567 CREATE DATABASE FileStreamDemoDB_Snapshot_FS ON PRIMARY(NAME = FileStreamDemoDB, FILENAME = 'C:\sqlshack\SQLDB\FileStreamDemoDB_test.snap'), FILEGROUP [DemoFileStream] CONTAINS FILESTREAM(NAME = DemoFiles,FILENAME = 'C:\sqlshack\Demo\DemoFiles_fs') AS SNAPSHOT OF FileStreamDemoDB_test;GO We receive the following error message. We can create a database snapshot of a SQL FILESTREAM enabled database however; it must not include the FILESTREAM filegroup. The database Snapshot does not support the FILESTREAM property. If you are using the database for the relational tables as well as FILESTREAM feature also, Database Snapshot allows querying the tables pointing to source database easily. In this case, we should be cautious not to include FILESTREAM columns in select statements. Drop the Database Snapshot similar to a standard database using the Drop Database statement. 1234 USE [master]GODROP DATABASE [FileStreamDemoDB_Snapshot]GO Transparent Data Encryption and SQL FILESTREAM Transparent Data Encryption ( TDE) encrypts and decrypts data and log files in real-time. TDE protects data and log file by encrypting them with certificates. We cannot access data without decrypting with a valid certificate. You can see the overall TDE process in this diagram (Reference article TDE) Suppose we are using the FILESTREAM database for the standard database activities along with the FILESTREAM functionality. We require SQL Server data to be protected. It will also require encrypting data stored in FILESTREAM container as well. We can enable the TDE for the FILESTREAM database. However, it does not protect FILESTREAM data. We can take reference of MSDN for the combination of TDE and SQL FILESTREAM. “FILESTREAM data is not encrypted even when TDE is enabled.” SQL FILESTREAM and Database Mirroring SQL Server database mirroring is a high availability feature of the SQL Server database. It prepares a primary and secondary copy a database between two different SQL Server instances. In case of any issues with the primary instance goes down, the secondary database becomes available for the users. We might require configuring database mirroring on a FILESTREAM enabled database. SQL Server does not allow configuring database mirroring on a FILESTREAM database. It is enabled on a database level. Therefore, we cannot configure mirroring on this database. If we have already configured the database mirroring, we cannot modify this database to use the FILESTREAM feature. SQL Server does not allow adding a FILESTREAM filegroup to the existing mirrored database as well. You can still go to database properties and go to Mirroring. You can still go to configure database mirroring between Principal and Mirror instances but in a later stage when we try to start mirroring you will get an error message. A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage (Microsoft SQL Server, Error 5574) You can check the corresponding error message in sys.messages table as well. In the following query, we filtered the error 5574 in English language ( language_id 1033). 12 select message_id, text from sys.messages where message_id=5574 and language_id=1033 SQL FILESTREAM requires high usage of the log data compared to a standard database. We might insert or update the FILESTREAM object, and it requires changes to be captured in the log and sent over the mirror database. In case of update statement, FILESTREAM creates another copy of the FILESTREAM object, which again creates overhead for the SQL Server. Due to this reason, SQL Server does not allow configuring database mirroring on FILESTREAM database. SQL FILESTREAM and log shipping SQL Server log shipping works on taking regular log backups of the database and restoring on the secondary database in another instance. It provides high availability in case of any issues on the primary database. In my earlier articles (see TOC at the bottom), we explored the log backups for the FILESTREAM database. We can configure log shipping for SQL FILESTREAM database. Both the instances should be running SQL Server 2008 R2 or later version. You can understand log shipping better by viewing the following image. To configure log shipping, right click on the database and click on Ship Transaction Logs. It opens the log shipping configuration wizard. Configure the network path on the source and destination SQL Server instance, backup, copy and restore job frequency, database recovery state (Standby or NoRecovery mode). In the SQL FILESTREAM database, log backup includes FILESTREAM container objects as well. It might cause a larger log file size. We need to consider the following points before the log-shipping configuration. A network drive (Source instance) for transaction log backup should have sufficient free space Log shipping copies the backups to the destination server directory. It should also have sufficient free space considering huge log backup size also If the log backup size is huge, it might take time for the log shipping to copy the log backup from source to destination server. It might influence the network bandwidth. Therefore, you should consider this as well before implementation If there are multiple changes in the FILESTREAM database, log backup might take longer to copy FILESTREAM container objects into log backups. You should also set the log backup frequency considering this point as well You should monitor the log shipping process in the following aspects Log backup’s size We need to monitor the time taken in copying log backups to a destination server Restoration time for the log backups in destination server You should also set SQL Server Agent job failure alerts to avoid any excessive log growth Table of contents FILESTREAM in SQL Server Managing data with SQL Server FILESTREAM tables SQL Server FILESTREAM Database backup overview Restoring a SQL Server FILESTREAM enabled database SQL Server FILESTREAM database recovery scenarios Working with SQL Server FILESTREAM – Adding columns and moving databases SQL Server FILESTREAM internals overview Importing SQL Server FILESTREAM data with SSIS packages SQL Server FILESTREAM queries and Filegroups Viewing SQL Server FILESTREAM data with SSRS SQL Server FILESTREAM Database Corruption and Remediation Export SQL Server FILESTREAM Objects with PowerShell and SSIS SQL FILESTREAM and SQL Server Full Text search SQL Server FILESTREAM and Replication SQL Server FILESTREAM with Change Data Capture Transaction log backups in a SQL FILESTREAM database SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping SQL Server FILETABLE – the next generation of SQL FILESTREAM Managing Data in SQL Server FILETABLEs SQL Server FILETABLE Use Cases About Latest Posts Rajendra GuptaRajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features. While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.He can be reached at firstname.lastname@example.orgView all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Creating a SQL Server Database using DBATools - May 23, 2019 SQL IF Statement introduction and overview - May 20, 2019 SQL Notebooks introduction and overview - May 20, 2019 Related posts: Database snapshot in SQL Server How to create SQL Server Log Shipping What is SQL Server log shipping? SQL Server FILESTREAM Database backup overview What is SQL Server database mirroring?