Setup SQL Server Database Log Shipping

In this part I will show you guide to setup SQL Server Database Log Shipping. SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

SQL Server Log Shipping consists of some jobs, so you must enable SQL Server Agent Jobs:

• backup job: A SQL Server Agent job that performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. When log shipping is enabled, the job category "Log Shipping Backup" is created on the primary server instance.
• copy job: A SQL Server Agent job that copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. When log shipping is enabled on a database, the job category "Log Shipping Copy" is created on each secondary server in a log shipping configuration.
• restore job: A SQL Server Agent job that restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. When log shipping is enabled on a database, the job category "Log Shipping Restore" is created on the secondary server instance.
• alert job: A SQL Server Agent job that raises alerts for primary and secondary databases when a backup or restore operation does not complete successfully within a specified threshold. When log shipping is enabled on a database, job category "Log Shipping Alert" is created on the monitor server instance.

Log shipping consists of three operations:

1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the secondary server instance.

These operations run at a specific interval (which can be scheduled) so data movement from Primary to Secondary is based on this interval of Jobs Runs

In this part, I will setup SQL Server LogShipping on my local VMWare servers which I had deployed before, you can see the guide from this post: Setup VMware SQL Server

I have 3 different servers:

1. Domain Controller Server (DC01): to host active directory/domain services
2. SQL01: Primary server that host SQL Server
3. SQL02: Secondary server that host SQL Server

Setting Up SQL Server Database Log Shipping

1. First thing, to setup Log Shipping you need to have a shared folder between SQL01 and SQL02, so here I make a shared folder called "LogShipping" 

2. Now login to SQL01 instance, and then here I create a new database to be used as Log Shipping Database, open the properties of the database and select the Enable this as a primary database in log shipping configuration checkbox

3. In the Backup Settings options, input the path to the Shared Folder that was made before. In here you can also control backup file retention period, default is 72 hours, and alert for backup. You can also schedule the backup job for this Log Shipping. In this case i will just use the default options so then Press OK.

4. Now click Add on the Secondary Database Settings

5. Now on the new Pop Up Window connect to the SQL02, and then on the Initialize Secondary Database option, you will have 3 options.

First option is if you haven't backup the primary database, so SQL Server will do full backup of the database and restore it to the secondary instance

Second option is if you already do full backup of primary database but you haven't restored it to secondary instance

Third option is if you already restored a backup of primary database in you secondary instance

In this case, I choose the first option because I haven't done anything yet to the database including creating a full backup

6. Now in the Copy Files option, first thing, I created a folder in the Secondary Instance which is C:\LS_Backup   , now i put the directory of this folder as the target directory to store backup files from primary server to secondary server. In this part you can also setting the schedule of the Log Shipping Copy Job.

7. Next, in the Restore Transaction Log option, you can select the state of the secondary database, either the state will be on No Recovery mode, or Standby mode. If you choose no recovery mode, then you cannot access the secondary database, but if you choose standby mode then you can read the secondary database. In this part, you can also set the schedule of the Log Shipping Restore Job.

Now after you finish setting up everything, press OK

8. Click OK to implement the Log Shipping to SQL Server, wait until all the processes finish. After that, you can connect to SQL02 and then you will see the database will be initiated there and in this example I choose Standby mode so the secondary database will have Standby state, which makes the DB readable for SELECT query

Now everything has been completed and Log Shipping has been successfully configured in the SQL Server Instance with SQL01 as the primary and SQL02 as the secondary