Configuring Log Shipping with AlwaysOn

AlwaysOn + Log shipping related blogs
Below blog post are all related to AlwaysOn and log shipping configured together,
-> Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing
-> Removing Log shipping on a database with AlwaysOn configured

Environment

-> JBSAG1, JBSAG2 and JBSAG3 are part of a failover cluster without shared storage. Alwayson Availability group is configured between JBSAG1, JBSAG2 and JBSAG3. JBSAG1 is the current primary, JBSAG2 is the synchronous secondary and JBSAG3 is Asynchronous secondary. The database(s) that is part of Always on Availability group will have log shipping configured and its secondary will be JBSAG4.

-> AlwaysOn availability group is already configured. Below is the Availability group properties,

-> Transaction log backups will happen on AlwaysOn secondary as per the settings below.

-> This article will discuss how to configure log shipping. Please note that below steps for setting up Log shipping can be followed for both “AlwaysOn Backup preferences” “Secondary only” or “Primary”.

-> On AlwaysOn primary JBSAG1, right click JBDB database and click on properties. Click on “Transaction Log Shipping”,

-> Check “Enable this as a primary database in a log shipping configuration” and click OK.

-> Click “Backup Settings…”. Provide appropriate location where the backup should be placed,

-> Click on Schedule and set appropriate schedule for backup and click OK,

-> Click OK for “Transaction Log Backup Settings”. Click on “Add..” under “Secondary server instances and databases:”. In “Secondary Database Settings” connect to Log shipping secondary server instance.

-> Under “Initialize Secondary Database”, select appropriate option that suits better. In my case I will select first option, since my database is not that big.

-> Click on “Copy Files” and provide appropriate location where the files should be copied over. Select “Schedule” and change the schedule of copy job appropriately,

-> Click OK on the copy job. Click OK on “Secondary Database Settings” and then Click “OK” for “Transaction Log Shipping” on database properties.

-> On AlwaysOn synchronous secondary JBSAG2, right click JBDB database and click on properties. Click on “Transaction Log Shipping”, provide same settings for “Network path to backup folder” and Change the backup job schedule same as what was provided for log shipping settings in AlwaysOn primary JBSAG1.

-> Make sure it is blank for “Secondary server instances and databases:”. Click OK.

-> On AlwaysOn asynchronous secondary JBSAG3, right click JBDB database and click on properties. Click on “Transaction Log Shipping”, provide same settings for “Network path to backup folder” and Change the backup job schedule same as what was provided for log shipping settings in AlwaysOn primary JBSAG1.

-> Make sure it is blank for “Secondary server instances and databases:”. Click OK.

-> With this log shipping setup is complete. Below are the jobs that are created after the Log shipping setup,

-> You can try failover and failback to see if everything works fine. All these jobs can run on respective SQL Server instances irrespective of which SQL Server instance JBSAG1, JBSAG2 or JBSAG3 is Alwayson primary. There is no requirement to change any of these jobs to run on AlwaysOn primary only.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

2 thoughts on “Configuring Log Shipping with AlwaysOn

  1. Pingback: Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing | JBs Wiki
  2. Pingback: Removing Log shipping on a database with AlwaysOn configured | JBs Wiki

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