AlwaysOn Availability- log shipping Solution
AlwaysOn availability group (AG) feature is a high-availability and disaster recovery solution that provides an enterprise-level
Prerequisites for using log shipping
Before you set up log shipping, make sure you meet the following prerequisites:
The primary database must use the full or bulk-logged recovery model. Switching the database to simple recovery causes log shipping to stop functioning.
Before you configure log shipping, you must create a shared path to make the transaction log backups available to the secondary server.
The log-shipping stored procedures require membership in the sysadmin fixed server role.
The backup shared path should have read and write permissions to the SQL Server service account.
Configure log shipping DR solution example
Status
https://developer.rackspace.com/blog/Microsoft-SQL-Server-log-shipping/
If an AG fail over between PRIMEHEAD and HEAD2 occurs, log shipping is interrupted until you configure it considering AG specifications.
Now you can configure log shipping from HEAD2 to HEAD3, which will not impact log shipping functionality even if there is a fail over in the future between the AG replicas. Log backup always happens on the same path or location, regardless of which replica is serving as the primary.
You need to initiate a fail over between the replicas. Before initiating the fail over, complete the following steps:
Execute an LS backup job on primary replica and disable the job.
Execute an LS copy and restore job on secondary replica and then disable it
---------------------------------------------------------------------------------------------------------------------------------------------------------
FIX: Log shipping fails when you use it together with Always On Availability Groups in SQL Server 2016 and 2017
Symptoms
Resolution
When you use log shipping together with Always On Availability Groups in Microsoft SQL Server 2016 and 2017, log shipping can't detect available replica and fails with the following error message:
The backup operation on database 'DatabaseName' was skipped because it is part of an availability group and not its preferred backup replica
This problem is fixed in the following cumulative updates for SQL Server:
Cumulative Update 4 for SQL Server 2017
Cumulative Update 7 for SQL Server 2016 SP1
About cumulative updates for SQL Server:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Change Availability Group Endpoint IP
We have two nodes in an Always On availability group, on SQL Server 2014 Enterprise.
We need to change the IP addresses of the nodes, the cluster, and the listener.
ALTER ENDPOINT [MyEndpoint] STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = (10.x.x.x)) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate, ENCRYPTION = REQUIRED ALGORITHM AES) GO