Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The company that I am working for is looking at doing a data upgrade on a database to go from one version of an application to the next (Axapta 3 to Ax 2009). This process is very IO intensive and with our current setup will take a few days. We currently have a HP Lefthand SAN with 24 450GB 15k disks. We are running the SAN over iSCSI (2x 1gb nics) which limits us to a max through put of about 100MB/s.

We are currently looking for ways to increase the IO performance for the duration of the upgrade. The server that will be running the upgrade will be a HP DL360 G7, which has 8 SAS ports and a HP p410i RAID controller.

We only need about 400GB space for the data and the log-files.

We we thinking about maybe getting 8 SSD's add putting them in the server and setting up a RAID 10 array just for the weekend. I have read that the performance of consumer SSD's degrade when doing lots of write operations. As we only need this solution to work for just one weekend, should I be worried about this if we purchase consumer grade SSD's? Would the Intel X25-M series disks be okay or should we go for the X25-E.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

With intensive data changes, the bottleneck is usually the log file for the database. You should also anticipate log file growth if you are running in FULL recovery mode, and automatic log file (and data file) growth is a performance killer. Consider switching to SIMPLE recovery for the duration of the migration, doing so will minimize log space requirements. It will also keep you from having to run frequent transaction log backups, which need to read the log file, moving the disk heads and will slow stuff down. If you have a multistep migration process ("Run this program first, then run this other program, etc. etc."), you can just take DIFFERENTIAL backups (or even FULL backups) between steps.

I suggest that you isolate the log file from the data files by moving the log file off of the SAN and putting it on a mirrored pair of SAS drives. The reason for this is that I/O to the log file is synchronous, while reads from the data file(s) are often cached and writes are, for the most part, asynchronous. This means that the I/O to the log file is more time-critical than the I/O to the data files. Moving the log file will move some of the I/O bandwidth requirement off of the Lefthand, but more importantly it will allow log writes to occur serially, with little or movement of the drive heads. With respect to log writes, moving the drive heads is a performance-killer.

Plus, the latency on a pair of DAS drives will probably be better than going through a SAN switch. This is also a win for those synchronous log writes. The more gadgets (switches, etc) your data has to pass through between the RAM in the server and the drives, the more latency you will get. After all, the 15K RPM drives in the SAN aren't faster than the 15K RPM drives that you would stick in your DL360.

I know that SAN vendors will tell you that the SAN has so much write cache in it that it doesn't matter and that all writes to the log will be cached and de-staged later and the redundancy calculations (for RAID5 and similar RAID types) are lightening-quick, but the SANs never seem to perform quite as well as you are led to hope. I've been through this sort of thing a dozen times and the actual performance has always been disappointing. Hope for the best, but plan for the worst.

I say use a mirrored pair of SAS drives for the log. If you are OK with having to restore and re-run your migration in the (unlikely) case of a drive failure, you could go RAID 0 or with only 1 drive if you are desperate. (If you have a Lefthand, you are probably not desperate and have a few 72 GB or 143 GB SAS drives hanging around.) I would only run this way temporarily, for the duration of the migration. Usually, the data rate to the log drives is low, maybe 10 MB/s or less, but the latency on the many small writes holds everything up. You would definitely prefer 15K RPM drives for this task over drives with lower spin rates but perhaps higher average throughput numbers.

Also, you will want to grow that log file out manually, ahead of time, or monitor the freespace in the log file very closely. If the log file starts to get near full, manually grow the log file out. If you do it manually, the migration process will continue on. If you rely on auto-grow, the process will be held up for the duration of the growth, and it always seems that auto-growing a file by 1 GB takes longer than growing it 1 GB by hand. After you are done with the process, you can shrink the log file back down to whatever it was before the migration and move it back to the Lefthand.

Depending on how your vendor wrote their migration code, you may also want a lot out of tempdb. It's hard to know without trying it once, on a test server. If so, you may also get improvements by moving the tempdb data files onto their own set of local drives. For that, I'd definitely want RAID10 (not RAID5). You can go with RAID0 if you want to risk it for a weekend, again with the caveat of having to restore your databases and re-run your migration from scratch if something goes "bang". Don't rely on RAID0 after the migration.

Also, make sure that you align your partitions when you create them on the empty drives. It's a cheap performance improvement.

Whatever you do, don't forget that copying all of the data off of the Lefthand and putting it back will take a significant amount of time. I often have to push 1 TB or so between servers and through 1 Gb NICS. It will take hours. Do a test with maybe one or two 10 GB files, determine the expected data rate and do the math on how long it will take to move your large files around. Robocopy works very well, I've been using it for at least 16 years.

Another $0.02 (if you have read this far): I don't know much about Lefthands. If those NICs were load-balancing properly, I would expect something well over 175 MB/s. I can hit 115 MB/s or so on TCP ethernet on 1 Gb/s NICs without any kind of special magic. I expect that iSCSI would be slightly faster. If you can only feed the Lefthand at 100 MB/s or less and these SQL Server files are the only thing on it, the Lefthand unit seems like $overkill$. You could probably get that data rate out of 2 or 4 consumer-level 7200 RPM SATA disks. The DL360 should easily be able to best that data rate with a set of directly-attached 10K SAS drives.

share|improve this answer
1  
Rather than change to the Simple recovery model, isn't this just the type of situation that the Bulk Logged recovery model was made for? –  hmallett Apr 20 '11 at 15:03
    
I aggree that we should be getting more than 100MB/s over 2 NIC's. When it was sold to us we were told that we would get the usage of 2Gb/s, but its not what we are seeing. Another question for another day. Yes you could hit 100MB/s as sec on 2-4 consumer-level drives for sequential reads with a large block size, but i don't think you will get anywhere near that then doing random writes in 8k blocks. –  holz Apr 20 '11 at 23:11

You're running your MSSQL box over a (pointlessly) teamed iSCSI link? no wonder you're looking for extra performance - I genuinely don't understand why you'd cripple yourself doing that. Anyway, yes moving to DAS will massively increase your overall performance but going to non-HP SSDs may very well not work at all, their cards are famous for only working with HP-firmware'd drives, I'd consider just using regular 'spinning' disks, perhaps their 146GB 15krpm 2.5 disks, their own SSDs are fearsomely expensive (good though). If you absolutely want to try the non-HP SSDs then I'd suggest you buy a single one first, try it and if it's recognised then buy more, and yes - heavy DB writes will indeed slow down and eventually kill even the best SSDs.

share|improve this answer
    
The performance of the system is great for what we need, with no single point of failure for a fraction of the cost of most other solutions. The issue is not performance for day to day tasks, but the time-frame needed to do a once off data migration, and if a consumer SSD can withstand 2 days of heavy abuse. –  holz Apr 20 '11 at 9:24
    
It will stand it, if it works, I wouldn't want to leave it in place for long though - we tested some first-gen X25-E's two years ago as Oracle log drives, lasted just under a month if I recall - but that's not what you're proposing. If you're serious about this go for a SSD that uses the Sanforce SF2200 chip, it needs less idle time before starting its housekeeping. –  Chopper3 Apr 20 '11 at 9:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.