Database Logshipping issues & Considerations!

Posted by Deepak Kumar | Posted in SQL DBA, Uncategorized | Posted on 04-06-2010

3

While working on SQL Server logshipping a DBA must consider following points to have log shipping setup running smoothly over a long period of time. Logshipping architect will look like:

image

  • Transaction log Backup and restoration jobs should work constantly (in parallel) and if backup/restoration jobs are not in sync due to network/server or any other issue it will crash logshipping setup and you may need to setup again or apply full backup restore once again. A DBA must add good number of alerts to notify as soon as transaction log backup, file copy or restoration job fails and act immediately to resolve it.
  • Its highly recommended that you should use almost similar set (size/drive name) etc on both servers. if your database log file increases to 100GB on primary server that means your secondary server should have 100GB capacity also. Similarly if you add new log/data files on primary database it should have similar location on secondary server, or you may need to manually restore TL backup on secondary server with move command.
  • Primary database can create huge transaction log backups during alter index/dbreindex tasks that you may find hard to move to secondary server(s); to mitigate the delay and risk you may choose to buy 3rd party products like Red-Gate/ Idera etc with logshipping features.
  • Use a customized script to transfer SQL Server logins to secondary server(s).
  • Logshipping failover is not fully automatic, manual DBA intervention is expected to recover databases during failure.
  • For SQL 2000 logshipping is good, however if you are using SQL2005 or 2008 use database mirroring which is advance to logshipping with hot standby feature of SQL Server.

Comments (3)

Hi! This is my first comment here so I just wanted to give a quick shout out
and tell you I truly enjoy reading through your blog posts.
Can you suggest any other blogs/websites/forums that cover
the same topics? Many thanks!

Hey I know this is off topic but I was wondering if you
knew of any widgets I could add to my blog that automatically tweet my newest twitter
updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

very informative post! thanks for sharing. I have found another helpful post which you can see here: http://www.sqlmvp.org/troubleshooting-log-shipping-issues-in-sql-server/

Write a comment

*

© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com