SQL Server 2008 R2 : LOG SHIPPING – A VERY LARGE DATABASE


Scenario:  A couple of days back, when we were setting up a DR plan for one of our most mission critical DB server 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 purpose; therefore Log Shipping was the best choice. Implementing Log Shipping for small databases is relatively Simple as one can easily do it using the GUI but for large database GUI is not a good option as the database on the destination server will be always out of sync. So a question arises on “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 same.

For demonstration purpose, let us assume that we have two instances named INTEL and INTELabc residing on SQL Server 2008 R2 environment. We have a database named test on the instance named INTEL which is of the size 125 GB. 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 our 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 the same to hold the copy of the Transactional Logs. Ensure that the account under which the 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, 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 i.e around 125 GB it will take some time for it to be backed up. In my case it took approximate 1 hour and 12 minutes for the backup to get completed. Once the backup gets 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 works much faster and better than zip.

Once the file gets copied onto the secondary server in the folder named LogShippingLogs, we will restore it with the name Test_DR, which means that it is a copy of the database named test.  Also, keep in mind that when the Full Backup of the database is restored, it should be kept in NORECOVERY mode, which means that additional copies of backups can be restored. In order to restore the database, we will use the below T-SQL.

RESTORE FILELISTONLY FROM DISK=’F:\LogShippingLogs\test.bak’

The above command will return the logical name of the data and log files of the database named test, please refer the screen capture below.

 

As shown in the above screen capture, the logical name of the data and log file of the database named test is test and test_log respectively.

RESTORE DATABASE TEST_DR

FROM DISK=’F:\LogShippingLogs\Test.bak’

WITH

MOVE ‘test’ TO ‘E:\Microsoft SQL Server\MSSQL\Data\test.mdf’,

MOVE ‘test_log’ TO ‘E:\Microsoft SQL Server\MSSQL\Log\test_log.ldf’,

NORECOVERY,

STATS=10

Where E:\Microsoft SQL Server\MSSQL\Data and E:\Microsoft SQL Server\MSSQL\Log is the location where the data and log files of the database will reside.

Once the FULL backup of the database gets restored, by that time significant changes have happened to the database named test on the source server. We will therefore take the Differential Backup of the database named test on the source server using T-SQL as shown below:

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

After taking the differential backup, we will again RAR the copy of the differential backup and then transfer it to the folder named LogShippingLogs which is present on the F drive of the secondary server.

Once the differential backup gets copied onto the secondary server, we will restore it using the set of T-SQL commands as shown in the screen capture below:

RESTORE FILELISTONLY FROM DISK=’F:\LogShippingLogs\test_diff.bak’

This gives the logical name of the data and log file of the database named test.

RESTORE DATABASE TEST_DR

FROM DISK=’F:\LogShippingLogs\Test_Diff.bak’

WITH

MOVE ‘test’ TO ‘E:\Microsoft SQL Server\MSSQL\Data\test.mdf’,

MOVE ‘test_log’ TO ‘E:\Microsoft SQL Server\MSSQL\Log\test_log.ldf’,

NORECOVERY,

STATS=10

After performing the above, we will then start setting up the LogShipping for the database named test. On the primary server, perform the following steps:

Right Click on the database named test and Select Properties->Transaction Log Shipping, please refer the screen capture below:

 

Enable the check box, Enable this as a primary database in a log shipping configuration, please refer the screen capture below:

Click on Backup Settings, a new window appears as shown in the screen capture below:

In the text box named Network path to backup folder as well as the text box below that named If the backup folder is located on the primary server, type a local path to the folder, type the location where the transactional log file will reside on the server. It should be in the form \\servername\foldername. In our case, it will be \\INTEL\LogShippingLogs where INTEL is the server name whereas LogShippingLogs is the name of the shared folder on the F drive on the source machine which will hold the copies of Transactional Log backups.

You can specify the retention period of the Transactional Log files as 24 hours, I normally prefer keeping it for 24 hours. Append the name of the Backup Job with _DR which means we can clearly distinguish that this job will take the Transactional Log backup of the database named test which is involved in Log Shipping. You can change the Backup schedule as per your wish, by default it is 15 minutes and I myself kept the frequency as 15 minutes, please refer the screen capture below:

Click the Add button to add the Secondary server instance and databases please refer the screen capture below:

In the Secondary Server Instance text box, type the name of the Secondary Server Instance, it will be INTELabc; the name of the Secondary database will be test_dr.

Under the Initialize Secondary Database tab, Select the No, the secondary database is initialized tab. Under the Copy Files tab, In the Destination Folder for copied files text box, enter INTELabc\LogShippingLogs.Specify the retention period of copied files as 24 hours and Append the name of the copy Job with _DR which means we can clearly distinguish that this job will copy the Transactional Log backup of the database named test which is involved in Log Shipping from the server named INTEL to the server named INTELabc, please refer the screen capture below:

Under Restore Transaction Log tab, Select No Recovery Mode radio button and also Append the name of the restore Job with _DR which means we can clearly distinguish that this job will restore the Transactional Log backup of the database named test which is involved in Log Shipping onto the database named test_dr present on the server named INTELabc.

You can specify the source server itself to act as a Monitor Server instance, please refer the screen capture below:

Click on the OK button as shown in the screen capture below:

A screen capture appears as shown in the screen capture below which indicates that everything has been completed successfully.

That’s it, we have successfully implemented performing a Log Shipping on a database whose size is of 125 GB. I hope you all have liked it, any suggestions for improvement are most welcome.

Advertisements

One Response

  1. Great article! I was wondering about your choice to use RAR for compressing the .bak file. Can’t 2008 R2 compress this for you? Also, I’m not clear why you change the database name on the Secondary server. Wouldn’t you want the same name so that in the actual disaster, all you have to do re-route traffic from one server to the other and not change your connection strings? For example, if my connection string is “Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;” during the disaster, I can simply change my hosts file or DNS to route traffic to a different server. I don’t see how you’ll manage a different Initial Catalog setting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: