SQL Clone Works with FILESTREAM

SQL Clone is an amazing product that virtualizes your data, allowing multiple instances to share a read only image, but still produce writeable databases that look normal to SQL Server. It’s similar to how a container appears to a user, but this uses real SQL Server instances.

I need to write up a more detailed walkthrough of this, but someone asked the question today about SQL Clone and FILESTREAM and I didn’t see a proper article on the Redgate site, so I decided to run a test and post this.

Setting up FILESTREAM

We have some articles at SQLServerCentral on FILESTREAM, but essentially this feature uses a folder on your instance file system to store blog files, instead of putting them in the database. To enable this, you need to do it in Configuration Manager

and in SSMS

You do need to restart the database engine, but then you can create a database that includes a FILESTREAM filegroup.

CREATE DATABASE [FSTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FSTest', FILENAME = N'D:SQLServerDataSQL2017FSTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [FSFG] CONTAINS FILESTREAM 
( NAME = N'FSData', FILENAME = N'D:SQLServerDataSQL2017FSData' )
 LOG ON 
( NAME = N'FSTest_log', FILENAME = N'D:SQLServerDataSQL2017FSTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

Once this is done, create a table and load some data.

CREATE TABLE Books
(   BookKey     INT              IDENTITY(1, 1)
  , BookTitle   VARCHAR(100)
  , FSGIUD      UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL
        DEFAULT NEWID()
  , BookContent VARBINARY(MAX)   FILESTREAM);
GO
DECLARE @Document AS VARBINARY(MAX)
 
-- Load the image data
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            'E:DocumentsUsing Local and Hosted Agents for Build with Azure DevOps.docx',
            SINGLE_BLOB ) AS Doc
 INSERT dbo.Books
     (BookTitle, FSGIUD, BookContent)
 VALUES
     ('Using Local and Hosted Agents for Build with Azure DevOps'   -- varchar(100)
    , NEWID() -- uniqueidentifier
    , @Document
     )         
 ;
GO

This gives you a database table with some data in SQL Server, in the Books table, and some in this folder, FSData, on your file system.

DO NOT mess with this folder, but the contents here will be included in any backup.

Now, I showed how to make an image and clone in a previous post, which I’ll do from this database.

When I get done, I’ll deploy this to another instance. In this case, I was worried about a folder issue on the same instance, but I’ll test that another day.

Whoops, I need FILESTREAM on the second instance.

I’ll follow the same config steps as above and restart this instance. this time things work.

If I script the table, I see it is  a FILESTREAM enabled table.

That’s it for now, but I’ll get a proper article written for the Redgate Hub.