[NotSupported] SQL Server will not recognize shared folder for database files

Discussions about using Windows guests in VirtualBox.
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

[NotSupported] SQL Server will not recognize shared folder for database files

Post by MarkFoley »

I've just created a Windows 7 guest on a Linux host and installed SQL Server on the Windows guest. My objective is to have the database files (.mdf, .ldf) on a shared folder. However, according the Microsoft documentation, "SQL Server Setup will block installations on read-only, mapped, or compressed drives." And further says: "The supported storage types for data files are: Local Disk, Shared Storage, SMB File Share."

Now, I'm reading all over the Internet about why it's a great idea to have database servers run as VMs, so people must be doing this. Moreover, I cannot imagine that the database data files, which could take up terabytes for some implementation, are all stored within the VM. If so, it would be faster to re-installing SQL Server from scratch and restore database backup than it would be to restore a gigantic VM image backup.

The Microsoft forums insists this can't be done, but again, surely it *is* being done in real life. Microsoft does list "Shared Storage" and "SMB File Shares" as supported. They don't really define what they mean by "Shared Storage". Apparently, Virtualbox shared folders don't qualify. Also, I'm not sure how "SMB File Shares" are distinct from "mapped" drives (how would one access an SMB Share if not via mapping?).

Has anyone here done this with SQL Server data files and know of a way to make this work (short of the trace flag 1807 kludge)?
Last edited by socratis on 20. Dec 2019, 20:57, edited 1 time in total.
Reason: Marked as [NotSupported].
scottgus1
Site Moderator
Posts: 20965
Joined: 30. Dec 2009, 20:14
Primary OS: MS Windows 10
VBox Version: PUEL
Guest OSses: Windows, Linux

Re: SQL Server will not recognize shared folder for database files

Post by scottgus1 »

MarkFoley wrote: Virtualbox shared folders don't qualify.
This. If your 'Virtualbox shared folders' are Guest Additions Shared folders, then, this. GASF's don't implement a full shared folder experience, they're just for file transfer.

You should set up a Virtualbox network between host and guest. It's a real TCP/IP network, just like wired Ethernet. Then you can use typical Windows Shared Folders (also called SMB shares) on the Virtualbox network. You can also map these shared folders to drive letters in the guest OS if you want.

Use Host-Only if you want the guest to only access folders on the host drives, and Bridged if you want the guest to act like just another PC on the office network, and full communication between the guest and all devices in the LAN.
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

Re: SQL Server will not recognize shared folder for database files

Post by MarkFoley »

scottgus1: "You should set up a Virtualbox network between host and guest. It's a real TCP/IP network, just like wired Ethernet."

Sounds plausible. Since the Microsoft doc said "SMB File Shares" are supported. So, do you mean I create a Samba share on the host? I.e. smb/nmb?
BillG
Volunteer
Posts: 5102
Joined: 19. Sep 2009, 04:44
Primary OS: MS Windows 10
VBox Version: PUEL
Guest OSses: Windows 10,7 and earlier
Location: Sydney, Australia

Re: SQL Server will not recognize shared folder for database files

Post by BillG »

Microsoft Shared Storage usually refers to iSCSI devices used for database file storage of vm failover clusters. The shared storage can be accessed from any server in the cluster.

https://www.virtualizationhowto.com/201 ... rver-2016/

What is going to access the SQL server? The Linux host? That won't be easy even if you have network access between them.
Bill
socratis
Site Moderator
Posts: 27330
Joined: 22. Oct 2010, 11:03
Primary OS: Mac OS X other
VBox Version: PUEL
Guest OSses: Win(*>98), Linux*, OSX>10.5
Location: Greece

Re: SQL Server will not recognize shared folder for database files

Post by socratis »

Bottom line is that VirtualBox Shared Folders (VBoxSFs) are not suitable for this. VBoxSFs present a very simplified file system implementation, just enough to read/write files from/to the guest. Many applications can error when using VBoxSFs, because they expect advanced features, for example file locking, access controls, etc., which don't exist as a concept for VBoxSFs.

