SQL Server Performance Forum – Threads Archive

Building Server from Scratch with DB Restore

Hello, I am replacing my 72GB 15K disks with 140GB 15 disks to increase the space on my server’s hard drive and I am installing a new operating system, Windows Server 2003 advanced. I want to build out the server with SQL best practices and make sure I do it right. My server has 6 internal disks and will have 4x146GB and 2x72GB drives. What is the best way to configure this server for optimal performance and best practices? If I went with my current configuration, here is what I would have: Use the 4x146GB disks as a RAID5 and the 2x72GB as RAID1. Build out a 292GB E: drive for the data files and backups. Build out a 72GB C: drive for the tempDB files, the OS and the paging file. My DB is SIMPLE recovery, so I do not have transaction logs growing. Here is another option that I am thinking of and my statistics needed for analysis: DB size – 17GB currently, may double in size in next year.
Read/Write ratio = 2.7:1 Option#1:
3 separate RAID1 volumes. 146GB E: drive, 146GB F: drive and 72GB C: drive. The C: drive holds the tempDB files, the OS and paging file, The E: drive holds the data files and the F: drive holds the backups. One final very important question: When I build out the new server and restore the DB and tempDB, how do I change the drive and folder locations of those DB’s? When I go into Enterprise Mgr. and click on the path, it says "A database file’s physical location cannot be changed once the database file has been created." The same question goes for the DB file. When I build out my new server and install SQL EE, do I specify the locations of the files and then just do a restore of the backup there? I have had problems in the past trying to restore a DB where the old data files were on a different path? All input, guidance, help is greatly appreciated. Thanks!!

One more question I would like to add instead of making another new post is the following: My Network team is suggesting I upgrade my RAID controller, but they are saying that the one I have now is very good, just not the latest and greatest. Does anyone know how much of a performance boost I may get from upgrading the RAID controller? I currently have an HP Smart Array 5300 controller, but they are recommending the following: HP Smart Array P600 controller SA-P600 (newer and more expensive)
HP Smart Array P600 controller SA-6402

You can take help from these KBAs
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/?id=314546
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm in order to move the user databases and for tempdb you need not worry to move it to the new server, as SQL Server recreates freshly you can define a size on TEMPDB from existing server. In order to restore database to a seperate location as compared to existing location you can use WITH MOVE clause in RESTORE statement, BOL has got an example in this regard. I would suggest to move TEMPDB to a seperate drive, rather than keeping it with OS paging file drive. Edit:
I’ve corrected them now it seems some problem with cut & paste from Google[xx(]
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, All 3 of those articles come back with "page cannot be found". Could you let me know a quick and short context for the WITH MOVE and RESTORE for my documentation? I tried to use these with the BOL documentation to no avail. Does anyone have any thoughts on what RAID controllers are best from HP and if it makes sense to upgrade? Satya, Here is a sample, if you could write the syntax it would be greatly appreciated: Old Database:
location: F:Sample DatabasesMicrosoft SQL ServerMSSQLDataoldDB.mdf
Name: oldProductionDB Restore Location:
location: E

rogram FilesMicrosoft SQL ServerMSSQLData
ewDB.mdf
Name: newProductionDB Thanks.

Satya, Sorry, I didn’t see that you edited the prior post. The third link works, but the first two still go to page cannot be found. Which of the following options is a better configuration for SQL (I only have 6 internal drives for use): Option #1:
146GB RAID1 (SQL Data Files)
146GB RAID1 (TempDB files, backups)
72GB RAID1 (SQL binaries, OS, paging file) Option #2:
292GB RAID10 (SQL Data Files, TempDB, backups)
72GB RAID1 (SQL binaries, OS, paging file) Thanks.

I would go with Option #1, here is the fresh KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;224071 and KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for your information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, just out of curiosity. Why would you prefer #1?
And where are the logs located? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I have my DB’s recovery model set to SIMPLE, so I don’t have big Log files, but that is a good question. Here are the modified options: Option #1a:
146GB RAID1 (SQL Data Files)
146GB RAID1 (TempDB files, backups, SQL Log Files)
72GB RAID1 (SQL binaries, OS, paging file) Option #1b:
146GB RAID1 (SQL Data Files)
146GB RAID1 (TempDB files, backups)
72GB RAID1 (SQL binaries, OS, paging file, SQL Log Files) Option #2a:
292GB RAID10 (SQL Data Files, TempDB, backups, SQL Log Files)
72GB RAID1 (SQL binaries, OS, paging file) Option #2b:
292GB RAID10 (SQL Data Files, TempDB, backups)
72GB RAID1 (SQL binaries, OS, paging file, SQL Log Files) Thanks! Satya, I got the articles now. Thanks.

How much tempdb does your database use? Unless you know that tempdb access is high I would not dedicate 2 drives to it. I would go with 2b. If you database doesn#%92t have a large percentage of writes then you might want to consider RAID5 as well. What can you tell us about the access patterns of your application? Bert

Bert, My Read/Write ratio = 2.7:1 (37% writes) and is currently on a RAID5. I would like to get the performance boost and redundancy boost by converting to a RAID1 or a RAID10. Right now, my TempDB is 3.7GB (my DB is about 18GB). I would not be dedicating a whole drive to TempDB (option #1), that drive would also contain my full DB backups, which I currently keep 3 which equals 60GB extra. The bigger drive would enable me to keep more native full DB backups, so I will use the extra space. Thanks for the help, would anyone else like to add their 2 cents? Thanks.

I just re-read your first post…sorry I missed your ration… you definetely need to go with RAID10 for this large % of writes….. well that also depends on how much data is being written. To be honest with only 6 drives I would consider going with 1 6 drive RAID10. So you tempDB is 3.7GB but is it alwasys being used….used during a certain time of day for reports…etc IF tempDb is not being heavly acccesed then you dont want to waste 2 drives that might be sittign idle for most of the time. Bert

Bert, Thanks for the input. Does anyone else agree with Bert, that I should configure a 6 disk RAID10 with 364GB (146 + 146 + 72) that will contain my DB files, log files, TempDB files, OS files, paging file, the whole shebang, etc? Certainly makes it easier…. Rodney

]]>