Configuring SQL Server 2012 Logshipping on AlwaysON Availability Group Databases with Successful Failover of AG and Logshipping
Configuring SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG) is supported in SQL Server 2012 or higher. We can failover AlwaysON Availability Groups between Primary and Secondary Server and also move or enable the Logshipping from new Primary Server to DR server. Below is the environment setup.
Three servers Server01, Server02, Server03 with Windows Server 2008 R2 as Operating System. Each server has one standalone default instance of SQL Server 2012. AlwaysON Availability Group (AG) and Listener “Prod_LSTNR” has been setup between Server01 and Server02.
Now we can setup Logshipping from Server01 to Server03 just the same way how we setup logshipping between any two server databases as mentioned below.
How to Setup Logshipping between two servers?
– Connect to SQL Server instance from SSMS using instance name as Server01.
– On Server01, right click the database you want to use as your primary database in the log shipping configuration, and go to its Properties and go to last tab “Transaction Log Shipping”.
– Select the “Enable this as a primary database in a log shipping configuration” check box, which will now enable the logshipping configuration options to choose backup, copy and restore settings.
– Under “Transaction log backups”, click on Backup Settings.
– In the Network path to the backup folder box, type the network path to the backup share to use it for placing the transaction log backups.
– If this backup share is located on the primary server itself then type the local path to the backup folder in the “If the backup folder is located on the primary server” text box. If the backup folder is not on the primary server, you can leave this box empty.
– Make sure that the SQL Server service account on the primary server has full permissions on the backup share.
– Configure the Delete files older than and Alert, if no backup occurs within parameters.
– You can choose to customize the schedule for log backup and alerts.
– Starting SQL Server 2008, backup compression is supported, so you may choose to enabled it or set it to use Instance wide backup compression configuration.
– Now, under Secondary server instances and databases, click Add.
– Click on Connect and then connect to the SQL Server instance Server03 that we want to use as the secondary server for logshipping.
– In the Secondary Database box, either choose a database from the list or just type the name of the database you want to create.
– On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.
– Make sure that you choose correct option above and appropriately prepare the database on secondary server.
– Now go to Copy Files tab, and in the “Destination folder for copied files” text box, put the path of the folder to which the transaction logs backups should get copied. This folder is often located on the secondary server or common share. Make sure that SQL Server service account on secondary has full permissions to this path.
– You may optionally choose to change the copy and alert thresholds as per your SLA’s
– Now move to Restore tab, under “Database state when restoring backups”, choose the No recovery mode or Standby mode option. If you are choosing Standby mode option, then also select that you want to disconnect users from the secondary database while the restore operation is performed.
– If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.
– You may optionally alter the restore and alert thresholds.
– If you want, you may also add a third server as Monitor server instance to monitor this log shipping configuration. You need to decide whether you want to use monitor server or not, because, if you want to add the monitor server later, we need to remove logshipping entirely and reconfigure it with monitor server.
– If you choose to configure monitor server as well, then click on Connect and connect to the SQL Server instance to use as the logshipping monitor server.
– Under Monitor connections, choose the connection method to be used by the backup, copy, and restore jobs to connect to the monitor server. Under History retention, choose the length of time you want to retain a history of log shipping.
– On the Database Properties dialog box, logshipping tab, click OK to begin the configuration process.
– Verify and make sure that logshipping log backup job is running successfully on Primary server Server01 and is creating the backup file on the backup share we provided to place the log backup files.
– Verify and make sure that logshipping copy job is running successfully on Secondary Server Server03 and is copying the backup file from the backup share to path specified during copy settings for secondary server.
– Verify and make sure that logshipping restore job is running successfully on Secondary Server Server03 and is restoring the backup files.
– Check Logshipping report from SSMS on Server01 and Server03, by right clicking on SQL Server Instance -> Reports -> Standard Reports -> Transaction Log Shipping Status.
Now we have successfully setup logshipping from Server01 to Server03, Next thing is to failover AlwaysON Availability Group “PROD_LSTNR” from Server01 to Server02, after which we also want to move the logshipping to happen from Server02 to Server03 and disable logshipping from Server01 to Server03.
Disable the logshipping from Server01 to Server03 by following below steps
Connect to SQL Server instance using SSMS with instance name as Server01 and Server03
Disable logshipping log backup job on Server01.
Disable logshipping copy job on Server03 related to Server01 as primary.
Disable logshipping restore job on Server03 related to Server01 as primary.
Perform AlwaysON Availability Group “PROD_LSTNR” failover from Server01 to Server02. Verify to make sure databases are synchronized and no errors related to AlwaysON.
Now configure logshipping from Server02 to Server03
– Connect to SSMS using SQL Server instance name as Server02.
– Configure logshipping from Server02 to Server03 using steps mentioned above under section “How to Setup Logshipping between two servers?”.
– Make sure logshipping is working fine by checking new backup job on Server02 and new copy and restore jobs on Server03 which are related to Server02 as primary.
From now onwards, you can failover AlwaysON Availability Group from Server01 to Server02 or vice-versa and at same time switch the logshipping to be performed from Primary AlwaysON AG database to Server03, by disabling the logshipping jobs related to AlwaysON AG secondary server.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)