October 18, 2012 at 10:58 am
Hello, we are considering implementing Transaction Log Shipping and I would like to get input on the possible performance impact we may encounter. Our database is 1.5TB and is extremely busy all day. I would plan on having the Distributor on a separate server. The main server is on a SAN of hundreds of disks and has 260 G of Ram allocated. Any input would be greatly appreciated.
Thank you
October 18, 2012 at 11:28 am
Log shipping or transactional replication?
Log shipping doesn't use a distributor, just the normal log backups that you'll be taking already
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 18, 2012 at 12:42 pm
Gail, thank you for chiming in. You know, I forgot about log shipping and that may be a better option. What we need to do is provide our BI team with a snapshot of our production database on a daily basis but it does not need to be up to date by transaction. I currently do transaction log backups every 10 minutes so that is more than sufficient.
How would that affect our production and BI databases?
Also, can log restores be postponed during the day and only applied during certain hours?
We are currently on SQL Server 2005 SP4.
Thank you for your expertise!
David
October 18, 2012 at 1:04 pm
Perhaps just restore your daily backup every night?
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 18, 2012 at 1:26 pm
That would be the easy solution except for the time it would take to restore. Unfortunately I am not able to test a restore due to hardware constraints at this time, so I have no idea what to expect in terms of restore timings. We are using a 3rd party compression tool, so the 1.5TB database is actually 500+GB compressed. Once I am able, I will be testing this option.
October 18, 2012 at 1:56 pm
If you do go that route, you get 3 things for the price of 1, you get to test your restores and ensure that they really do restore, you get to test restore duration and you get the reporting environment.
Other option, restore full backup and nightly take and restore a diff WITH STANDBY (can't do it with recovery), or similar with transaction log backups. Has to be with standby, otherwise you'd have to restore the full each night.
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 19, 2012 at 2:21 am
oradbguru (10/18/2012)
How would that affect our production and BI databases?
It won't, the log backups will run every 10 minutes as scheduled. This would be no different to the current log backup regime that is currently in force. You will of course have to ensure that once LS is configured that all other Log backup jobs on the server are set to ignore the LS database.
oradbguru (10/18/2012)
Also, can log restores be postponed during the day and only applied during certain hours?
Yes, that is one of the key flexibilities of Log Shipping. The copy and restore jobs can be scheduled to run at certain times, with the copy job of course running before the restore job 😉
oradbguru (10/18/2012)
Thank you for your expertise!David
You're welcome
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2012 at 12:36 pm
Would we be able to add new indexes to the target database once it is in LS mode?
October 19, 2012 at 12:39 pm
Also, if I were to set up database mirroring and snapshot the mirrored database each night, is the snapshot something relatively quick? Also, does mirroring have any impact on performance?
Thank you!
David
October 19, 2012 at 12:46 pm
oradbguru (10/19/2012)
Would we be able to add new indexes to the target database once it is in LS mode?
Edit: misread. No, target is read-only at best.
oradbguru (10/19/2012)
Also, if I were to set up database mirroring and snapshot the mirrored database each night, is the snapshot something relatively quick? Also, does mirroring have any impact on performance?
Usually, yes, but it depends on a few things (creating a snapshot has to run crash recovery, if there's lots of uncommitted transactions or committed but not written to disk, that can take time). Yes, mirroring can impact performance if run synchronously and across an inadequate connection.
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 19, 2012 at 12:52 pm
Thank you Gail. So just to clarify, if I set up LS, I can schedule a time after hours to copy over all the transaction logs and apply them to the secondary database. correct? Once they are applied I can add any indexes if needed and run ETL scripts? I thought the database is read-only.
David
October 19, 2012 at 12:59 pm
oradbguru (10/19/2012)
So just to clarify, if I set up LS, I can schedule a time after hours to copy over all the transaction logs and apply them to the secondary database. correct?
Correctomondo 😉
oradbguru (10/19/2012)
Once they are applied I can add any indexes if needed and run ETL scripts?
On the prmary, yes, not the secondary directly though.
oradbguru (10/19/2012)
I thought the database is read-only.David
Read only\standby or restoring the choice is yours when you implement LS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2012 at 1:02 pm
Gail, I think that is the kicker. From time to time BI will need to add indexes on the secondary database. How would we accomplish that?
David
October 19, 2012 at 1:05 pm
Thought you were asking about adding indexes on the primary.
Transactional replication. Or a complete restore of the full backup nightly. Log shipping, mirroring both leave the DB on the secondary at best read-only, not writeable.
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 19, 2012 at 1:17 pm
oradbguru (10/19/2012)
From time to time BI will need to add indexes on the secondary database. How would we accomplish that?David
What do you use for backups are they native, native compressed, third party?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply