Index maintenance kill my Mirroring configuration RRS feed

  • Question

  • Hi there,

    I have 2 issues with mirroring, The first problem is the Timeout (My Timeout is 60 seconds) and second one is the speed rate of the mirroring. I configured mirroring through a VPN to test the speed of the comunication because we are thinking to use it for DRP(Costa Rica to Panama). My big concern is the speed how the SQL sends the packages to the destination. For Example. My VPN can reach 9Megabits/sec of speed with a copy of files, but with the mirroring it can not take more than 2 mbps . How can i improve this rate?.  also, Worst thing is we realized after we run the Index Maintenance  the Log grows at leas an extra 7GB and it hits the mirroring send rate over the VPN greatly (it adds hours). What can i do to handle the index mantenance in a database with mirroring configured?. I CAN NO AVOID TO MAINTENACE THE DATABASE INDEXES

    I appreciate any help or guide that you can give us.

    My Software: Windows Server 2003 R2, SP2,SQL 2005  SP3

    Thursday, April 2, 2009 5:52 PM

All replies

  • Remember that indexing is a logged operation, so if you've got large DBs/massive tables, it's going to generate a lot of log info that now needs to be mirrored. That subsequently will affect disk I/O as well as your network. Unless you can break up your index maintenance into smaller chunks (maybe over a few days), increase your bandwidth or disk I/O, the only thing I can think of doesn't apply to you: SQL Server 2008's log stream compression. That would buy you something, but since you're on SQL 2K5, you're going to have to find a way to manage this.

    Allan Hirt - http://www.sqlha.com
    Thursday, April 2, 2009 9:04 PM