July 1, 2021 by Kenneth Fisher
Log shipping is probably the least complicated and easiest to configure of the various methods of creating a secondary copy of your database.
Here’s a basic overview of the process.
- Take a full backup of the primary database.
- Restore the database in the secondary location, leaving it in either recovering or standby mode.
- Take log backups of the primary database.
- Copy those log files to the server the secondary instance is on.
- Restore the log files to the secondary continuing to leave it in recovering or standby.
So first a few questions for you to either answer or research:
- Can I leave the secondary in a readable state? If so how?
- If the secondary is left in a readable state is it always readable? If not when is it not readable?
- What are some drawbacks to this method vs say Mirroring or Availability Groups?
- What are some benefits?
- Think about how you might set this up manually, at least in a general sense.
Now I want you to try this:
- Pick a database and open the log shipping properties. (5 pts)
- Add a secondary. (5 pts)
- Review options on each of the tabs. (15 pts)
- Configure the Initialize Secondary Database tab. (15 pts)
- Configure the Copy Files tab. (15 pts)
- Configure the Restore Transaction Log tab. (15 pts)
- Review the jobs created. (15 pts)
As an example of what you might see here is a real world set of requirements I had recently.
- There will be a full restore once a week, Sunday night/Monday morning. This will be handled by a different process.
- A backups (full, differential, and log) are handled by a separate process.
- The secondary is on the same server as the primary.
- The log restores should only happen once a day at 2am.
For the remaining 15 points configure a log shipping solution with those requirements.