Blog Post

SQL Homework – July 2021 – Log shipping

,

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating