November 3, 2003 at 7:03 am
Hi!
I need to implement a number of reporting servers with information from the main server. Replication does not work in this case, because the main server is overloaded with replication. Making a separate distributor is not a choice, cause of management issue.
Standard log shipping would be good choice but it kicks of users during log restoring.
(15 minute concurrency is a must)
I am thinking of a custom log shipping, when logs are backed up (as in standard log shipping) on a network share (this is also a standard backup work), but insted of restoring them I would like to read the log backups and apply the transactions the way it works in replication.
How feasible is it to program this? Are there existing solutions for this?
Thanks.
November 6, 2003 at 8:00 am
This was removed by the editor as SPAM
November 6, 2003 at 8:39 pm
Hi there
Shipping - to apply any full/diff or trn backup file will require users being kicked from the instance, so when do you apply the backup to the log shipped db?? i have the same problem!
Replication - is fine, but a lot more complex, wont automatically cater for new tables/alterations-to for publication etc, I dont like it for DR purchases, but for reporting may be ok if you talking a smaller subset of the db, basically more DBA work required.
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
November 6, 2003 at 10:05 pm
Chris is right. You cannot restore with people in the db. You could implement 2 phase commit as well, but that would be a pain and a lot of work.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
November 7, 2003 at 1:27 am
As long as your reporting users don't need up to date information, you don't need to restore the transaction logs every 15 minutes.
We have implemented a solution that backs up the transaction logs every 2 minutes, and copies the backup to the secondary site. The logs then sit on the secondary server, and are all restored during the early hours of the morning.
If we need the reporting database to be more up to date, we can just run the restore part more often.
You will still need to kick the users off while the logs are being restored, but at least they aren't kicked off every 15 minutes.
Note, this is done using our own log shipping code (as we need to zip the backups because of bandwidth restrictions), so I'm not sure whether SQL Server's in built log shipping is flexible enough to provide this sort of schedule.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply