Log shipping from Sql Server 2008r2 to Sql Server 2008R2
Log shipping from one server to another server is an option that a DBA has, for high arability in Sql Server.
It’s very much like Oracle data guard where from primary to standby archive redo logs goes and are applied automatically. Comparing to Oracle, I would say its lot easy to configure and troubleshoot.
Following are the pre-requisites of log shipping.
Primary database should be running in Full or Bulk recovery mode
Create a shared folder to store backup of transaction logs
We can use either SSMS or using T-Sql, I have discussed SSMS methods following.
Right click on the database and choose property options
From property option select “Transaction Log Shipping”
Select “Enable this as a primary database in a log shipping configuration” check box
Select “backup setting” under “transaction log backup”
In “network path to backup folder” provide path of shared network folder where backup files will be stored.
Now click on “Add” option and it will bring you to new page that asks for connection to your secondary database. Click on “Connect” button and provide logon information of your server hosting secondary database.
In “Initialize Secondary database” tab select first radio button if you want your secondary database to be created from scratch.
In “Copy files” option provide the path of locally created folder.
In “Restore Transaction Log” option choose standby mode and click ok. This will take you back at “Transaction log shipping page”
Optionally you can provide information of monitoring instance.
Now! That’s all you need to set up a Primary/Standby or Primary/Secondary database in SQL server.
This whole setup has created 4 jobs. 1 in primary database and 3 in secondary database. Following I have discussed the jobs.
The “Backup” job created in Primary site, would take backup of transactional log periodically. Sql Server agent put the backup files to designated shared folder.
The “Copy” job created in secondary site, would copy the backup logs from shared folder to local machine.
The “Restore” job would periodically restore the backup on secondary site and keep secondary database in sync with primary database.
The “Alert” job sends alerts at each event.
Any correction,suggestion would be appreciated.
Thanks and regards,