Treat the Host/VM connection as you would a real connection between a Server and a Client, be it SMB/CIFS, iSCSI, other.
Do NOT send me Personal Messages (PMs) for troubleshooting, they are simply deleted.
Do NOT reply with the "QUOTE" button, please use the "POST REPLY", at the bottom of the form.
If you obfuscate any information requested, I will obfuscate my response. These are virtual UUIDs, not real ones.
scottgus1
Site Moderator
Posts: 20965
Joined: 30. Dec 2009, 20:14
Primary OS: MS Windows 10
VBox Version: PUEL
Guest OSses: Windows, Linux

Re: SQL Server will not recognize shared folder for database files

Post by scottgus1 »

MarkFoley wrote:So, do you mean I create a Samba share on the host? I.e. smb/nmb?
socratis wrote:Treat the Host/VM connection as you would a real connection between a Server and a Client, be it SMB/CIFS, iSCSI, other.
This. When the guest and host are connected Host-Only or Bridged, you have a real Ethernet network between host and guest. You can do with that network just about anything you can do with a wired Ethernet network. Treat your guest and host as two PCs side by side, networked together, and link them up as per usual.
socratis
Site Moderator
Posts: 27330
Joined: 22. Oct 2010, 11:03
Primary OS: Mac OS X other
VBox Version: PUEL
Guest OSses: Win(*>98), Linux*, OSX>10.5
Location: Greece

Re: SQL Server will not recognize shared folder for database files

Post by socratis »

scottgus1 wrote:When the guest and host are connected Host-Only or Bridged
I would suggest the HostOnly if the VM does not need other LAN/WAN access. Simpler, safer, more robust...
Do NOT send me Personal Messages (PMs) for troubleshooting, they are simply deleted.
Do NOT reply with the "QUOTE" button, please use the "POST REPLY", at the bottom of the form.
If you obfuscate any information requested, I will obfuscate my response. These are virtual UUIDs, not real ones.
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

Re: SQL Server will not recognize shared folder for database files

Post by MarkFoley »

I created a samba share on the Linux host, and mapped it from the Windows VM guest, thus a CIFS connection per scottgus1's recommendation. I'm still getting the "The path S:\ is not a recommended drive Type." I've also tried \\dbvmhost\database where dbvmhost is the Linux VM host and "database" is the samba share name, same error. As mentioned in my initial post, the Microsoft documentation says, "SQL Server Setup will block installations on read-only, mapped, or compressed drives." And further says: "The supported storage types for data files are: Local Disk, Shared Storage, SMB File Share." If \\dbvmhost\database is not a SMB file share, what is it?

BillG: not sure where your pointing me with your link: "iSCSI Shared Storage in Windows Server 2016". I do not have a Windows Server in the mix.

This SQL Server is not for local-only access. It will (eventually) be accessed by other hosts on the LAN via port 1433 or the like.

Any other ideas on how I can get this to work? Seems like it should be doable.
socratis
Site Moderator
Posts: 27330
Joined: 22. Oct 2010, 11:03
Primary OS: Mac OS X other
VBox Version: PUEL
Guest OSses: Win(*>98), Linux*, OSX>10.5
Location: Greece

Re: SQL Server will not recognize shared folder for database files

Post by socratis »

MarkFoley wrote:Any other ideas on how I can get this to work?
Start asking in the SQL forums. If the VM is using Bridged or HostOnly mode, and you're not using VirtualBox Shared Folders, but a true SMB share, then it's not a VirtualBox issue, it's an SQL issue...
Do NOT send me Personal Messages (PMs) for troubleshooting, they are simply deleted.
Do NOT reply with the "QUOTE" button, please use the "POST REPLY", at the bottom of the form.
If you obfuscate any information requested, I will obfuscate my response. These are virtual UUIDs, not real ones.
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

Re: SQL Server will not recognize shared folder for database files

Post by MarkFoley »

