jwcolby
jwcolby at colbyconsulting.com
Tue Jun 2 19:59:26 CDT 2009
I am running a pair of homebuilt servers, Quad core, 8 gigs RAM, Windows 2003 x64, SQL Server 2005 x64. I use an Areca hardware raid controller and have an 8 (500 gig) disk Raid 6 array. I am about to replace those disks with 5 (1 TB) disks Raid 6. Obviously I will lose a bit of speed in the process since I have fewer disks in the array, though I will eventually fill out the array with 3 more disks for a total of 8. Maybe. My question concerns how to physically implement the database. I know very little about the details of file groups, disks for log files and so forth, splitting out indexes etc. The databases are essentially read only under normal use. Because of the size of the databases, anywhere from 50 million records and up, as many as 640 fields in one, but more common 60 to 100 fields, I tend to set up what I know as "cover indexes". I am considering going to a pair of 120 g SSDs to house one specific database that is kind of the center of my data universe. In this database, 50 million records, 640 fields, I will create these indexes to place the PK and anywhere from a few to a dozen or more fields into an index. That index then provides all of the fields needed to supply all of the where clause. My question really is, if I am successful in getting the index to supply all of the data, should the INDEXES go in the SSD and the main table just sit out on the raid array? How do I go about specifying where indexes are physically placed? Next question is related, what about the log files? I could put the log files on a non raid disk connected directly to the motherboard, or on a pair of disks mirrored on the Areca Raid controller (if redundancy is important) or even a pair of disks in Raid 0 if redundancy is not important but speed is. As I said, I am rebuilding this thing and want to take this opportunity to think about how to distribute the parts of the databases, and I know absolutely nothing about this side of things. Any thoughts on this? TIA, -- John W. Colby www.ColbyConsulting.com