June 7, 2018 at 3:46 am
Setup:
SQL Server 2017 instance running on server A
Several databases B,C,D.. exist on the instance, the data and log files are mounted on a SMB 3.1 shares on individual servers B,C,D.....
This setup is recommended by Microsoft:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-2017
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2011/10/18/sql-databases-on-file-shares-its-time-to-reconsider-the-scenario/
Details:
Server A is Windows Server 2016 Standard
SQL Server 2017 Standard 14.0.1000.169
Server B, C, D.. are Windows Server 2012 R2
SMB Shares are non-cached, non-encrypted
Problem:
Server B was rebooted. The database B became inaccessible as expected. After reboot, the database is still not available.
The error message reads:
Msg 823, Level 24, State 2, Line 2
The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x000000002d0000 in file '\\SHAREPATH_TO_B\Database_B.mdf'. Additional messages in the SQL Server error log and operating system error log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Attempts in SSMS at bringing the DB offline then online fails with the same error message. Detaching the database also fails with the message.
Current solution: A SQL Server restart brings the DB online again, but this causes all databases (C,D,E..) to restart as well.
How can we bring database B (or any other after a similar reboot of B,C,D..) online, without having to restart the SQL Server instance?
June 7, 2018 at 10:08 am
Restarting the share while the SQL Server has the database attached sounds like a terrible idea. You could very easily cause corruption. Generally it's a bad idea to attach a databade on a share for this very reason; are you able to migrate the database so that it's hosted on the same server?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 8, 2018 at 12:28 am
I agree it's a terrible idea, and it's not something we plan to do an a daily basis. We did this on a test database to simulate a real life outage, and to find out how we can recover from similar production outages in the future.
We can't host all the data on the local sever, it's integral to our business that the data is hosted on individual VMs.
At the moment it looks like we have to restart the SQL instance if any of the SMBs goes away for some reason (which should be very rare occasion, outside of planned maintenance windows).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply