June 26, 2008 at 8:02 am
Hi there,
Our company is going to move our database to SAN.
I am a junior DBA and has no experience on SAN. After I read some articles, I am planning to put Tempdb in raid 10, log files on raid 10 and data files on raid 5.
here is an expert comments:
The Log, TempDB, and Data files all need to be in RAID5 configuration for optimum performance. If you only make the tempdb mirrored, then the performance will be severely hampered when you do complex queries, jobs, or any trace profiling and index optimization, as SQL uses the tempdb aggressively for these things. The logs will also best be handled in RAID5 because as you grow in scale and size, you will need to write transaction logs more frequently and faster, as well as accessibility in the case of rollbacks or any other failure would be quite slow if you were to have the logs in a raid 1 config.
How do you think?
June 26, 2008 at 8:21 am
"The Log, TempDB, and Data files all need to be in RAID5 configuration for optimum performance"
You need to find a real expert.
Here is an approach to confirm if a SAN configuration will meet the requirements:
Obtain the current SQL Server file io statistics by running this SQL:
select *
from ::fn_virtualfilestats(default,default)
Then, after the SAN is configured, be sure to run the SQL IO simulator on the current disk configuration and on the new SAN configuration to approximate the current I/O workload. http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
If the SAN is not dedicated to this single SQL Server, then you should run the SQL IO simulator on each of the servers at the same time to reproduce the expected IO workload.
Compare the thruput results for current vs SAN and if the SAN does not meet the thruput requirements, you need to report these finding to management and request a reconfiguration of the SAN.
SQL = Scarcely Qualifies as a Language
June 26, 2008 at 9:06 am
Carl Federl (6/26/2008)
"The Log, TempDB, and Data files all need to be in RAID5 configuration for optimum performance"You need to find a real expert.
Here is an approach to confirm if a SAN configuration will meet the requirements:
Obtain the current SQL Server file io statistics by running this SQL:
select *
from ::fn_virtualfilestats(default,default)
Then, after the SAN is configured, be sure to run the SQL IO simulator on the current disk configuration and on the new SAN configuration to approximate the current I/O workload. http://support.microsoft.com/default.aspx?scid=kb;en-us;231619
If the SAN is not dedicated to this single SQL Server, then you should run the SQL IO simulator on each of the servers at the same time to reproduce the expected IO workload.
Compare the thruput results for current vs SAN and if the SAN does not meet the thruput requirements, you need to report these finding to management and request a reconfiguration of the SAN.
thanks Carl.
currently, we put all the files on Raid 5, windows, sql server 2000 binary, database data file and log files. I know this terrible.
I don't know how to simulator current disk configuration to test SAN using SQLIO.
And I hope SAN has high performance value than current disk array using. How can I set up my expectation?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply