How to disable Filestream on SQL Server 2008 R2 to enable mirroring?

Question

I'm enabling database mirroring on SQL Server 2008 R2, I've already configured my servers but when trying to start mirroring I get the following error:

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'PlaneamientoComercialDB'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A database cannot be enabled for both FILESTREAM storage and Database Mirroring. (Microsoft SQL Server, Error: 5574)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=5574&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I don't even have files on my database so I'd like to turn off filestream, how can I do it?

Thanks

Answer 1

Although I don't have any columns that uses FILESTREAM, for some reason I had a filegroup containing filestream. I could solve the issue by removing the filegroup

ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName

Thanks

Answer 2

FILESTREAM is configured at the server instance level. Use SQL Server Configuration Manager to disable. Under SQL Server Configuration Manager -> SQL Server Services. Find the service, right-click, properties:

Answer 3

Database Mirroring and FILESTREAM data not allowed

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”.

Answer 4

I ran into this issue when trying to test mirroring between 2 qa servers. Right click database --> Properties --> Filegroups

Remove filestream and click ok. Make sure to do on both servers or it will still fail.