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

I found the following when scripting the database: ALTER DATABASE [PlaneamientoComercialDB] ADD FILEGROUP [fsGroup] CONTAINS FILESTREAM GO Is there a way to alter the filegroup? — Daniel Martinez, Aug 21 '13 at 21:39

score 2 · Answer 1 · answered Feb 06 '14 at 19:48

2

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.

answered Feb 06 '14 at 19:48

user3281192

21
2

Bryan · Answer 2 · 2013-08-21T21:04:32.263

1

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:

enter image description here

edited Aug 21 '13 at 21:04

answered Aug 21 '13 at 20:21

Bryan

17,112
7
57
80

I did it on the principal, mirror and witness server but still get the same error. Ive also tried restarting the whole server Thanks – Daniel Martinez Aug 21 '13 at 20:27
Try disabling using SQL Server Configuration Manager – Bryan Aug 21 '13 at 21:04

score 0 · Accepted Answer · answered Aug 22 '13 at 02:05

0

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

answered Aug 22 '13 at 02:05

Daniel Martinez

397
4
20

score 0 · Answer 4 · answered Nov 01 '13 at 06:39

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

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

4 Answers4