Sql-server – Moving large database to another drive, how to measure performance before, and setup some type of replication for fast fail back

performancereplicationsql-server-2008

Sorry if this is kind of more than question at a time, but here goes.

Running SQL Server 2008 on windows server 2008 as well. We have a large database just over 200gb right now (it holds images of documents that are scanned in, the 3rd party app is using an image column. it also has some other data that is accessed, including licensing info for the program)

The data files for it are still on the c drive because someone decided to set it up that way. It's been growing bit by bit and the drive is getting dangerously low on space. I have an iScsi drive I want to move it to before anything bad happens (there are log files on this drive right now, but it has a ton of free space and I don't really have any other options right now), but I need to be sure of a few things first.

  1. I want to find a good way to measure the i/o rates on the files as it currently stands, but even after searching I'm not sure of the best way to do this over the course of a day or two to have a good idea.

  2. I also want to test the throughput of the new drive I plan on using to make sure it is suitable. I was thinking of using SQLIO for this, but haven't looked into it much yet.

  3. Lastly, just in case there is some serious performance issues, I was thinking it might be a good idea to have some kind of replication setup, probably asynchronous to avoid performance issues, so that we can quickly revert back to the original files without having to lose any data or wait for the new file to copy back over again.

Thanks

Best Answer

You can use perfmon to measure your disk performance and contention. I would measure the following counters for the appropriate drives (all under physical disk):

  • Avg. Disk sec/Transfer - Otherwise known as IOPs, this is the total number of actions (Read and Write) that occur on your disk.
  • Avg. Disk sec/Read - This is the average amount of time it takes an action to read from your drive. For DB operations, it's recommended (generally) this be under 20ms.
  • Avg. Disk sec/Write - Same as for sec/Read, but for writes. Again, should be (generally) under 20ms.

You want to monitor these values over a period of time to see how they trend. If you capture them at regular intervals (default is every 15 seconds), put them into Excel and calculate your Min, Max, and Average over a reasonable period of time for your standard work period to establish your current baseline.

Next up, for testing the drive, SQLIO is a good tool. I'd probably combine it with the perfmon counters to measure how the disk performs in comparison to your current setup. This will give you a general idea of how the disk will perform.

For replication, you can replicate databases and individual objects, but not files. So to accomplish your goal, you would need to basically create a full copy of the database to replicate to and handle the different naming/connection strings. That being said, I believe that's WAY overthinking the issue. If you do enough baselining and testing up front, you will know how it will perform before you even make the move.

Related Question