SQL Server Performance

Disaster Recovery Strategy for VLDB - Best Data Replication Approach?

Discussion in 'SQL Server 2005 General DBA Questions' started by DBADave, May 4, 2009.

  1. DBADave New Member

    We have 19 databases that total 915GB in data file size and 140GB in log file size. We anticipate about 250GB of growth each year. Our disaster recovery process uses Double-Take replication software to replicate data and log files to our DR site, located < 100 miles from our office. We've found that when reindexing runs, Double-Take cannot keep up with the changes and crashes. This necessitates a remirror to the DR site, which is costly in terms of time and system resources. I have the following questions.

    (1) How do you replicate data of similar or larger size to your DR site? Log Shipping, Database Mirroring, SAN Copy, etc...
    (2) How do you handle large amounts of data changes that occur during reindexing operations?

    Here's our configuration.

    SQL Server 2005 EE SP2 - 64bit (Clustered - Active / Passive)
    Gig NIC
    EMC Clarion SAN (low-end SAN - Direct attached storage would have provided faster disk performance)
    RAID 10 for Data and Log files (3 RAID 10 arrays for data files and 2 RAID 10 arrays for log files)
    Default Filegroups with One Data File and One Log File Per Database
    450MBit link to DR site
    DBADave, May 4, 2009
    #2

  2. ndinakar Member

    I have not seen any issues with EMS SAN replication with EMC DMX drives.. these are faster than Clario (and ofcourse more expensive too)
    ndinakar, May 5, 2009
    #3

  3. FrankKalis Moderator

    Can say anything about (1), because that's another team.
    However as for (2): I have 2 import jobs running every minute which are importing ~15,000 rows on every run. Unfortunately they can't be turned off for some business reasons. There is no dedicated maintenance window, so I have to rebuild indices online. If you now are reading very carefully through the BOL explanations at ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/eef0c9d1-790d-46e4-a758-d0bf6742e6ae.htm (How Online Index Operations Work) you'll find that rebuilding indices online uses the same mechanism as for snapshot isolation level. That means that the initial index snapshot in tempdb has to be maintained with every modification during the build phase. I believe that this rather huge amount of modifications caused time-outs and overall poor performance, until I restricted the indices being rebuild to the ones with the most pages and the greatest fragmentation and specified the MAXDOP = 1 hint and - after agreement with the users - all the indices that can be rebuild within a 20 minute timeframe. This usually means that only 2 - 3 indices are rebuild on every run once a day, but so far, nobody complained about time-outs during index rebuild operations.

    FrankKalis, May 6, 2009
    #4

  4. DBADave New Member

    Frank,
    Any idea how your DR replication/mirroring is impacted during large reindexing operations? Just curious if the process/utility you are using can keep up with the large amount of changes made in a small amount of time. In our case Double-Take can't keep up, but we are working with the vendor to see if their product can be tweaked to handle large changes. If it can't, we will try another approach.
    Dave
    DBADave, May 6, 2009
    #5

  5. FrankKalis Moderator

    I'll find out. [:)]
    FrankKalis, May 6, 2009
    #6

  6. satya Moderator

    Dave
    Are you performing REINDEX on all the tables on this database or choosing the tables that requires this REINDEX?
    The sizes and capacity of the system should be ok to handle such a load, but as the third party tool involved it is bit hard to pin point where exactly this is failing.
    (1) & (2) In my case most of the clients demand 24/7 availablity to the application with limited time to maintenance, so what I define to them is to choose the tables that only requires reindex (weekly basis) and overnight REORG in order to handle the logical fragmentation.
    I would like to know how high the transactions are based on thsi database?
    Do you have Log shipping or DB Mirroing or SAN Copy on exisitng enviornment?
    satya, May 7, 2009
    #7

Share This Page