Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Optimizations and SAN Replication

Author  Topic 

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2015-01-06 : 08:56:37
Hello,

We are in the process of setting up SAN to SAN replication between our data center and a disaster recovery site. We run a maintenance plan to check DB integrity and rebuild indexes. We run this once a week.

During normal SQL operation the amount of data change is fairly low. But when the optimizations run the data change spikes to around 15 GB. This spike causes a bandwidth issue due to all of the other servers we replicate.

Is there any way to reduce the amount of change during this operation? The total size of the database files (not including the log files) is 12 GB.

I know someone is going to say "buy more bandwidth". We will if we have to ... but we actually have 2 SQL servers and the other produces a 60 GB spike when the optimizations run. The extra bandwidth will be costly and that's what we are trying to avoid.

Thoughts?

Thanks,
Terry

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-06 : 09:15:44
What do you mean by "when the optimizations run"?
Go to Top of Page

tbrothers
Yak Posting Veteran

83 Posts

Posted - 2015-01-06 : 09:35:27
I mean when the maintenance plan runs. The plan settings are as follows:

1. Check DB Integrity task
(Include indexes)
2. Rebuild Index task
(Reorganize pages with the default amount of free space)
(Sort results in tempdb)

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-06 : 11:56:07
OK, no surprise then. The rebuild index task will cause all index pages to be touched. (for a CI that means the entire database). So the replicator sees that almost everything has changed (all the sectors involved) and there is your spike. Problem is that the replicator is looking at the physical layer. SQL Server replication looks at the logical layer and keeps the traffic low.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-07 : 01:51:59
is it synchronous replication or asynchronous? The amount of data you're talking about is very low. Are there any other underlying issues - such as disk storage layout? Do you have the data and log files separated onto separate IO channels?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -