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.
| 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"? |
 |
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
|
|
|
Optimizations and SAN Replication