I understand that Microsoft does not recommend using NAS for SQL servers (http://support.microsoft.com/?id=304261). I have tried and failed to make Drobo FS the location for the database files of one of my apps that uses SQL. This is a bummer. Drobo tech support suggested that I raise this with the Drobo community, in case someone has a workaround. Can the Drobo FS be configured so that I can use it with SQL server?
Thanks.
I’m no expert on SQL Server, but the question interested me so I read the article you point to.
It seems pretty clear as to why they don’t support it - basically performance, data consistency and reliability issues, which are probably the main reasons you are using an SQL database in the first place. This makes me even more confused as to why you want to do this if it puts your data at risk.
If you want to run SQL servers with the files on a Drobo, you need a DroboPro or a DroboElite I think, which would probably qualify as a SAN to SQL Server.
Can you use the Drobo FS to just be the backup location for the database? They say they support backup to NAS, just not keeping the actual working files on the NAS.
This section of the article you point at seems to tell you what to do, but it is surrounded by “don’t do this” language, so I suggest you don’t:
In SQL Server 7.0, without trace flag 1807, if you use the DISK INIT backward-compatible syntax followed by a CREATE DATABASE statement with either mapped or UNC syntax, error 5105 occurs.
In SQL Server 7.0, with trace flag 1807, if you use the DISK INIT backward-compatible syntax followed by a CREATE DATABASE statement with mapped syntax, the file creation succeeds. If you use DISK INIT with UNC syntax, error 5105 occurs.
In SQL Server 2005, SQL Server 2000, or SQL Server 7.0, without trace flag 1807, if you execute a CREATE DATABASE statement with mapped or UNC syntax, in SQL Server 7.0 error 5105 occurs and in SQL Server 2000 error 5110 occurs.
In SQL Server 2005, SQL Server 2000, or SQL Server 7.0, with trace flag 1807, a CREATE DATABASE statement performed with mapped or UNC syntax succeeds.
Thank you. The question, I think, boils down to whether Drobo FS can be configured to allow access to an SQL server running as a network service (an alternative I tried was to configure the SQL server to log on as a Local Client, but this didn’t help). Drobo Support says they don’t know how to do that, and suggested I start a thread here, in case someone knows a 3rd part app that does that.
I am no SQL expert either, so the advice in the Microsoft article is not terribly helpful (plus, it is hemmed in with “don’t do this” warnings).
If all else fails, my fall back position is to use the FS as a backup–as you suggested. But I thought I’d try asking users first. Maybe someone has an idea.
Thanks again.
As Spiney points out, databases in general are disk-intensive - lots of reads and writes, and any latency introduced slows the entire database. Not to mention the potential for database corruption in the event of a lost or faulty network connection is EXTREMELY high…
Even in a transactional database, in a file-sharing scenario, the file-sharing protocol becomes the weak link. It’s the main difference between a SAN solution and a NAS.
In the SAN scenario, the SAN link provides reliability link a “traditional” wired scenario.
In the NAS scenario, you’re relying on the file-sharing protocol’s fault-tolerance, and usually there isn’t enough.
Essentially those are the “I/O guarantees” that Microsoft describes in its NAS requirements for SQL Server.
Drobo FS, as a home/SOHO-oriented solution, likely doesn’t have the required level of “guarantee” there.
Difficult to say… If you have a reasonable stress test scenario you can run, then you could give it a try, but Spiney’s right a direct-attached Drobo would be a much better solution.
I would agree with the Microsoft article and say that this is not recommended. If this database is for any type of business application and has an SLA, then you are asking for trouble going this route.
With that said, SQL Server typically runs as a Network Service which is a non privileged account. If you are trying to use remote storage on the Drobo FS, then you should try using a wide open share such as the default Public share. If you are trying to use protected shares on the Drobo FS, then the issue becomes that both the SQL Server service and the Drobo FS need to be using the same authentication account which you can’t do since since your Drobo FS and SQL Server are probably not using a domain controller. The best you can do in this case is to create the same local user account on both the SQL Server host and on the Drobo FS and make sure they both have the exact same user name and password (including the same exact case). Make sure that you have the same workgroup for both as well.
If you are using domain authentication for your server, then you could also try to reconfigure Samba on the Drobo FS to work with your Domain users. I don’t know if there is any reason why Samba on the FS couldn’t do this, but I’m sure that DRI will tell you that this is not supported as you are changing the system configuration files and bypassing the FS user/group security for it.
Also, make sure you are using UNC path notation when you configure the paths, you cannot use mapping drive letters as your service process has no way to access a mapped drive letter as it won’t exist when the service runs.
Doesn’t the Microsoft article give you the answer? Set trace flag 1807 on your SQL Server instance. Check the help for how to do that.
I haven’t tried it, and wouldn’t recommend it, but I may give it a go just to see what happens.
Also, as with any service accessing network resources, the account under which the service runs must be granted access to the network resource. In this case, that means creating a user account on the Drobo FS with the same name and password as the SQL Server service account.