SQL Server Log Shipping For Large Databases

Scenario:  A couple of days back I was
setting up a DR plan for one of our most mission critical DB servers which was
hosting around 10 databases having size of 10, 23, 5, 7.4, 1.8, 6.2, 7.9, 5.53, 8.7 and
125 GB respectively. I decided to implement Log Shipping as a part of the DR
strategy because the DR server (Secondary Server) was never going to be used
for Reporting purposes, therefore Log Shipping was the best choice. Implementing
Log Shipping for small databases is relatively straightforward as one can easily do it
using the GUI but for large database, the GUI is not a good option as the database
on the destination server will be always out of sync. So a question arises How
do we accomplish this task?
. In this article, I am going to explain you’ll
the detailed steps which we followed to implement the log shipping for our large databases.

For demonstration purposes, let us assume that we have two
instances named INTEL and INTELabc on SQL Server 2008 R2.
We have a database named test on the instance named INTEL which
is 125 GB in size. A log shipping copy of the same database will reside on the instance named INTELabc.

Create a folder named LogShippingLogs on the source
and destination machine on the appropriate drive. In this case, we created the
folder on the F drive of the machine. This drive was dedicated to hold the DB
backups and therefore we decided to use this drive to hold the copy of the
Transactional Logs. Ensure that the account under which SQL Server Agent
services are running on the source and destination machines has full access to
this folder on the source as well as the destination machines.

Connect to the SQL Server Management Studio on the instance
named INTEL. Verify that the Recovery Model of the database named Test has been
kept as FULL because in Simple Recovery Model, the transactional log backup of the
database cannot be performed. In order to check the Recovery Model of the
database, just Right Click on it, Select Properties and then Options as shown
in the screen capture below.

As shown in the above screen capture, the Recovery model of
the database is FULL which satisfies the eligibility criteria for the database
to participate in Log Shipping.

Take the Full Backup of the database named test. This
can either be done by using GUI or by using the T-SQL, I always prefer using
T-SQL rather than the GUI. The T-SQL is as shown below:

BACKUP DATABASE test TO DISK=’ F:\LogShippingLogs\test.bak’

Since the size of the database is huge – around 125 GB it
will take some time for it to be backed up. In this case it took approximately 1
hour 12 minutes for the backup be completed. Once the backup is
completed, RAR the backup file. This is because transferring 125 GB file over the
network to the secondary server will consume a lot of resources. When I RAR the
backup file, the size of the backup file gets reduced to a mere 10 GB which
when transferred over the n/w to the secondary server will not cause huge
consumption of resources. Also, for huge files RAR is much faster
than zip.

Pages: 1 2 3


9 Responses to “SQL Server Log Shipping For Large Databases”

  1. Nice article

  2. Very good article. Can you please elaborate on how to monitor log shipping, especially the way the synchronization works. How can one knows its synchronize/ up to date with the primary server

    Thank you.

  3. hi,

    Its a very nice article. But why while adding secondary server screenshots are not given for ‘Initialize Secondary Database’ and adding secondary server by using ‘Add’ in detail.

  4. In the start of your article you mention that using the GUI is not a good option. Yet throughout the procedure you use the GUI.
    Can you clarify this a bit further, where did you NOT use the GUI?

  5. You might also want to ensure that Backup compression is being used, since the first backup and logs will be shipped across the network

  6. Hi,
    Thanks for providing a comprehensive guide. I would like to mention that in SQL Server 2008 R2 Standard and Enterprise, you can use compressed backups as the native SQL Server backup option, thus eliminating the extra RAR step. Log Shipping under these versions can also utilize backup compression, so your time to backup and restore and time to transfer the backup is shorter. It is well worth investigating and is a great time, disk space, and bandwidth saver. Thanks again.

    Jan S./SEAQL

  7. This goes back to the point I made. I can’t quite see how I would treat Log Shipping for a ‘small’ database any differently.
    Nice article but seems to have missed the point.

  8. Gud article you given step by step process could you please provide log shipping errors and solutions .information

  9. Nice .you given step by step process could you please provide log shipping errors and solutions .information

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |