SQL Homework – July 2021 – Log shipping

Leave a comment

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,576 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: