SQL Server 2008: How to get FILESTREAM and partitioning to work

One of the things I struggled with earlier in the year while writing a SQL Server 2008 training course for Microsoft was how to get FILESTREAM to work with partitioning. There wasn’t (and still isn’t) any information in Books Online that I could find so I had to play around to figure it out.

I should say that the CTP-6/February CTP version of Books Online *does* have a bunch of code examples around using FILESTREAM, so I’m not going to write a blog post about that. Look in the Getting Started with FILESTREAM Storage section.

Back to partitioning – first I created a test database:

CREATE DATABASE FileStreamTestDB
ON PRIMARY
   (NAME = FileStreamTestDB_data,
      FILENAME = N‘C:\Metro Demos\FileStreamTestDB\FSTestDB_data.mdf’),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
   (NAME = FileStreamTestDBDocuments,
      FILENAME = N‘C:\Metro Demos\FileStreamTestDB\Documents’)
LOG ON
   (NAME = ‘FileStreamTestDB_log’,
      FILENAME = N‘C:\Metro Demos\FileStreamTestDB\FSTestDB_log.ldf’);
GO

Then I tried the obvious, knowing that I can’t partition on the ROWGUIDCOL:

CREATE PARTITION FUNCTION MyPartFunction (INT) AS RANGE RIGHT FOR VALUES (1000, 2000);

CREATE PARTITION SCHEME MyPartScheme AS PARTITION MyPartFunction ALL TO ([PRIMARY]);

CREATE TABLE FileStreamTest (
   TestId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   Customer INT,
   [Name] VARCHAR (25),
   [Document] VARBINARY(MAX) FILESTREAM)
ON MyPartScheme (Customer);
GO

Partition scheme ‘MyPartScheme’ has been created successfully. ‘PRIMARY’ is marked as the next used filegroup in partition scheme ‘MyPartScheme’.
Msg 1921, Level 16, State 4, Line 8
Invalid filegroup ‘default’ specified.

Eventually I worked out that you have to define a separate partitioning scheme just for FILESTREAM data. This is because the regular data is stored on non-FILESTREAM filegroups, so trying to use the regular partitioning scheme for FILESTREAM would mean telling the Engine to store the FILESTREAM data in non-FILESTREAM filegroups. Clearly a non-starter. Ok – try again with a separate partitioning scheme (the prior MyPartFunction partition function and MyPartScheme partition scheme already exist now remember):

CREATE PARTITION SCHEME MyFSPartScheme AS PARTITION MyPartFunction ALL TO ([FileStreamFileGroup]);

CREATE TABLE FileStreamTest (
   TestId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   Customer INT,
   [Name] VARCHAR (25),
   [Document] VARBINARY(MAX) FILESTREAM)
ON MyPartScheme (Customer)
FILESTREAM_ON MyFSPartScheme;
GO

Partition scheme ‘MyFSPartScheme’ has been created successfully. ‘FileStreamFileGroup’ is marked as the next used filegroup in partition scheme ‘MyFSPartScheme’.
Msg 1908, Level 16, State 1, Line 1
Column ‘Customer’ is partitioning column of the index ‘UQ__FileStreamTest__03317E3D’. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Hmm – I can’t partition on Customer because there’s already a unique index over TestId – UNLESS I specifically set the unique index on TestId to be non-partitioned by setting a filegroup for it:

CREATE TABLE FileStreamTest (
   TestId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE ON [PRIMARY],
   Customer INT,
   [Name] VARCHAR (25),
   [Document] VARBINARY(MAX) FILESTREAM)
ON MyPartScheme (Customer)
FILESTREAM_ON MyFSPartScheme;
GO

That works! Now – the BIG issue with this setup is that switching partitions won’t work while the unaligned index is enabled. So how to disable it? First we need to find out what it’s called:

SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID (‘FileStreamTest’);
GO

And then disable it:

ALTER INDEX UQ__FileStre__8CC33161060DEAE8 ON FileStreamTest DISABLE;