October 8, 2014 at 3:20 am
Hi,
Currently I have 2 SQL 2008 Standard Edition servers with one DB that is configured to do log shipping between them. These 2 servers are is same network so transfering files is quite fast.
Main DB size is 1.4 Tb, and log is backed up every 10 mins. During most part of the day each log size is 80 Mb in average.
During night, every day there is a maintenance plan for checking indexes and/or reorganizing/rebuilding and updating statistics.
In a period of 4 hours when maintenance is progress, log files grow to huge sizes, up to 16 Gb every 10 mins when log file is backed up in average.
Restoring log files on seconday Server is done every 2 hours during all day. By this point, everything is working fine (as I mention these 2 servers are on same network and physical location).
Now, Same Log shipping configuration I want to apply to 2 servers located on remote locations. One in Asia and Other in the US.
Huge Log shipping file sizes make imposible to have then synchonized, as transfering files takes for ever.
Does anybody have any suggestion to have these 2 servers synchronized?
Thank you
October 8, 2014 at 3:28 am
What, exactly, does your index maintenance job do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 3:32 am
There is a maintenance plan for reorganizing/rebuilding and updating statistics
October 8, 2014 at 3:45 am
You're using the built in maintenance plan?
What tasks in what order?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 6:43 am
alejandro-jasso (10/8/2014)
Hi,Currently I have 2 SQL 2008 Standard Edition servers with one DB that is configured to do log shipping between them. These 2 servers are is same network so transfering files is quite fast.
Main DB size is 1.4 Tb, and log is backed up every 10 mins. During most part of the day each log size is 80 Mb in average.
During night, every day there is a maintenance plan for checking indexes and/or reorganizing/rebuilding and updating statistics.
In a period of 4 hours when maintenance is progress, log files grow to huge sizes, up to 16 Gb every 10 mins when log file is backed up in average.
Restoring log files on seconday Server is done every 2 hours during all day. By this point, everything is working fine (as I mention these 2 servers are on same network and physical location).
Now, Same Log shipping configuration I want to apply to 2 servers located on remote locations. One in Asia and Other in the US.
Huge Log shipping file sizes make imposible to have then synchonized, as transfering files takes for ever.
Does anybody have any suggestion to have these 2 servers synchronized?
Thank you
You could investigate the possibility of stoppping the LS backup job between certain times.
Let the maint run then take a differential backup at primary.
Transfer to secondary and restore.
Then resume the LS plan
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 11, 2014 at 11:59 pm
Hi,
Maintenance is doing tasks in following order:
Reorganize index, for some tables.
Rebuild indexes for tables with highest values of fragmentation
Update statistics
October 12, 2014 at 12:14 am
Hi Perry,
I tried doing your suggestions.
I allowed maintenance plan to finish, I stopped LS during this time. Once Maintenance was finished, I took differential Backup but still this backup is 180GB. Transfering this huge file and then restoring it will not allow me to have DB synchonized within reasonable time. Considering that after file transfering, DB restore, LS will take several hours to bring changes on secondary DB.
October 12, 2014 at 9:44 pm
alejandro-jasso (10/8/2014)
Hi,Currently I have 2 SQL 2008 Standard Edition servers with one DB that is configured to do log shipping between them. These 2 servers are is same network so transfering files is quite fast.
Main DB size is 1.4 Tb, and log is backed up every 10 mins. During most part of the day each log size is 80 Mb in average.
During night, every day there is a maintenance plan for checking indexes and/or reorganizing/rebuilding and updating statistics.
In a period of 4 hours when maintenance is progress, log files grow to huge sizes, up to 16 Gb every 10 mins when log file is backed up in average.
Restoring log files on seconday Server is done every 2 hours during all day. By this point, everything is working fine (as I mention these 2 servers are on same network and physical location).
Now, Same Log shipping configuration I want to apply to 2 servers located on remote locations. One in Asia and Other in the US.
Huge Log shipping file sizes make imposible to have then synchonized, as transfering files takes for ever.
Does anybody have any suggestion to have these 2 servers synchronized?
Thank you
1. What are the largest tables in your main DB? By any chance, are they "audit", "log", or other types of "worm" tables where rows are written but never modified?
2. Also, you have the Standard Edition, which also means that any REBUILDs you do are done in an OFFLINE fashion. Is your system actually able to withstand having indexes offline or does that cause extreme pain for night-time customers/usage?
3. What about Clustered Indexes? Are you able to withstand a table being OFFLINE during your rebuilds of clustered indexes?
4. How often do your Clustered Indexes need to be rebuilt/reorganized? Or, are they "ever increasing" and almost never need rebuilding?
5. During the index maintenance period, is "Point-In-Time" recovery to the minute critical or can you afford to miss an unpredictable 0 to 10 minutes of data during a restore, as indicated by your log file frequency?
I'm not actually trying to annoy you with a bunch of seemingly unrelated questions. I have a couple of ideas that might help when used alone or together but need to know the answers to the questions above to be able to formulate an answer that will fit your needs and the condition of your main DB.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2014 at 12:36 am
alejandro-jasso (10/12/2014)
Hi Perry,I tried doing your suggestions.
I allowed maintenance plan to finish, I stopped LS during this time. Once Maintenance was finished, I took differential Backup but still this backup is 180GB. Transfering this huge file and then restoring it will not allow me to have DB synchonized within reasonable time. Considering that after file transfering, DB restore, LS will take several hours to bring changes on secondary DB.
I'm guessing you have a maintenance plan around the sametime that also has a full backup task in there somewhere?
If this is the case then the diff option wont work as it will be based on the new full in the maintenance plan and not the secondary database on the secondary instance.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply