Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SAN and NAS protocols and how they impact SQL Server Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 15, 2013 10:01 AM
Points: 1, Visits: 10
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
Post #1289205
Posted Tuesday, April 24, 2012 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:32 PM
Points: 7, Visits: 99
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. :)
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.
Post #1289225
Posted Tuesday, April 24, 2012 8:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
Kevin,
thanks so much for the information....

Keep up the good work on that Engine




Greg J


Gregory A Jackson MBA, CSM
Post #1289508
Posted Tuesday, April 24, 2012 8:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
James,
Thanks for reaching out....I really appreciate the info.


GAJ


Gregory A Jackson MBA, CSM
Post #1289510
Posted Tuesday, April 24, 2012 8:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
I found this to definitely be true


GAJ


Gregory A Jackson MBA, CSM
Post #1289511
Posted Tuesday, April 24, 2012 8:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
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
Post #1289513
Posted Wednesday, April 25, 2012 11:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:42 AM
Points: 535, Visits: 750
Thank you for the research and sharing the results. Good info.


Post #1290132
Posted Thursday, April 26, 2012 9:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 8:44 PM
Points: 358, Visits: 901
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.
Post #1290842
Posted Monday, April 30, 2012 6:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #1292886
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse