DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Enterprise AI Trend Report: Gain insights on ethical AI, MLOps, generative AI, large language models, and much more.

2024 Cloud survey: Share your insights on microservices, containers, K8s, CI/CD, and DevOps (+ enter a $750 raffle!) for our Trend Reports.

PostgreSQL: Learn about the open-source RDBMS' advanced capabilities, core components, common commands and functions, and general DBA tasks.

AI Automation Essentials. Check out the latest Refcard on all things AI automation, including model training, data security, and more.

Related

  • Restoring the MS SQL Server Database in Easy Steps
  • How To Fix SQL Database Restore Failed, Database Is in Use
  • Resolving Log Corruption Detected During Database Backup in SQL Server
  • How to Build Your Exchange Server Recovery Strategy to Overcome Ransomware Attacks

Trending

  • Enhancing Secure Software Development With ASOC Platforms
  • Test Parameterization With JUnit 5.7: A Deep Dive Into @EnumSource
  • Effective Communication Strategies Between Microservices: Techniques and Real-World Examples
  • Power BI: Transforming Banking Data
  1. DZone
  2. Data Engineering
  3. Databases
  4. Creating a Hybrid Disaster Recovery Solution Utilizing Availability Group and Log-Shipping

Creating a Hybrid Disaster Recovery Solution Utilizing Availability Group and Log-Shipping

SQL Server Always on availability group is a great addition to SQL Server for providing high availability and disaster recovery.

By 
Priyanka Chauhan user avatar
Priyanka Chauhan
·
Feb. 09, 24 · Tutorial
Like (1)
Save
Tweet
Share
4.6K Views

Join the DZone community and get the full member experience.

Join For Free

SQL Server Always on availability group is a great addition to SQL Server for providing high availability and disaster recovery. Always-on availability group support failover the environment for a discrete set of user databases, known as availability databases, that failover together. They also support a set of read-write primary databases and several sets of corresponding secondary databases. Optionally, AGs can make secondary databases available for read-only access and some backup operations.  

SQL Server log shipping is another popular disaster recovery solution that utilizes transaction log backups shipped from a primary database on a primary SQL server instance to one or more secondary databases on separate secondary SQL server instances. The transaction log backups are applied to each secondary database individually to obtain a synchronized database as primary.

Log Shipping and SQL Server Always on Availability Group Can Be Included in a Disaster Recovery Solution for the Following Reasons

  1. To avoid a single point of failure if the underlying cluster fails.
  2. Difficulties in maintaining Windows failover cluster across regions.
  3. The secondary server is already part of another failover cluster.

Environment

 

Graphical user interface, diagram, schematic

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

The AlwaysOn availability group is already configured. Below are the Availability group properties,

AlwaysOn availability group

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

Transaction log backups will happen on AlwaysOn secondary

This article will discuss how to configure log shipping. Please note that the below steps for setting up Log shipping can be followed for “AlwaysOn Backup preferences,” “Prefer Secondary”, “Secondary only”, “Primary,” or “Any Replica”.Transaction log backups will happen on AlwaysOn secondary as per the settings below.

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

Transaction Log Shipping

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

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

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

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

Click on Schedule, 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 the Log shipping secondary server instance.

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

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

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

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

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

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

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

On AlwaysOn synchronous secondary JBSAG2, right-click JBDB database and click on properties. You may get the below error if “Readable Secondary” is set to NO in Availability Group properties.

TITLE: Microsoft SQL Server Management Studio

It cannot show the requested dialog.

ADDITIONAL INFORMATION:

It cannot show the requested dialog. (SqlMgmt)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The target database, ‘JBDB,’ participates in an availability group and is currently not accessible for queries. Either data movement is suspended, or the availability replica is not enabled for reading access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

Microsoft SQL Server Management Studio

If you receive the above error. Try changing “Readable Secondary” to Yes and try again.

If you receive the above error. Try changing “Readable Secondary” to Yes and try again.

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

backup settings

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

secondary server instances and databasesave log shipping configuration

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

schedule

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

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

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

jobs created

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 Always-on primary. There is no requirement to change any of these jobs to run on AlwaysOn primary only.

Issues Encountered in This Setup

LSCopy and LSRestore jobs may fail with the below error:

 
Date 1/8/2021 1:04:00 PM

Log Job History (LSCopy_JBSAG1_JBDB)

Step ID 1

Server JBSAG4

Job Name LSCopy_JBSAG1_JBDB

Step Name Log shipping copy job step.

Duration 00:00:00

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

 2021-01-08 13:04:00.30 *** Error: Could not retrieve copy settings for secondary ID ‘1d58dd23-142c-498f-83ab-5077791b5781’.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:04:00.30 *** 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.(.Net SqlClient Data Provider) ***

2021-01-08 13:04:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:04:00.30 *** 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.(.Net SqlClient Data Provider) ***

2021-01-08 13:04:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:04:00.30 *** 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.(.Net SqlClient Data Provider) ***2021-01-08 13:04:00.30 —– END OF TRANSACTION LOG COPY —–
Exit Status: 1 (Error)


LSRestore job error:

 
Date 1/8/2021 1:10:00 PM

Log Job History (LSRestore_JBSAG1_JBDB)

Step ID 1

Server JBSAG4

Job Name LSRestore_JBSAG1_JBDB

Step Name Log shipping restore log job step.

Duration 00:00:00

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

 2021-01-08 13:10:00.28 *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:10:00.28 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

2021-01-08 13:10:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

2021-01-08 13:10:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***

2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***2021-01-08 13:10:00.30 —– END OF TRANSACTION LOG RESTORE —–

 Exit Status: 1 (Error)


I searched through the internet and found the same/similar errors in LSCopy and LSRestore jobs if the server mentioned on these jobs is not the log shipping secondary server. Let us look at what I have in my database server.

LSCopy job step contains the below command:

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

LSRestore job step contains the below command:

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

In our setup, LSCopy and LSRestore jobs contain the correct log shipping secondary server as above, marked in green. You need to ensure that LSCopy and LSRestore jobs should contain the log shipping secondary servers only.

I started a profiler trace on the Log shipping secondary server and started the LSCopy job and found below:

LSCopy job

From the above screenshot, the function sys.fn_MSvalidatelogshipagentidreturns value 1 or 0 depending on the below query:

query

 
return case

when ((@agent_type = 0) and

 exists (select * from msdb.dbo.log_shipping_monitor_primary
 where primary_id = @agent_id)) then 1
when ((@agent_type in (1,2)) and
 exists (select * from msdb.dbo.log_shipping_monitor_secondary
 where secondary_id = @agent_id)) then 1
else 0 end


Let's execute the below query on Log shipping secondary JBSAG4 and verify the output:

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary] where secondary_id = '1D58DD23-142C-498F-83AB-5077791B5781'

We do not see any data in the object msdb.dbo.log_shipping_monitor_secondary for agent_id specified in the LSCopy job, so this means it is going to return always 0, and you will see the below error:

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.(.Net SqlClient Data Provider)

Let's execute the below query and see what valid ID we have in the object log_shipping_monitor_secondary

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary] where primary_database = ‘JBDB’

Secondary_id is 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23. Let's try replacing the LSCopy and LSRestore job as below:

LSCopy job:

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4

LSRestore job:

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4 

Once I had replaced the jobs using the above query, the job was completed fine.

It Always seems On failover happened from JBSAG1 to JBSAG2; once after the failover, someone opened the log shipping settings and configured a secondary server on JBSAG2 as below:

database properties

In the above action, I created 1 more LSCopy and LSRestore job on the secondary server JBSAG4. However, it seems like the job created as part of the JBSAG2 configuration was later removed, and this left the other job with the wrong secondary_id value. This is the reason for failure.

Database Disaster recovery Error message Log shipping Microsoft SQL Server Transaction log

Opinions expressed by DZone contributors are their own.

Related

  • Restoring the MS SQL Server Database in Easy Steps
  • How To Fix SQL Database Restore Failed, Database Is in Use
  • Resolving Log Corruption Detected During Database Backup in SQL Server
  • How to Build Your Exchange Server Recovery Strategy to Overcome Ransomware Attacks

Partner Resources


Comments

ABOUT US

  • About DZone
  • Send feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: