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 |
|
lkerznowski
Starting Member
22 Posts |
 Posted - 2014-08-15 : 08:46:38
|
| Hey.I have a question regarding the restore process associated with Transaction Log Shipping. More so, about how it actually restores, and the rate that you can* restore them at. At the business I work for, we just started to implement this as a backup measure after a nasty power outage two weeks ago that we finally recovered from (backups we not working before that, and some other logs got corrupted).This is how I have it set up right now:DB1 = Main, live databaseDB2 = Secondary, backup serverDB1 had Transaction Log Shipping enabled- Full Backup was taken- Logs are created every 15 minutes, and stored locally (6:00am to 10:01pm)- Log are shipped over to DB2 every 15 minutes (6:03am to 10:04pm)[I used the extra minute so the process would run at that last time, and the 3 minute offset is to ensure the log finishes]- Restore process on DB2 set for Every 1 Hour from 5:40am to 10:00pm.My question relates to that Restore process. Is how I have it set up right now ACTUALLY working. I have no issues in the actual process, as the restores are occurring on time and everything is set up correctly. More so, is it alright to have the Restore Process set to restore 4 logs (4 created in the last hour)?Another IT person has custom reports that he needs to run on the database that usually take 20~30 minutes. The reports running on the live server caused the database to become slow and lock up for our employees. The was a first main reason we wanted to move with a second server; so that the reports could be run on that and not bog down the main, live server.I set it up so that the Restore has 20 minutes to finish before the end of each hour, and my IT person can run his reports at the beginning of each hour. Is this allowed? Or do the Restore rates have to match the Saving/Shipping processes? I just wanted to make sure that logs are not being skipped as of right now since technically there are 4 in the queue to be Restored at each :40 interval. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-15 : 14:02:11
|
| I don't see why you would run the restores hourly. Just rust them at the same frequency as the backup and copy jobs.But I don't see why log shipping was chosen as the solution. Why not use database mirroring or Availability Groups instead? Or how about Failover Clustering?Yes the IT person can run select queries on the secondary server, provided the correct option is selected. But those queries will get disconnected each time a restore is done. If you have Enterprise Edition, you should definitely look into Availability Groups as you can use a readable secondary, which can be always available to the IT person.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
Transaction Log Shipping - Restore question