jwcolby
jwcolby at colbyconsulting.com
Wed Oct 6 12:50:30 CDT 2010
I am trying to use commercial solid state disks http://www.newegg.com/Product/Product.aspx?Item=N82E16820227551 in Raid 0 to hold my database from hell as well as another database that contains related information. My theory is that under normal use these two databases are read only. As such I can do reads from them all day without worrying about hot spot wear on the SSDs. In theory placing these databases on SSD should provide an enormous performance boost vs rotating media since SSD streaming read values are much higher and the IOPS is enormously higher. My problem is that I know of no way to discover what the write pattern looks like on the disk as SQL Server does (for example) an index build on adjacent fields (let's say 6 adjacent fields) across 50 million records on a table with an existing Clustered Index. Given that MLC SSDs can only handle 10K writes before potentially wearing out the cells, it seems risky to try and do this kind of operations in place on the SSDs. The problem is that I do need to maintain and build new indexes on these databases. I also need to keep a modern backup so that if one of the SSDs die (killing the entire Raid 0 array) I can easily get them back from backup. I am able to do backups easily, however I am struggling with how to easily do maintenance work on the nearline copy of the database and then get that copied to the online location. Basically ATM I have two copies of the database in SQL Server - _DataHSID and _DataNearLine. "NearLine" is just part of the name to remind me that I need to do things like updates and index manipulations in that copy. _DataHSID is physically located on drive H: (the SSD array) whereas its log file is in F:\SQLServerLogFiles (rotating media) which is the default location for SQL Server to build log files. _DataHSIDNearLine is physically located on rotating media J:\ and its log file resides there too. So what I am trying to figure out is the easiest way to copy the NearLine version to the OnLine location. Since both are attached, it seems that I have to detach OnLine, maybe copy (or detach and move) NearLine to OnLine and reattach OnLine. I can use SMO to detach / attach objects programmatically, and C# can do the copy for me, so I can do all this using C# / .Net if that makes sense, and it appears to make sense (to me). I figure I need to 1) Detach both of the databases. 2) Delete both of the log files, OnLine and nearLine 3) Copy the NearLine database file to the SSD 4) Attach both OnLine and nearLine. BTW, HSID is currently about 50.5 million records, 560 fields, has a clustered index and a handful of multi-field non-clustered indexes on it. The file size for _DataHSID is about 47 gigs. It is set to grow by 500 megs at a shot, and has a mere 125 meg available free space after all of the operations done on it recently. It took 6 minutes and 30 seconds to manually copy from rotating to SSD. Another possibility would be to do a backup / restore directly to the SSD. The backup compresses down to about 7.7 gigs, and a backup / restore would no doubt be faster. I just thought I'd check with others out here to see if anyone is doing this kind of thing and has solved the problem. TIA, -- John W. Colby www.ColbyConsulting.com