Database Mirroring and FILESTREAM data not allowed – or is it?

In a previous blog entry, I talked about the new FILESTREAM data capability in SQL Server 2008 where we can store Binary Large Object (BLOB) data on the file system but have it transactionally part of the database. This feature allows the database files to remain a reasonable size for performance while allowing protection and unlimited storage of BLOB columns. But what if we want to set up a Database Mirroring partnership with such a database?

In my previous testing and in subsequent SQL Server 2008 classes, I used the AdventureWorks2008 sample database which is set up for Filestream data.  We use this test database for almost all the labs in our new 2008 courses. The danger here is that as a trainer, I get lost in the virtual reality of the imaginary AdventureWorks bicycle company which specializes in selling expensive mountain bikes. Yes, I do need the occasional consulting gig to keep me honest and get me out of the AdventureWorks comfort zone.

We use the AdventureWorks2008 database for our Filestream lab to show how to setup and use filestream data. We also use the same database for the Database Mirroring lab which shows that you can synchronize a database across multiple servers with automatic failover without a cluster. In my blog entry, I assumed therefore that Database Mirroring supported Filestream data. “Wrong!” - a knowledgeable reader pointed out that Books Online clearly states that this is not the case. “Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.”

But we do our Database Mirroring lab using a database that is setup for Filestream data. So what’s the truth? I said I would investigate and I did. In our lab, we backup the AdventureWorks2008 database. We restore it to a server designated as the Mirror server. We then setup a Database Mirroring partnership and all works fine including manual and automatic failover. Normal datatypes are synchronized correctly.

But what about the Filestream data? The Filestream data in the sample database is actually stored in a table named Production.Document  where technical documents for products and their specifications can be stored. This table contains the varbinary(max) datatype column that has the Filestream attribute. Hang on, the whole table has gone missing! It turns out that some bright spark at Microsoft Learning created a special version of AdventureWorks2008 without the Production.Document table so that the Database Mirroring lab would work. A special VM named HOU-SQL-01 contains this customized database. A different VM is used for the Filestream lab. Mystery solved.

Just to verify, I tested out Database Mirroring with the full version of AdventureWorks2008 and, although it let me setup the Mirroring endpoints, it did not allow Mirroring to start giving an error 5574: “A database cannot be enabled for both FILESTREAM storage and Database Mirroring”.

So, although I doubted Books Online, it is right again. At the end of the day you cannot synchronize Filestream data using Database Mirroring.

My next question is “Why?”

In the famous words of Tennyson: “Ours is not to reason why, ours is but to do and die!”

Cheers

Brian

Recent Posts:

Business Intelligence Security? Same old story...

Business Intelligence load balancing? Have your check book ready…

A computer science degree: helpful but not necessary

The Mother-of-all VPC’s for Business Intelligence!

• Microsoft
• books online
• Database Mirroring
• filestream
• mirroring
• SQL Server
• SQL Server 2008