SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SAN and NAS protocols and how they impact SQL Server


SAN and NAS protocols and how they impact SQL Server

Author
Message
kfarlee
kfarlee
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 12
While much of this article is accurate, I need to correct a few misconceptions.
I am a PM, working on the storage engine for SQL Server, specifically on access to storage devices. Over the past couple of years, I've been working with the Windows File Server team to see if there was anything we could do about the poor performance of SQL running over SMB.

As it turns out, there was. It seems that SQL had never paid attention to NAS because nobody used it, because the performance sucked. Windows didn't pay attention to workloads like SQL because again, nobody used it, so it wasn't on their radar. We got the File Server team using TPCC as one of their performance benchmarks for the file server, and some interesting things came to light.
The first was a bug which has been in the client side of the protocol stack since Windows 95! It effectively serializes all write traffic. When we started out, SMB was at ~20% of the performance (as measured by TPCC) compared to direct attached storage. By the time we were done, the performance over SMB was at 97% of the performance of direct-attach.

The great thing about this is that it is a client-side fix, which has been released as a Windows QFE to Windows Server 2008 R2, and I believe 2008 as well. Because it's client-side, you get the benefit regardless of what NAS device is on the other end of the wire.

Going beyond that, the File server team has incorporated the RDMA protocol, in which network traffic goes directly from the wire into user memory without being copied to kernel space. This dramatically reduces CPU consumption, which can suck up most of your CPU, even in iSCSI environments. Now, the CPU used by the TCP stack is comparable to Fibre Channel.

There are also a collection of significant enhancements to the protocol, moving the protocol from 1.0 to 2.2.
The Windows architects have published all of these changes, and have been sitting with all of the major NAS vendors, to make sure that they can also take advantage of these improvements.

Bottom line is that just within the past year or so, the landscape has significantly shifted, so that now it is possible to put together a NAS-based configuration which performs on par with a SAN.

Kevin Farlee
SQL Server Storage Engine PM
James Hamoline
James Hamoline
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 108
Gregory,

I work in a mid-sized company and have the luxury/challenge of being both the DBA and Storage Admin. We also use Netapp Storage mostly with FC in one datacenter and iSCSI in another. When you decide to move your databases to iSCSI LUNs here's my favorite way of doing it. You can move the datafiles ONLINE! I've used this method several times to move entire datafiles to new LUNs as an online operation.

1. Create the new LUNs and attach them to the SQL Server using iSCSI or FC
2. Add datafiles to the existing filegroup (create SQL data files on the freshly attached iSCSI or FC storage)
3. Empty the existing data file into the newly attached one. This is an online operation. "DBCC SHRINKFILE(<FileName>, EMPTYFILE)"
4. Voila! The datafiles have now been migrated to iSCSI or FC. Smile
4. Remove the old (now unused) files from the SQL filegroup.

Brad Hoff, @SqlPhilosopher, has created a blog post describing the method in detail: http://www.sqlphilosopher.com/wp/2012/02/moving-a-filegroup-to-a-new-disk-array-with-no-downtime

That will not work for is your transaction log file. For that:
1. CHECKPOINT
2. Take a tlog backup
3. Shrink the transaction log (This will make the copy time for the log file minimal)
4. Run ALTER DATABASE MODIFY FILE to change the location of the ldf file.
5. Take database offline.
6. Move the physical file from old location to new location.
7. Bring database online.
8. Expand the log file back to its original size.
GregoryAJackson
GregoryAJackson
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 506
Kevin,
thanks so much for the information....

Keep up the good work on that Engine

:-)


Greg J

Gregory A Jackson MBA, CSM
GregoryAJackson
GregoryAJackson
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 506
James,
Thanks for reaching out....I really appreciate the info.


GAJ

Gregory A Jackson MBA, CSM
GregoryAJackson
GregoryAJackson
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 506
I found this to definitely be true


GAJ

Gregory A Jackson MBA, CSM
GregoryAJackson
GregoryAJackson
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 506
Hi Santhosh,
sounds like an ugly problem.
Private Message me and I'll see if I can help you out.


GAJ

Gregory A Jackson MBA, CSM
Misha_SQL
Misha_SQL
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 1009
Thank you for the research and sharing the results. Good info.



Scott D. Jacobson
Scott D. Jacobson
Right there with Babe
Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)

Group: General Forum Members
Points: 770 Visits: 1018
I'm really surprised no one mentioned OpenFiler. Some of us don't have the luxury of shelling out hundreds of thousands or millions of dollars for a solution. Especially if that solution is going to be used in a dev/testing environment.

We have a dev set up with SQL Server on bare metal and VMs. Both instances are using iSCSI targets as local disks and the performance over regular 1Gbps Ethernet is great for our purposes.

OpenFiler also offers paid support if you need a little more assurance and feautres (HA, failover) in the enterprise space. Great tool.
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3736 Visits: 1807
kfarlee (4/24/2012)
While much of this article is accurate, I need to correct a few misconceptions.
I am a PM, working on the storage engine for SQL Server, specifically on access to storage devices. Over the past couple of years, I've been working with the Windows File Server team to see if there was anything we could do about the poor performance of SQL running over SMB.

As it turns out, there was. It seems that SQL had never paid attention to NAS because nobody used it, because the performance sucked. Windows didn't pay attention to workloads like SQL because again, nobody used it, so it wasn't on their radar. We got the File Server team using TPCC as one of their performance benchmarks for the file server, and some interesting things came to light.
The first was a bug which has been in the client side of the protocol stack since Windows 95! It effectively serializes all write traffic. When we started out, SMB was at ~20% of the performance (as measured by TPCC) compared to direct attached storage. By the time we were done, the performance over SMB was at 97% of the performance of direct-attach.

The great thing about this is that it is a client-side fix, which has been released as a Windows QFE to Windows Server 2008 R2, and I believe 2008 as well. Because it's client-side, you get the benefit regardless of what NAS device is on the other end of the wire.

Going beyond that, the File server team has incorporated the RDMA protocol, in which network traffic goes directly from the wire into user memory without being copied to kernel space. This dramatically reduces CPU consumption, which can suck up most of your CPU, even in iSCSI environments. Now, the CPU used by the TCP stack is comparable to Fibre Channel.

There are also a collection of significant enhancements to the protocol, moving the protocol from 1.0 to 2.2.
The Windows architects have published all of these changes, and have been sitting with all of the major NAS vendors, to make sure that they can also take advantage of these improvements.

Bottom line is that just within the past year or so, the landscape has significantly shifted, so that now it is possible to put together a NAS-based configuration which performs on par with a SAN.

Kevin Farlee
SQL Server Storage Engine PM


I was about to post this same information after reading this, so I am happy that you chimed in here Kevin. I've been benchmarking SQL Server on SMB recently due to the support for SMB as a shared storage solution for failover clustering in SQL Server 2012 (which = no need for expensive SAN with good HA), and I've been getting excellent results. :-)

Keep up the good work on the NAS support for SQL, it is really redefining the storage market space for databases.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
dmitriy 68472
dmitriy 68472
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 146
Interesting.. we have NetApp FAS2240-4 if I remember its number correct (with SATA disks).

It is connected over Ethernet to VMware hosts.

We tested the performance with iSCSI and it was awful

Then we switched to NFS and it's more or less OK now.

Does it contradict your summary?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search