October 20, 2009 at 9:10 am
Hi,
I recently was added to a project on a partner that is migrating their solution to a new hardware infrastructure.
They bought and IBM Bladecenter H, with 4 Blade Server IBM HS22 w/ 2 Quad-Core E5520@2.26GHz/8Mb FSB 1066Mhz, 48GB PC3-10600 CL9 DDR 1333MHz each server and QLogic 4Gb Fiber Channel Expansion Cards on each server also with a IBM 31.4GB SATA2 SSF SSD.
The storage has 7 x 4Gbps FC 450GB@15K EDDM and 5 x 1TB@7.2K SATA EV-DDM.
One blade server will be use exclusively has the database server.
The internal SSD will be for the OS and SQL Server 2008 Enterprise.
The storage will be shared by 3 Blade server that will use virtualization, but all FC will be for the database server.
I was planning the following for the DB storage:
The 7 FC HDD will be grouped by:
• 4 HDD – RAID 1+0 for SQL SERVER data files
• 2 HDD – RAID 1 for SQL SERVER log files
• 1 HDD – spare
I have several questions about this:
Question 1: what to do with tempdb? The application has some problems and sometimes, when processing large amount of data, tempdb can grow up to 10GB .. What’s the best solution, since this is a very write intensive app?
1. Use the RAID 1 for the log files and add another file group and store there the tempdb?
2. Buy 2 more FC 450GB HDD disks to make a RAID 1 for tempdb (price difference for other disks is very low so 450GB is the “standard”)?
3. Buy 4 more FC 450GB HDD disks to make a RAID 1+0 for tempdb?
4. Buy 2 64GB SSD disks to make a RAID 1 or RAID 0?
5. Another solution?!
Question 2: since this application has very “sensitive” data (money transfers, …), I was thinking of creating a new file group to put those tables and have a backup plan that runs in 5 min intervals, or less. Is there a problem to create this file group on the same RAID where the data files are stored (have all data files on the same disk with different file groups) due to disk controller queue? Also, we have a Robot Tape Backup IBM TS3200 FC, but is this “fast” enough for the backups or, since there are 7 x 1TB SATA disks, use one of those to put the backups on and then copy them to the tape, since the backup would be faster?
Question 3: should I let the primary keys and indexes on the same file group as the data files or should I create one for indexes and let the PKs on the data file group? If I do so, since I only have the 2/3 RAIDs on which one should I put it?
Question 4: the main table on the database has, for now, 500.000 records and it’s planed to grow on the following year up to 4.000.000 or more… the table stores data for ads with a status column (active, stopped, paused,..) and a country column. Status has only 6 possible values and country has 265. Since only active ads are displayed on the public web site and one can only search ads on one country at a time what’s the best option? Table partitioning over status and a index over country, table partitioning over the two columns (265 possible values for country seems a lot to partition by) or just use indexes? If I do partition, once again, the file group/raid question… where to store it?
Question 5: the ads table has another table were images are stored. We don’t use FILESTREAM since Microsoft only advises FILESTREAM for 2MB or higher files and our files only have 120KB at the most, so the initial design had them stored in the database… probably will be change in a future plan, but in the near future will stay like this. My question is, since this table is approximately 50% of the database, should we create a file group for this table? The table is, manly used for storage, has many writes but few reads since we have cache servers that store the images.
Thanks in advance,
Pedro
PS: Sorry for the long story...
October 21, 2009 at 1:02 am
Since there's no delete or move of posts to other groups I created this one in SQL Server 2008 - General (http://www.sqlservercentral.com/Forums/Topic806267-391-1.aspx) and would like to close this one.
Thanks and sorry,
Pedro
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply