Rajendra Gupta
SQL Server FILESTREAM - TDE hierarchy

SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping

March 5, 2019 by

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.

SQL Server FILESTREAM - Database Snapshots

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.

SQL Server FILESTREAM - Database Primary and Secondary files

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.

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.

SQL Server FILESTREAM - Database Snapshot

We can access data from the user table stored in the primary data file.

Now, let us try to access the FILESTREAM table.

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.

Database Snapshot error on SQL Server FILESTREAM

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.

FILESTREAM column example

Rerun the select statement without specifying a FILESTREAM column.

We get the result as follows.

select statement without specifying a FILESTREAM column

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.

We receive the following error message.

Error in FILESTREAM Database Snapshot

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.

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)

SQL Server FILESTREAM - TDE hierarchy

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.

SQL Server FILESTREAM - Database 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).

SQL Server FILESTREAM - Database Mirroring error for SQL Server FILESTREAM

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.

SQL Server FILESTREAM - SQL Server Log shipping scenarios

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).

SQL Server FILESTREAM - Database Mirroring

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.

  1. A network drive (Source instance) for transaction log backup should have sufficient free space
  2. Log shipping copies the backups to the destination server directory. It should also have sufficient free space considering huge log backup size also
  3. 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
  4. 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
  5. You should monitor the log shipping process in the following aspects
    1. Log backup’s size
    2. We need to monitor the time taken in copying log backups to a destination server
    3. Restoration time for the log backups in destination server
    4. You should also set SQL Server Agent job failure alerts to avoid any excessive log growth

Table of contents

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
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)