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.
|
1 2 3 |
SELECT name, physical_name, state_desc FROM sys.master_files WHERE 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.
|
1 2 3 4 |
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.
|
1 2 3 4 |
SELECT TOP (1000) [CustomerID] ,[CustomerName] ,[Country] FROM [FileStreamDemoDB_Snapshot].[dbo].[customers] |
Now, let us try to access the FILESTREAM table.
|
1 2 3 4 |
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.
|
1 2 |
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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 |
USE [master] GO DROP 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).
|
1 2 |
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023