SQL Server Log Shipping

SQL Server Log Shipping

Log shipping? Why are we talking about lumber? Actually, log shipping is a powerful yet simple way to ensure failover capability in the Enterprise edition of SQL Server. Baya Pavliashvili gets you started by discussing the advantages and drawbacks of log shipping, and then shows you how to set up log shipping and how to fail your database over to the standby server.

Log shipping is one of the failover solutions offered by SQL Server 2000. In this context, failover means substituting primary server with a backup (sometimes also referred to as standby) server if the primary hardware becomes unusable. Failover solutions can also be used to provide close to 100% uptime—during primary database or server maintenance and during software upgrades you could use the standby server to continue serving your customers.

Failover solutions can be automatic or manual. With automatic failover, the backup server detects when the primary server is not available and takes over without any intervention from the database administrator. An example of an automatic failover solution is clustering. With manual failover, the database administrator (DBA) has to perform some steps to bring the standby server online. In SQL Server 2000, log shipping is a form of manual failover.

NOTE

Only Enterprise and Developer editions of SQL Server support log shipping.

How It Works

Log shipping implementation is straightforward:

1. Full backup of the database is taken on the primary server and copied to the standby server.
2. Standby server maintains a copy of the database.
3. The database is not operational; it can stay in either read-only mode or no-recovery mode.
4. Transaction log for the "log-shipped" database is backed up on the primary server periodically. Note that only databases that are in FULL recovery mode can be log-shipped.
Transaction log backups are placed on a shared drive; standby server's SQL Server Agent account must have access to this shared drive.

5. Transaction log backups are copied to the standby server.
6. Transaction log backups are applied to the database on the standby server in the order that they were taken on the primary server.
7. Either primary server, standby server, or a separate server can be used to monitor log shipping. If you use a separate server for monitoring, it does NOT have to have Enterprise Edition of SQL Server; any edition (other than MSDE) will do.

If the primary server becomes unavailable due to disk failure or some other reason, DBA can take the following steps to fail the database over to the standby server:

1. Perform one last backup of the transaction log on the primary server (if possible).
2. Copy all transaction log backups to the standby server and apply them in the same order they were taken on the primary server.
The last transaction log backup should be restored by using the WITH RECOVERY clause so that the standby database becomes operational.

3. Transfer any logins that exist on the primary server to the standby server. Only the logins that must have access to the log-shipped database must be transferred.
This step might be further complicated if logins with the same name exist on both servers. In such cases, the DBA needs to ensure that appropriate mappings exist between SQL Server logins and database users on the standby server.

During the initial configuration of log shipping, you can allow the standby database to assume the primary role. That means you can ship transaction logs from the standby server to the primary server after you have failed the primary database over. So if primary server (server A) fails over to standby server (server B), servers can switch roles so that you can fail server B back to server A if needed.


To implement the Log shipping or mirroring right click on any database --> Go to Properties and under Select a page click on Transaction Log Shipping and enable it by clicking on "Enable this as a primary database in a log shipping configuration" check box.




 

• Now Under the Transaction log backup click "Backup Settings"….. a pop-up window appeared "Transaction Log Backup Settings" as Under:
  
  
   
• If we are creating backups on a network share enter the network path or for the local machine we can specify the local folder path and click "OK".
  
   
• When we click on OK button the above window will close, then from the "Database Properties window" click on Add… button to configure the Secondary Server instance and database.
  
  
  
  Note: we can add multiple servers if we want to setup one to many server log-shipping.
   
• A window "Secondary Database settings" will appear now; in this step we can specify how to create the data on the secondary server. There are three options: create a backup and restore it, use an existing backup and restore or do nothing because we have manually restored the database and have put it into the correct state to receive additional backups.
  
  
   
• Click on Connect.
  
  
   
• Enter the Secondary Server Name and its Password to connect to it, so that our secondary database setting shows both the "Secondary Server Instance" and "Secondary Database".
  
  
• In Copy Files tab, specify the path of the Destination Shared Folder where the Log Shipping Copy job will copy the T-Log backup files. This step will create the Copy job on the secondary server.
  
  
   
• In Restore Transaction log tab, specify the database restoring state information and restore schedule. This will create the restore job on the secondary server. Click "ok", and we will see that in our "Database Properties" window a secondary Server has Added:
  
  
  
  
   
• Now we will configure Log Shipping Monitoring which will notify us in case of any failure, click on "Settings" a "Log shipping Monitor Settings" window will be opened:
  
  
  
  Note: Log shipping monitoring configuration is optional.
  
  
   
• Click on connect and enter the Username and password of your Primary Server, click "OK".
  
   
• Again Click "OK" on Database Properties Window, then you will get the following success result:
  
  

How to bring a log shipping standby database online?

On the failure of Primary database in a log shipping setup, we need to bring the secondary database online for use, which can  be standby or read-only. To make the standby available, break the log shipping and bring secondary database online WITH RECOVERY.To bring the standby database to the current state of the source database, if possible try to perform a final log backup on the source database, then run the log shipping backup job on the source server and copy the backup file to the log shipping share. If there are files remaining in the log shipping share, then use SQL Backup to restore the logs on the standby database by specifying a wildcard (*.sqb) filename, using the option WITH RECOVERY.

Note: If the source database is damaged, a final log backup may fail. In this case, try appending the NO_TRUNCATE option to the BACKUP LOG command.

If there are no remaining log files to restore, use the following SQL query to bring the database on-line:

"RESTORE DATABASE [PatientInfo] WITH RECOVERY".