OK, I've posted in a Microsoft forum: https://social.technet.microsoft.com/Fo ... ade&ppud=4. I will post back with any insights.

The VM is using Bridged networking.
scottgus1
Site Moderator
Posts: 20965
Joined: 30. Dec 2009, 20:14
Primary OS: MS Windows 10
VBox Version: PUEL
Guest OSses: Windows, Linux

Re: SQL Server will not recognize shared folder for database files

Post by scottgus1 »

MarkFoley wrote:The VM is using Bridged networking.
Just double check there's a fully functioning network on the host PC adapter you're Bridging to. Bridged acts as if there's an unmanaged network switch behind your PC and the PC and guest are plugged into it.There needs to be a running network beyond the switch for the guest to truly connect. As a test, make sure Ping works.
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

Re: SQL Server will not recognize shared folder for database files

Post by MarkFoley »

scottgus1 wrote:Just double check there's a fully functioning network on the host PC adapter you're Bridging to. Bridged acts as if there's an unmanaged network switch behind your PC and the PC and guest are plugged into it.There needs to be a running network beyond the switch for the guest to truly connect. As a test, make sure Ping works.
The network seems to be working. I can Samba-map drives on other network hosts, I can Remote Desktop to/from other Windows host on the network, I can see other host in Windows Explorer > Network. Interestingly, I can ping from the VM, but not to the VM from another LAN host. Perhaps a firewall setting.
scottgus1
Site Moderator
Posts: 20965
Joined: 30. Dec 2009, 20:14
Primary OS: MS Windows 10
VBox Version: PUEL
Guest OSses: Windows, Linux

Re: SQL Server will not recognize shared folder for database files

Post by scottgus1 »

MarkFoley wrote:I can ping from the VM
Good, so the network is working.
MarkFoley wrote:but not to the VM...Perhaps a firewall setting
MarkFoley wrote:a Windows 7 guest
Windows defaults to having ping blocked in the Windows firewall. In the guest, turn on "File and Printer Sharing (Echo Request - ICMPv4-In)" for Private and possibly Domain networks. (On Windows hosts, the host PC firewall typically does not interfere with the Bridged guest traffic. Bridged connects earlier in the network stack than the host firewall does. Maybe the same for Linux hosts?)

With a good network, now the troubleshooting goes to pretending your virtual machine is a real machine and figure out how to connect SQL between Windows and Linux over Ethernet.

Hope you find success!
MarkFoley
Posts: 150
Joined: 19. Aug 2016, 03:20

Re: SQL Server will not recognize shared folder for database files

Post by MarkFoley »

I've checked on the Microsoft Technet forum. I was trying to change the program location during installation. I went ahead and kept C: as the program location then in Management Studio > right-click Instance > Properties > Database Settings > [Data | Log | Backup]. I was able to change the location there. I had to restart the SQL Server services for it to "take". No complaints. However, when I tried to restore a database to that location I got an error, something like "the system cannot find the path specified." So, in the end, I've given up this idea for now. I don't want to make it a career. Instead, I've located the database files on the C: drive as per normal and as per recommended by the TechNet forum: https://social.technet.microsoft.com/Fo ... ade&ppud=4

We'll see what kind of backup sizes I end up with in this scenario. I have to believe it's doable, but I need to move on.

(Sorry, I forget how to mark this post as SOLVED, or GIVE UP, or whatever)
socratis
Site Moderator
Posts: 27330
Joined: 22. Oct 2010, 11:03
Primary OS: Mac OS X other
VBox Version: PUEL
Guest OSses: Win(*>98), Linux*, OSX>10.5
Location: Greece

Re: SQL Server will not recognize shared folder for database files

Post by socratis »

Actually I think I'm going to mark this as [NotSupported] after all...
Do NOT send me Personal Messages (PMs) for troubleshooting, they are simply deleted.
Do NOT reply with the "QUOTE" button, please use the "POST REPLY", at the bottom of the form.
If you obfuscate any information requested, I will obfuscate my response. These are virtual UUIDs, not real ones.
Locked