November 12, 2013 at 3:06 am
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.
November 12, 2013 at 7:05 am
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:
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