Log shipping creates jobs for Backup, Copy and restore. We need to create folders for the Backup and Copy. Backup folder should be on the network and SQL Server Agent service accounts of both primary and secondary server should have access (r/w) to the folder. Copy folder can be either local to secondary or on network.
1. Create folder for backup either on network or create a local folder and share it.
2. Open folder properties.
3. Open Sharing tab. Click “Share”.
4. Add Service accounts of SQL Agent of primary and secondary server and Read/Write permissions. Click “Share”.
5. Now the folder is shared on the n/w and network address of the folder is \\WIN2\Log_Backups.
6. Create COPY folder. And add permissions for Service account of SQL Agent of secondary server.
7. Now open properties of the Database on log shipping is to be configured and click “Transaction Log shipping”.
There is nothing as of now we have to configure it.
8. Click on “Enable this as a primary database in a log shipping configuration”.
9. Click on “Backup Settings”.
10. In the “Network path to backup folder” give the network path of the backup folder we created earlier.
11. We can decide the retention period of the backup files. Here we have set the retention period as 10hours that means the transaction log backups older than that will be deleted automatically. We can also mention the time in which the alert will be generated if no backup occurs. Here we have set that time as 30 minutes.
12. The default Schedule of backup jobs is always 15 minutes. If you want to change that click on “Schedule”. Change the schedule as per the requirements of your environment.
13. After setting the schedule click “Ok”. And then again “OK ” to save the backup settings. Now we have configured the backup jobs settings.
14. Now we have to add the secondary server(s). Click on “Add”.
15. In the Secondary Database Settings click on “Connect” and connect to the secondary server.
16. Once you are connected (Here our secondary server is WIN2\INSTANCE3). It will ask for the secondary database name. If you have already initialized the Secondary database than choose that else provide the name of the Secondary database. By default its same as Primary database.
17. In the tab “Initialize Secondary Database” choose the appropriate option. There are three options. a) Let it create a backup and restore it. b) You already have a backup and can provide the location of the backup. c) You have already initialized the secondary database.
I haven’t that’s why chosen the “Yes, generate a full backup of the primary database and restore it into the secondary database (and create the secondary database if it doesn’t exist)”.
You can also provide the database data file and log file backup directory by clicking on “Restore Options..”
18. In the Copy Files tab provide the destination folder location (the one we created earlier). We also have the option of deleting the old log backup files. Here I have chosen it as 10 Hours. If you want to change the schedule of Copy job click on “Schedule” and change it according to your needs. I am happy with the 15 minutes so haven’t changed them.
19. Click on “Restore Transaction Log” tab.
20.Here we have the option to choose whether we want to keep the secondary server in “No recovery mode” i.e it will not accessible or “Standby” by so that it available but in read-only mode. I have chosen the “Standby mode”.
21. If you want some delay between your secondary and primary server than you can choose the delay that you wish. It is helpful in case someone has deleted some table from primary and your secondary is running with some delay than you can copy the table back to primary from secondary.
22. Change the Restore job name and schedule if you wish too. I haven’t made any changes to them. Click “OK” if everything is as per your need.
23. Now we have a secondary server.
24. If you want a monitor server instance than click on “Use monitor server instance”. And click “Settings”.
25.Connect to the Instance of the SQL Server where history of log shipping will be stored and jobs for alerts will run.
26. I have chosen the same instance as secondary server for monitoring.
27. Provide the service account for the jobs to connect on the monitor server instance. If the service account of SQL Agent of both secondary and primary have access rights to the monitor chose the “By impersonating…” else provide the Login which does have the access.
28. Everything is set click “OK”.
29. If everything goes well you will see all around Success.
30. Check whether the database is created on the Secondary server or not.
31. You can check the Server level standard report for Transaction Log shipping Status for verifying and monitoring later on.