Log Shipping is one of the methods for creating a Standby server, by god forbidden if something happens to our production server we need a standby server and Microsoft has come up with this idea and introduced Log Shipping in SQL 2000 itself. But in SQL 2005 it has been enhanced further by making it more user friendly. It is mainly used in OLTP environment (Online Transaction Processing). It is used as a High Availability Solution and also in Disaster recovery situations.
Over View of Log Shipping
Lets discuss the overview of log shipping. The basic idea is nothing but backup and restore of the database and transaction logs sequentially from the primary server to secondary or standby server. We might be having some extremely critical databases in our production environment and need them to be online 24*7.If the production server is down due to some natural disasters or in some cases we need to reboot the server after applying some service packs or in case of some hardware upgradation i.e adding extra hard disks, all those require some downtime of the production server in that case we can make use of log shipping.
The below 5 steps are just basic overview and we would discuss each of those in detail.
Step 1 : Take a Full Backup of the DB to be log shipped in Primary server and restore it in Secondary server with Standby option.
Step 2 : Backup the T-log in Primary server and restore it sequentially in Secondary server with Standby option.
Step 3 : If the Primary server is about to go down incase if we need to reboot the server or HDD upgrade then Failover occurs, try to take a backup of transaction log and restore it in Secondary server with Recovery option so that DB is brought online. Else proceed to Step 2.
Step 4 : Change the roles i.e the current secondary becomes the primary and redirect all the applications to the new primary server.
Step 5 : Ensure that Syslogins and Sysusers table in primary and secondary servers are sync alse the users will not be able to access the SQL Server or the DBs.
Leave a Reply