August 13, 2010 at 8:57 am
I have a strange request. I know that in theory, my idea will work – has anyone tried something similar?
I have a clustered server, with SQL Server 2005 running on it. I have a large iSCSI SAN configuration with a lot of databases. These databases include tempdb’s which are very active.
I want to move the tempdb’s off of the SAN and onto direct attached drives. I have done this with several non-clustered servers with great results. Now, as a proof of concept, because my bosses will not do anything without several iterations of a proof of concept, I want to be able to do the following:
Maintain all databases on the SAN, clustered as normal. Attach a direct connect disk to each side (physical) of the cluster ie: Server1 has one direct attach, and Server2 has another direct attach. Both of these direct attached have identical drive letters.
I now want to put tempdb onto the direct attach.
The Cluster configuration is active/passive. SQL Service is running on Server1 and off on Server2. If the cluster fails over, then SQL starts on Server2. Since tempdb is recreated every time SQL starts, in theory, as long as the drive letters match and the path matches, the tempdb should come up on the direct attached drives.
I understand that there are better ways to do this, but a clustered direct attach is big bucks without a guarantee. Thought/ideas/suggestions?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 13, 2010 at 9:23 am
Can you configure direct attached drives as cluster resources? A clustered SQL Server can only use drives that are configured as cluster resources and are dependencies of the SQL service.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2010 at 9:35 am
Gail, Thanks for the response. unfortunately, the simple answer is no. We would need additional equipment, to the cost of $22000.00 in order to do this per the hardware vendor. That is why I am looking to experiment first as a proof of concept. Again, with everything being identical on both sides, you are saying that tempdb would not come up?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 13, 2010 at 9:49 am
If a drive is not a cluster resource and a dependency of the SQL service, SQL cannot even see that drive. Hence if you do an ALTER DATABASE and set TempDB's location to that drive, when SQL starts it won't be able to create tempDB (because as far as it's concerned, the drive doesn't exist) and it will fail to start up. You'll have a fair amount of fun (not) fixing that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2010 at 10:00 am
Thanks Gail. That is why I called this a theory as well as strange. Just grasping at straws here, unfortunately as usual.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 13, 2010 at 11:57 am
Huge disclaimer: I don't know the support that MS offers for this!!!!
I have done some research recently on this due to a consideration of putting tempdb on SSD's and found a couple of interesting links. I will say that it seems possible based on what I have read but again, is it supported is the question. So, my recommendation, read the articles, see if this is something you want to consider and then call MS and check with them on what that is going to do to any support you might need in the future.
So, the links.
http://nyc-dba.blogspot.com/2008/10/using-local-disks-in-cluster.html
http://support.microsoft.com/kb/555312
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 13, 2010 at 1:22 pm
Dave,
Thanks for the links. It appears that it is doable. Now to play.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
August 13, 2010 at 1:36 pm
Let us know what kind of performance boost as the result of putting TempDB on the SSD's! I read about this awhile back but didn't hear of anyone implementing. I also heard about SAN's with SSD drives in them but cost were too high to be practical.
August 13, 2010 at 1:45 pm
Twinsoft SME (8/13/2010)
Let us know what kind of performance boost as the result of putting TempDB on the SSD's! I read about this awhile back but didn't hear of anyone implementing. I also heard about SAN's with SSD drives in them but cost were too high to be practical.
If we move forward with this I will definitely share some results. I will say though that my guess is that when you remove IO latency you will quickly expose the next performance bottleneck and, whatever that is will be the limiter. So any results will have to be considered with that in mind.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 13, 2010 at 2:41 pm
I will keep all posted if we do anything with this as well. Should be an interesting project.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply