Wednesday, December 7, 2011

Step By Step Guide to Configure the Log-Shipping On SQL Server

About Log-Shipping:
Log shipping technology is one of the methods for implementing High Availability solution. Log shipping is easy to set up, maintain and troubleshoot. Standby database can be available for reporting purpose and multiple standby databases can be configured for log shipping in the same server or instance. 

Number of  Jobs will create for Log Shipping:
Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.

Minimum Requirements for Configuring Log Shipping:   
Click here for Prerequisites for Log Shipping In SQL Server 2008.  


Steps By Step guide to configure Log-Shipping:
In this example, I have used three instances for log shipping and Personal_Database is used for log-shipping.
 

Log Shipping Database Name: Personal_Database
Primary Server Instance:  NARESH\PRINCIPAL1
Secondary Server Instance: NARESH\SECONDARY
Monitor Server Instance: NARESH\MONITORSERVER

Make sure your database is in full or bulk-logged recovery model, because T-Logs are backed up and transferred to secondary server. where in Simple recovery mode you cannot take T-Log Backup. 


Step 1:  
Go to the Primary Instance and Right Click on Personal_Database (in my case i am using Personal_Database is for Log-Shipping) and Click on Tasks and then click on Ship Transaction Logs.


Step 2:  
The next step is to configure and schedule a transaction log backup, click on Backup Settings.


Step 3: 
If you are creating backups on a network share enter the network path or for the local machine you can specify the local folder path. Here i am giving local path as well as network path. 

Delete files older than: Delete files older than 72 is default, you can change as per your requirements.
Set backup Compression: The backup compression feature was introduced in SQL Server 2008 Enterprise edition. While configuring log shipping, you can control the backup compression behavior of log backups by specifying the compression options from drop down.

After setting up both the options now click Schedule.


Step 4:  
Now you have to configure the Transaction Log backup settings. 
  Name: In my case job name is LSBackupSchedule_NARESH\PRINCIPAL1 
  Schedule Type: Recurring
  Frequency: Runs daily for every 15 minutes.


Step 5:  
For configuring the secondary instance and secondary database. Click on the Add button to configure the Secondary Server instance and secondary database. You can add multiple servers if you want to setup one to many server log-shipping, In this example i am setting up one to one server log-shipping.


Step 6:   
Once you clicked the Add button it will open a new window where you have to configure the Secondary Server and secondary database. Click on the Connect button to connect to the secondary server. Once you connect to the secondary server you can access the three tabs as shown below.

6.1: Initialize Secondary Database: 
In this step you can specify how to create the database on secondary server. 1. Create a backup and restore it, 2. use an existing backup and restore or 3. do nothing because you have manually restored the database and have put it into the correct state(recovery or standby mode) to receive additional backups.

  
6.2: Copy Files:
In this tab you have to 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.


6.2.1: Schedule:
Now you have to configure the Copy Transaction Log Backup files from Primary server to Secondary server.  Name: In my case job name is DefaultCopyJobSchedule
  Schedule Type: Recurring
  Frequency: Runs daily for every 15 minutes.


6.3 : Restore Transaction Log: 
You have to specify the database restoring state information (Restore with NORECOVERY or STANDBY mode) and restore schedule for creating restore job on the secondary server. Check the option Disconnect users in the database when restoring backups or else it will leads to other issues like backup failures etc...
 

6.3.1: Schedule:
Now you have to configure the schedule for Restore primary server transaction log on Secondary server and leave the database in NORECOVERY or STANDBY mode.
  Name: In my case job name is DefaultRestoreJobSchedule
  Schedule Type: Recurring
  Frequency: Runs daily for every 15 minutes.


Step 7:  
For alerting notifications we will configure Log Shipping Monitoring which will notify us in case of any failure. Monitoring configuration is optional, if you need monitor server then configure or else ignore. Click on Settings button which will a new window for Log Shipping Monitor Settings.

 

Step 8:  
Now Click on Connect button to setup a monitor server. Monitoring can be done from the Primary server, Secondary server or a separate SQL Server instance. Monitor server configure for alerting Primary or secondary server jobs fails and we can also configure how long job history records are retained in the MSDB database. 

NOTE: You cannot add a monitor Server once log-shipping is configured.


Step 9: 
Click on the OK button to finish the Log Shipping configuration and it will show you the below screen.


Finally you all done. In my next article i will cover how to check Log-shipping status, fail-overs and etc...



How to configure log shipping, Configuring Log Shipping, About Log Shipping, Configure Log Shipping, Step By Step SQL Server Log Shipping, Step By Step Log-Shipping Configuration for SQL Server 2012, Step By Step Log-Shipping Configuration for SQL Server 2008 R2, Step By Step Log-Shipping Configuration for SQL Server 2005, SQL Server 2005 log shipping setup using the wizard, Prerequisites for Log Shipping. Log Shipping Deployment. Log Shipping Requirements . Log Shipping 'skipping' all logs and not restoring. An Overview of Log Shipping in SQL Server 2005. An Overview of Log Shipping in SQL Server 2008 r2. An Overview of Log Shipping in SQL Server 2012. sql server 2000 log shipping multiple servers. prerequisites for replication. SQL Server Log Shipping to a Different Domain or Workgroup. Ship's Log Requirements. Prerequisites for Migrating from Log Shipping to AlwaysOn, step by step tutorial. step by step guide. Step by Step Installation. Step-by-step procedure. log shipping step by step. Step by Step Guide to. sql server step by step. sql server step by step ebook. microsoft sql server step by step ebook. sql server step by step tutorial. step by step installation guide. microsoft sql server 2008 step by step.

3 comments:

This is a well explained guide!
Very easy to follow and simple
Good Job!
you did however say that you will post another article on how to check Log-shipping status, fail-overs and etc...

It's not a good guide. This occurs on same computer. on 2 computers, there is some problem of right to access share folder.

Post a Comment