SQL Databases on File Shares - It's time to reconsider the scenario.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Blakmk — Tue, 27 Mar 2012 09:57:54 GMT

I would be curious to know if the stale read and lost write conditions associated with cifs have gone away. This would be a compelling reason to avoid this.

Quite like all the new features though

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Allan Hirt — Thu, 20 Oct 2011 19:12:32 GMT

While SMB *may* be a choice, things shift. You may have a security issue. So yes, you take the complexity away from LUN masking/mapping/et al., but cause another potential hurdle. I've tested it, and the SQL service account needs admin rights.

Another thing as it relates to availability. Network reliability is still an issue. Then there's ensuring the SMB share itself is highly available (read: on a clustered file share). Sure, things get easier in Win8 with built-in NIC teaming for fault tolerance, but to outright assume the network won't be a potential availability problem anymore is not true - at least not from what I've seen at customers.

If SMB 2.2 requires Win8 (which I believe it does), it's already a non-starter for the near future.

Then there's network performance. I would be very hesitant to stick my most demanding, mission-critical (and large) DB on a SMB share if my network sucked. So in theory while it's true networking itself is better, how a company implements is a different ballgame. Testing is still an important aspect.

Don't get me wrong. On a well designed infrastructure with dedicated 10 gigabit NICs, SMB could fly. I've tested it with current functionality and it works. But using SMB is far from a slam dunk right now.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Brent Ozar — Thu, 20 Oct 2011 18:42:41 GMT

Kevin - great, can you update your manageability steps above then? In the SMB column, you'll need to add steps to get permissions on the shares for the database. If it's a free-for-all, then on the SAN side, you'll need to remove the steps about LUN mapping.

You can't have it both ways - you can't say that SMB is easy to manage because there's no security, and then add security over on the SAN side. Is that fair?

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Kevin Farlee [MSFT] — Wed, 19 Oct 2011 22:28:15 GMT

Brent,

I assume that someone who is laying out a storage network will not intermix it with the office email network. With Fibrechannel that's easy because you can't, but the same principles should apply.

So I would always recommend a dedicated network to isolate impact in both directions.

As to permissions, I would expect that permissions on the share would be locked down, but that either the servers would all have access to shares which they might have need to mount, or that adding the permissions would not be an onerous task. Once added/configured, my comments about the ease of operation should stand.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

conecrusher — Wed, 19 Oct 2011 03:03:38 GMT

In fact, in some cases, money can solve everything, but the problem is not money, it can only improve performance through technology, thank you for your share.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

K. Brian Kelley — Wed, 19 Oct 2011 02:46:14 GMT

Brent,

I can see where it also has applications for smaller organizations who can afford a more lightweight NAS and want a reasonable HA solution. Not talking SAN. Now if the physical server where SQL is installed on drops, pretty quick to recover if you have another one, especially if you're extracting logins on a regular basis.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Brent Ozar — Wed, 19 Oct 2011 01:00:19 GMT

By the way, just to be clear, as long as it's secured and the traffic isn't fighting for the same network adapters, I love this. Makes perfect sense in virtualization environments. Just wanna be really, really sure we're not using the regular networks in virtualization environments - that's a recipe for performance disaster.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Brent Ozar — Wed, 19 Oct 2011 00:58:32 GMT

The Manageability section seems to imply that servers will just use one network connection for both SMB traffic and regular network traffic. "Mount and go" means there's no permissions - the files are just available to everyone without security issues. Is that really the case you expect in the real world?

Assuming it's not, and that network traffic and security issues will match typical SAN storage, I'm not sure how manageability is easier for SMB. I mean, sure, if you say anyone can access any mdf/ldf, manageability is easier - but then you can do that same thing in a SAN. Just don't zone any storage and you get the same effect.

Of course, there's two reasons we zone storage - performance and security.

re: SQL Databases on File Shares - It's time to reconsider the scenario.

Kevin Farlee [MSFT] — Wed, 19 Oct 2011 00:53:20 GMT

Brian, The File Server team has spent the last 6-9 months meeting with the major NAS vendors, laying out how SMB 2.2 works, and getting commitments from them to implement. So, the protocol-related parts will be available across NAS vendors to a large extent.

SQLChicken, We are running our automated test suites against SMB configurations and direct-attach side-by-side in the lab now. There were a number of talks at the BUILD conference that discussed this work as well. One good example would be: channel9.msdn.com/.../SAC-444T

re: SQL Databases on File Shares - It's time to reconsider the scenario.

SQLChicken — Wed, 19 Oct 2011 00:11:18 GMT

This is all great and good but from what I'm reading so far seems more academic than practical. Can we by chance see hard numbers on these tests and some more details? Operating System version/patch level? SQL Server version and workload tested?