VMware SRM manage with SQL Server high transfer amounts.

  • In our organisation we have a number of SQL Servers running on VMWare vms which utalise VMwares SRM feature to transfer block level changes on the SAN to a remote DR site. This is part of a number of DR solutions, but is our main way to be able to quickly spin up the production servers at our remote data centre in the event of a disaster.

    Logs and Data are configured on two separate disk structures and configured to transfer any block level changes every 10 minutes. The problem we are having is the amount of data being transferred is huge! Every 10 minutes we are seeing around 7 to 16 GB worth of changes on the data drive alone. The database server has two instances running with a number of databases installed, totaling around 500 GB worth of data.

    What I would like to know is, is this normal?! Our databases are not very busy, in that I would not expect them to be having lots of changes happening on them.

    Does anyone have any experience of SRM and how SQL performs with the tool? Will SRM just take any block changes to the data files, or will the addition of an 8KB block to SQL cause a the whole data file to be copied each time?

    I would be interested in any thoughts anyone has.

  • For reference, I had a long play around with a number of different queries, to try and retrieve the amount of writes to the disk for each database. Eventually I adapted a query from the following web page:

    http://www.mssqltips.com/sqlservertip/1416/gather-io-statistics-down-to-the-sql-server-database-file-level/

    USE master

    GO

    -- create table

    IF NOT EXISTS (SELECT *

    FROM sys.objects

    WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]')

    AND type IN (N'U'))

    BEGIN

    CREATE TABLE filestats

    (dbname VARCHAR(128),

    fName VARCHAR(2048),

    timeStart datetime,

    timeEnd datetime,

    timeDiff bigint,

    readsNum1 bigint,

    readsNum2 bigint,

    readsBytes1 bigint,

    readsBytes2 bigint,

    readsIoStall1 bigint,

    readsIoStall2 bigint,

    writesNum1 bigint,

    writesNum2 bigint,

    writesBytes1 bigint,

    writesBytes2 bigint,

    writesIoStall1 bigint,

    writesIoStall2 bigint,

    ioStall1 bigint,

    ioStall2 bigint

    )

    END

    -- clear data

    TRUNCATE TABLE dbo.filestats

    -- insert first segment counters

    INSERT INTO dbo.filestats

    (dbname,

    fName,

    TimeStart,

    readsNum1,

    readsBytes1,

    readsIoStall1,

    writesNum1,

    writesBytes1,

    writesIoStall1,

    IoStall1

    )

    SELECT

    DB_NAME(a.dbid) AS Database_name,

    b.filename,

    GETDATE(),

    numberReads,

    BytesRead,

    IoStallReadMS,

    NumberWrites,

    BytesWritten,

    IoStallWriteMS,

    IoStallMS

    FROM

    fn_virtualfilestats(NULL,NULL) a INNER JOIN

    sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid

    where b.filename not like '%ldf%'

    ORDER BY

    Database_Name

    /*Delay second read */

    WAITFOR DELAY '000:05:00'

    -- add second segment counters

    UPDATE dbo.filestats

    SET

    timeEnd = GETDATE(),

    readsNum2 = a.numberReads,

    readsBytes2 = a.BytesRead,

    readsIoStall2 = a.IoStallReadMS ,

    writesNum2 = a.NumberWrites,

    writesBytes2 = a.BytesWritten,

    writesIoStall2 = a.IoStallWriteMS,

    IoStall2 = a.IoStallMS,

    timeDiff = DATEDIFF(s,timeStart,GETDATE())

    FROM

    fn_virtualfilestats(NULL,NULL) a INNER JOIN

    sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid

    WHERE

    fName= b.filename AND dbname=DB_NAME(a.dbid)

    and b.filename not like '%ldf%'

    -- select data

    SELECT

    dbname,

    fName,

    timeDiff,

    readsNum2 - readsNum1 AS readsNumDiff,

    readsBytes2 - readsBytes1 AS readsBytesDiff,

    readsIoStall2 - readsIOStall1 AS readsIOStallDiff,

    writesNum2 - writesNum1 AS writesNumDiff,

    writesBytes2 - writesBytes1 AS writeBytesDiff

    ,

    writesIoStall2 - writesIOStall1 AS writesIOStallDiff,

    ioStall2 - ioStall1 AS ioStallDiff

    FROM dbo.filestats

    where dbname != 'Tempdb'

    order by writesMbdiff desc

    This allowed me to work out the number of bytes being written to disk by the data files and found that monitoring this over a number of minutes gave me similar stats to what the SRM replication was doing.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply