Using SQL Server 2008.
I have used SQL log shipping for years. Works great.
Working with a new standby server (recipient of log shipping), I am seeing very poor log restore performance, and wonder: Of the MDF and LDF, is one hit particularly harder than the other during log restore?
Background: With dedicated hardware, I have no problem with this area. (To be fair, I have always been unhappy with the ldf size on the target... but what are ya going to do with a db in restore state?)
I am now experimenting with a cloud server and block storage. So far.... I am rather underwhelmed with the IOPS of block storage. The log restore operation is really slow (our main db server puts out a log file every 4 mins, and it takes 2+ mins to restore each one on the target!).
So I am debating whether:
- get a faster block storage device (mucho $$) --- But this only makes $$ sense for either the LDF or MDF... If I have to do both, it is just too expensive (ergo my question)
- figure out another solve for SQL Server in the cloud
- Forget the cloud for SQL Server, 'cause the iops just aren't there...
Further digging around uncovers: https://serverfault.com/questions/261631/temporary-io-boost-for-an-mssql-server
Which indicates that the hot IO is going to be to the LDF... and that would be the spot to optimize... (though "restore log" is not explicitly the agenda of this thread).
Are we agreed that boosing the IOPS of the LDF disk should boost RESTORE LOG performance?