June 8, 2009 at 8:00 am
If we move our TempDB to an iSCSI solution we can greatly improve the performance of one of our vendor's apps. We don't have a lot of options on where to move it, and are considering alternatives, but this is seeming like the best plan.
The main shortcoming, is what to do in the circumstance that the iSCSI drives, or more likely, the network between the servers and the iSCSI drives, go down. During my testing it seemed that if this happened for an extended period of time (more than a network hiccup) it takes the SQL Server service down. Then there's no way to move TempDB because the service is down, that I know of.
1) is there a way to move TempDB while the service is down?
2) could I throw up a fake drive in place of the iSCSI one if I had to?
3) any other suggestions?
Thanks,
Rick Todd
June 8, 2009 at 9:28 am
Rick Todd (6/8/2009)
If we move our TempDB to an iSCSI solution we can greatly improve the performance of one of our vendor's apps. We don't have a lot of options on where to move it, and are considering alternatives, but this is seeming like the best plan.The main shortcoming, is what to do in the circumstance that the iSCSI drives, or more likely, the network between the servers and the iSCSI drives, go down. During my testing it seemed that if this happened for an extended period of time (more than a network hiccup) it takes the SQL Server service down. Then there's no way to move TempDB because the service is down, that I know of.
1) is there a way to move TempDB while the service is down?
2) could I throw up a fake drive in place of the iSCSI one if I had to?
3) any other suggestions?
Thanks,
To move TempDB, you need to specify its new location, and stop and restart the services. Since it's re-created everytime services start, your tempdb files will be moved to the new location.
Quoting from BOL:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
June 8, 2009 at 10:27 am
Is that iSCSI drive network attached storage?
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
June 8, 2009 at 10:33 am
Hi,
I think you can move the tempdb while the service is down. You have to change in the registry (not sure where to change) but I did not test it (Test for yourself on a test system). Be cautious it may lead to future problems also because the location of tempdb file is not only stored in registry but also in the master database.
Thanks
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
June 8, 2009 at 12:30 pm
ps (6/8/2009)
Rick Todd (6/8/2009)
If we move our TempDB to an iSCSI solution we can greatly improve the performance of one of our vendor's apps. We don't have a lot of options on where to move it, and are considering alternatives, but this is seeming like the best plan.The main shortcoming, is what to do in the circumstance that the iSCSI drives, or more likely, the network between the servers and the iSCSI drives, go down. During my testing it seemed that if this happened for an extended period of time (more than a network hiccup) it takes the SQL Server service down. Then there's no way to move TempDB because the service is down, that I know of.
1) is there a way to move TempDB while the service is down?
2) could I throw up a fake drive in place of the iSCSI one if I had to?
3) any other suggestions?
Thanks,
To move TempDB, you need to specify its new location, and stop and restart the services. Since it's re-created everytime services start, your tempdb files will be moved to the new location.
Quoting from BOL:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO
Thanks for your reply, but the whole point is that the SQL Server Service will be down...
Rick Todd
June 8, 2009 at 12:32 pm
GilaMonster (6/8/2009)
Is that iSCSI drive network attached storage?
Gila: it's on a crazy redundant Net App filer. I guess technically it is network attached storage, but when I hear network attached storage I usually think of cheap, throw-away storage, and this is definitely not that.
It's most likely that something between the SQL Server and the NetApp device would go wrong, not the device itself.
Rick Todd
June 8, 2009 at 12:55 pm
Just so you know, it is strongly not recommended that SQL run on a NAS. I'll find kb article if you like, or you can search yourself (I'd have to gooogle it, don't have it bookmarked). It's not the cheap part that's the problem, it's what's between SQL and the drives and what exactly they support. There's a reason SANs have redundant pathways.
Does it preserve write order?
If SQL goes down because the network glitches, you'll have to bring SQL up in restricted mode (command line), run the ALTER DATABASE that ps gave, then shut SQL down and bring it up normally.
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
June 8, 2009 at 1:28 pm
I came across this scenario in one of my tests where I tried using a usb drive for the tempdb, and yes it did start sql, and just recreated new tempdb files.
But as Gail mentioned, the proper way would be to start sql in minimal configuration in command prompt, and alter the tempdb.
_____________
Donn Policarpio
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply