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

2020-08-12 12_30_53-Window

and in SSMS

2020-08-12 12_31_19-Window

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.

2020-08-12 13_54_32-Window

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.

2020-08-12 12_52_32-Window

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

2020-08-12 12_52_28-Window

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

2020-08-12 12_50_47-Window

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

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate