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:\SQLServerData\SQL2017\FSTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), FILEGROUP [FSFG] CONTAINS FILESTREAM ( NAME = N'FSData', FILENAME = N'D:\SQLServerData\SQL2017\FSData' ) LOG ON ( NAME = N'FSTest_log', FILENAME = N'D:\SQLServerData\SQL2017\FSTest_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:\Documents\Using 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.
Pingback: Don’t Forget Unique with FILESTREAM–#SQLNewBlogger | Voice of the DBA