January 10, 2017 at 11:15 am
Hello everyone,
We are having a little difficulty with filestream access on one of our SQL instances. The instance was migrated from one physical machine to a different physical machine and we have almost everything working properly.
But filestream access fails to be enabled no matter what I try. I have configured filestream access from SQL Server Configuration Manager to mimic how it was set up on the old server, but no matter what I try, it fails to be enabled via TSQL.
I get the error:
Msg 5597, Level 16, State 1, Line 11
FILESTREAM feature could not be initialized. The Windows Administrator must enable FILESTREAM on the instance using Configuration Manager before enabling through sp_configure.
This is a TEST/DEV instance so it is not a high priority.
What I have tried:
dropping the database that uses filestream (at which point I am unable to get re-attached as it requires filestream to be enabled to attach it)
turning off and on the filestream access via SQL Server Configuration Manager
verifying the shares by running the command "net share" and I can see that the share exists
Repair install of the SQL Instance
restarting the SQL instance multiple times with different filestream settings
and I am now out of ideas. Any thoughts on things I can try? My next thought is to do a fresh install of SQL and attach the database but I'd prefer (if possible) to get it working as is without installing a whole new SQL instance.
This is with SQL version 11.0.6020.0 (X64) - SQL Server 2012 (SP3) (KB3072779).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 10, 2017 at 11:54 am
I've seen the order of the steps needing to be done in this order, if you want to try:
Enable Filestream in Configuration Manager and select all options (IO, remote)
After that, in a query
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO
And then restart.
Sue
January 10, 2017 at 11:58 am
First half of that works, but the second half fails.
the "Enable Filestream in Configuration Manager and select all options (IO, remote)" step is already done (also tried cycling the options but that didn't help as well as changing the share name). When I go to set filestream_access_level to 2, I get the error I posted below.
I'm thinking something is screwed up due to the file copy which included the system databases. I am thinking that one of the system databases isn't reading that the filestream is configured properly but I am not sure why or where to look.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 10, 2017 at 12:28 pm
Not sure. You could check the settings on the server for now just to see using:
SELECT SERVERPROPERTY('FilestreamConfiguredLevel')
SELECT SERVERPROPERTY('FilestreamEffectiveLevel')
They probably say 0 but worth a check.
There were bugs with it earlier on 2008, not sure if that workaround would work on 2012. It's a WMI script to enable it. See this post for more info:
Sue
January 10, 2017 at 12:38 pm
bmg002 (1/10/2017)
First half of that works, but the second half fails.the "Enable Filestream in Configuration Manager and select all options (IO, remote)" step is already done (also tried cycling the options but that didn't help as well as changing the share name). When I go to set filestream_access_level to 2, I get the error I posted below.
I'm thinking something is screwed up due to the file copy which included the system databases. I am thinking that one of the system databases isn't reading that the filestream is configured properly but I am not sure why or where to look.
I'm inclined to think that the bolded bit in your comment is the source of your pain. Everything I've ever come across indicates it's generally a bad idea and doesn't work well to migrate your system DBs.
You're generally better off just scripting out your logins, etc and re-creating them, if you're moving to a new server.
Jason
January 10, 2017 at 12:42 pm
Thanks for the quick reply.
I ran the commands you suggested and got results that I was not expecting:
3 for the first one (FilestreamConfiguredLevel) and 0 for thes second (FilestreamEffectiveLevel). I was expecting the second, but not the first. I thought it would be 2, not 3.
But followed the link, found the script and ran it as:
C:\itsupport>cscript .\filestream_enable.vbs /Machine:. /Instance:<NAME> /Level:3 /Share:<NAME>
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
Machine: .
Instance: <NAME>
Level: 3
ShareName: <NAME>
Current Filestream configuration:
InstanceName = <NAME>
AccessLevel = 3
ShareName = <NAME>
RsFxVersion = 0201
Calling method EnableFilestream with new level
Method executed successfully
New Filestream configuration:
InstanceName = <NAME>
AccessLevel = 3
ShareName = <NAME>
RsFxVersion = 0201
BUT I am getting the exact same errors. Thinking a fresh install and then attaching the database or restoring from backup is going to be my only option.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 10, 2017 at 12:48 pm
Yeah, I am thinking it is the system databases that are causing my headache.
Scripting out the logins would have been a huge pain in the behind which is why we went with the route that we did. We have virtual hosts set up so the server name shouldn't change, just the physical box that it is hosted on. Stop the database, move the files, start the database on the new host. It worked with the other test DB's that we had, but none had filestream access. Even our service broker went across without a hitch.
Just filestream access broke and I can't understand why that would fail when everything else succeeded. We were moving nearly 50 SQL instances to new physical servers, several which have over 400 logins and users across multiple databases.
Thankfully it is only one test instance that seems to be affected, as we don't use filestream access for much, I am thinking that killing the instance and rebuilding from scratch (instead of from the old system) is my only option. I am glad that the other migrations went so smoothly, just wish I knew what to look for or at to figure out this weird filestream issue.
EDIT - first, sorry for the double post instead of editing. That was a mistake on my part. Second - I may have found something! It looks like the RsFx file is a different version. well, kind of. The physical file has the same version, but for some reason script I ran and the file share show "RsFx Version 0201" but on the old server it was 0202. I bet that version change is causing the headache.
I'm going to try re-installing SP3 for that instance and see what all explodes.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 20, 2021 at 6:41 pm
I have same problem after installing sp3 on sql server 2008 r2 with databases with filestream. Before installation all work well. After instalation the same as above happend and all database with filstream don't (system has replication configured too). I try to uninstal SP3 but this failed. Did you found resolution because my system is in production.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply