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

System databases on an iSCSI LUN Expand / Collapse
Author
Message
Posted Friday, January 16, 2009 5:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 6, 2014 10:42 AM
Points: 35, Visits: 93
Hi All,

I am preparing to build a new SQL Server 2008 Enterprise server. The machine is very well equipped (Windows 2008 Enterprise, 64GB RAM, 16-core processors, etc). The system volume is a local hard drive, but the data volume is a 300GB iSCSI LUN. This is all for a set of single-purpose databases, for a LOB web app that has been performing poorly.

So here is my question: What is a best practice for locating your system databases on iSCSI LUNs?

---

I am somewhat suspicious of iSCSI LUNs. In the past I have seen that if you put databases on the LUN and then lose connectivity with an LUN, even for a few seconds, it can cause the SQL service to crash and not recover.

In my case, it was all the databases, even the system databases, sitting out on the LUNs. I had used the NetApp SnapManager for SQL and it had moved everything out to the SAN. So when someone accidentally unplugged the switch, SQL tried to restart the service in three quick tries, then gave up. When the LUN reconnected, SQL Server was already halted. When I manually started the service, everything came up just fine, but it gave me heartburn. This is hardly a high-availability solution.

---

Today I am looking at a server in an enterprise. We have redundant, round-robin iSCSI paths, multiple NICs, and backup power on switches and the NetApp. The server is about ready for me to install SQL Server. But looking at the logs, I see that a couple of Windows updates installed on the 15th at 3AM... and there dozens of iSCSIPrt errors to accompany it. Apparently whatever update was being applied caused the connections on each NIC to go down for a second or two.

I am worried that this could happen again and, even with all the redundancy we planned, it could still kill my SQL Server service.

This is a government website and they are serious about HA. So what do I do here? I have several ideas:

1. If I locate the system databases on the local C: drive, will the SQL Service will recover if the LUN is disconnected and reconnected?

2. Let's assume #1 is true, and the SQL Service will keep running. If the LUN containing the databases disappears, can this result in data loss, corruption, torn pages, etc?

I am thinking that the likelihood of a complete loss of connectivity is now very unlikely, since we have physical redundancy all the way to the SAN. But the errors from the 15th point out that a simple Windows Update could potentially interrupt a LUN connection and cause problems for me.

What do you think I should do?
Post #638606
Posted Sunday, February 28, 2010 5:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:41 AM
Points: 53, Visits: 118
Hi there. We experienced a very similar problem you did. Our primary SQL server was a physcial box using multiple LUNs for the database storage (1 LUN for the system dbs, 2 LUNs for Application DBs, and a 4th for the SnapInfo LUN. We were getting sporadic disconnects across multiple servers connecting to the SAN over iSCSI. This brought the SQL Service down when the LUN holding the system dbs got disconnected. I ended up reverting the system db's back to local disk which the physical box. As stop gap i write out a daily SQL backup of the system dbs to a share that is replicated to our DR site. The problem ended up being a bug in the OS for the NetApp. When we upgraded to the newer OS the problem disappeared. I am going to give it a few more months to show any problems before I put the system dbs back on LUNs.

To answer your question. If you have moved the system db's back to locally attached storage on the physical box then the lun's becoming disconnected will have no affect on the SQL Service. You applications databases may get throw suspect. If you using a single LUN for all your application databases and you have a significant number as we do I would recommend just cycling the SQL service once you have confirmed the LUN is available again. This will let the engine bring the dbs back online and verify they are ok. A simple refresh of MGMT Studio will tell you quickly if any of the dbs got damaged which can also be confirmed by the logs.
Post #874268
Posted Monday, March 1, 2010 11:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 4,308, Visits: 6,093
1) I advise my clients to avoid NetApp stuff like the plague when dealing with SQL Server servers. Way too many problems historically

2) I have several clients very heavily invested in iSCSI (EqualLogic primarily) with great success. One got up to over 30TB of SQL Server data before their bad design, underprovisioning and hard-hitting caused them to move to a (MUCH more expensive) Fibre Channel SAN.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #874615
Posted Wednesday, April 21, 2010 12:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 AM
Points: 4,432, Visits: 4,165
SQLGuru,

do you have some links or other information about NetApp Iscsi problems. I'm i the unfortunate situation that we use a NetApp and yes we have problems with it. But to convince management that the problem is not on the SQL Server side I need some more arguments.

Thanks in advance


Markus Bohse
Post #908004
Posted Wednesday, April 21, 2010 12:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 4,308, Visits: 6,093
MarkusB (4/21/2010)
SQLGuru,

do you have some links or other information about NetApp Iscsi problems. I'm i the unfortunate situation that we use a NetApp and yes we have problems with it. But to convince management that the problem is not on the SQL Server side I need some more arguments.

Thanks in advance


I do not have any links for you - sorry. As a consultant I have come across multiple clients in the past with issues on their storage systems. I do acknowledge that I have not seen many clients using them for SQL storage in the past few years so it is quite reasonable to believe that any issues they may have had could be rectified in later versions of their hardware/firmware. I also note that EVERY SINGLE CLIENT I have EVER had with ANY flavor of SAN has done a VERY poor job of implementing/operating said storage system. I have had more than one meeting with CIOs to tell them their seven figure investment was CAUSING performance issues as opposed to helping to ALEVIATE them. There are many many ways you can go wrong when provisioning a SAN for SQL workloads. I highly recommend getting some professional help before putting new storage online - it is soooooOOOOO much easier to do things right from the get-go than to fix problems after rollout!


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #908010
Posted Thursday, April 22, 2010 1:24 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:55 AM
Points: 4,432, Visits: 4,165
SQLGuru,
thanks for your reply and I totally agree with you that a lot of SAN implementations are not very well done.
Unfortunately I joined my current company just after they had implemented the NetApp storage. And can you believe it, they never did any performance test before moving all the data to the SAN.
Now we have endless discussions about what is acceptable performance vs. the "other advantages" of having data on the SAN.
But while performance is not great, at the moment I have bigger worries with Iscsi errors while doing backups to a share on the SAN.
Lots of EventID 20 "Connection to the target was lost. The initiator will attempt to retry the connection." messages.
But looking at the bright side of things, these might finally convince management, that they made the wrong choice.


Markus Bohse
Post #908360
Posted Thursday, April 22, 2010 8:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:00 PM
Points: 4,308, Visits: 6,093
MarkusB (4/22/2010)
SQLGuru,
thanks for your reply and I totally agree with you that a lot of SAN implementations are not very well done.
Unfortunately I joined my current company just after they had implemented the NetApp storage. And can you believe it, they never did any performance test before moving all the data to the SAN.
Now we have endless discussions about what is acceptable performance vs. the "other advantages" of having data on the SAN.
But while performance is not great, at the moment I have bigger worries with Iscsi errors while doing backups to a share on the SAN.
Lots of EventID 20 "Connection to the target was lost. The initiator will attempt to retry the connection." messages.
But looking at the bright side of things, these might finally convince management, that they made the wrong choice.


I would actually have been surprised if the company had done ANY perf testing prior to implementation!!

Have you ensured that every piece of hardware in the system has the latests certified drivers/firmware? It is AMAZING how many problems go away when you get those things addressed. Also review the network utilization metrics - some NICs/switches don't do very well if you start to saturate them. NIC teaming can be problematic if you don't dot the i's and cross the t's correctly. Oh, and have you checked for TCP/IP Chimney being enabled - BAD stuff there

I very rarely make this explicit suggestion (as opposed to a generic "hire a professional" recommendation) but please drop me a line if you feel as I do that a hired-gun would be helpful to give your environment and systems a performance review. I do this for a living and I doubt you have anything going on there that I haven't seen time and time again.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #908665
Posted Thursday, August 1, 2013 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:40 PM
Points: 1, Visits: 2
Are you available for a brief discussion on Failover Clustering and Availability Groups? This is a very gray area for me.

M
robiski@hotmail.com
Post #1480129
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse