January 20, 2012 at 9:49 am
Hi,
We are migrating a SQL 2000 server to SQL 2008 R2 and I'd like to confirm the best practices for the machine configuration.
At present our database is maintained at around 30GB but it used 24/7.
Unfortunately our database server currently has a file share which is used moderately by 300 users.
Our current SQL 2000 server has 4 GB of RAM. Is there a typical recommended amount of RAM for SQL 2008? We are moving to a 64bit version of SQL 2008 R2 so I'm assuming that it would appreciate more than 4 GB, is there an ideal?
I'm also interested to know the best RAID configuration for the server.
The server has an OS drive (C), File share (D), Database (E), SQL Logfiles (F).
If we have two array controllers available, how best should these be configured?
Our initial thoughts were for the log files to be RAID 10 and for the data files (and the rest) to be RAID 5.
Does the size of the disks matter in the array or is faster speed the priority? I.e. should we have smaller faster disks, or larger and slower?
I understand that it would be beneficial to split the data files by the number of cores available to SQL.
Should we implement something similar to the Log files?
I've read that TempDB should not be placed on the same location as the Log Files so was planning on placing it on the same location as the Database files.
Sorry for asking so many questions, I have been looking on the web but thought this would be a good place to confirm from a SQL perspective.
Many thanks,
Graham
January 22, 2012 at 2:43 pm
GRussell31 (1/20/2012)
Our current SQL 2000 server has 4 GB of RAM. Is there a typical recommended amount of RAM for SQL 2008? We are moving to a 64bit version of SQL 2008 R2 so I'm assuming that it would appreciate more than 4 GB, is there an ideal?
You can use more than 4GB. Is memory an issue now on SQL2000? If not, you will not probably see memory pressure on the new server with the same amount of memory. Tke into account that newer OSs need more memory than what Windows 2000 did.
I'm also interested to know the best RAID configuration for the server.
The server has an OS drive (C), File share (D), Database (E), SQL Logfiles (F).
If we have two array controllers available, how best should these be configured?
Our initial thoughts were for the log files to be RAID 10 and for the data files (and the rest) to be RAID 5.
Does the size of the disks matter in the array or is faster speed the priority? I.e. should we have smaller faster disks, or larger and slower?
RAID 10 is always the best choice. If you can't go with it, use RAID 5 for data and RAID 1 for log.
I understand that it would be beneficial to split the data files by the number of cores available to SQL.
Should we implement something similar to the Log files?
No, log files are sequential and you don't need more than 1 single log file. It doesn't improve performance at all.
I've read that TempDB should not be placed on the same location as the Log Files so was planning on placing it on the same location as the Database files.
A dedicated disk would be the best solution.
Hope this helps
Gianluca
Many thanks,
Graham
-- Gianluca Sartori
January 23, 2012 at 3:00 am
Hi,
Thank you for your reply.
Gianluca Sartori (1/22/2012)
You can use more than 4GB. Is memory an issue now on SQL2000? If not, you will not probably see memory pressure on the new server with the same amount of memory. Tke into account that newer OSs need more memory than what Windows 2000 did.
Memory is not an issue for our current server, but I was conscious that SQL 2008 (and the OS as you pointed out) well consume more memory.
I guess 8 GB would be the new comparison to the 4GB in our SQL 2000 box? Is there a recommend amount of memory for a 64 bit SQL 2008 server? I suppose that depends on the number of users....
RAID 10 is always the best choice. If you can't go with it, use RAID 5 for data and RAID 1 for log.
Thanks for confirming.
I understand that it would be beneficial to split the data files by the number of cores available to SQL.
Should we implement something similar to the Log files?
No, log files are sequential and you don't need more than 1 single log file. It doesn't improve performance at all.
Ok, upon thinking about it that makes sense, just the data files to split then.
I've read that TempDB should not be placed on the same location as the Log Files so was planning on placing it on the same location as the Database files.
A dedicated disk would be the best solution.
I guess this could be on a logical array partition?
I guess the disks we buy should be as fast as possible, especially for the logs.
Thanks.
January 23, 2012 at 3:22 am
GRussell31 (1/23/2012)
I've read that TempDB should not be placed on the same location as the Log Files so was planning on placing it on the same location as the Database files.
A dedicated disk would be the best solution.
I guess this could be on a logical array partition?
If you use a partition on the same disk, you won't have performance benefits.
-- Gianluca Sartori
January 23, 2012 at 3:25 am
I guess this could be on a logical array partition?
If you use a partition on the same disk, you won't have performance benefits.
With a virtual partition we can split it to a dedicated disk, but the traffic would still be coming through the same raid controller as the other logical partitions for that controller.
Thanks again for your help!
Graham
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply