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.

Thanks,
SQLServerF1 Team
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)

 

SQL Server 2012 Logshipping Backup Job Completes Successfully but No Backup File Created

I encountered a strange issue while working on SQL Server 2012 AlwayaON Availability Group databases which also has Logshipping configured. After failover of AlwaysON Availability Group databases from primary to secondary, and also after moving the logshipping from new primary server to DR server, logshipping backup job was having issues where the job was completing successfully, but was not creating the log backup files. 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. Logshipping has been setup from Server01 to Server03(This is just same way how we setup logshipping between any two server databases). AlwaysON and Logshipping has been working without any problems after the initial setup.

Now, when there was a planned maintenance to replace Server01 and Server02 with new hardware, we can failover AlwaysON AG to secondary server and also configure or move logshipping from new primary server to DR server. We decided to do this with minimum downtime to production users by moving all databases to one of the server and to perform the maintenance on the AlwaysoN AG secondary server.

As part of planned maintenance, we failed over everything to Server01 and performed maintenance on Server02 and added it back to the AlwaysON AG. Verified and AlwaysON AG databases got synchronized in some time and logshipping also was working fine without any problems.

Now we have to perform maintenance on Server01, so we moved the AlwaysON AG “Prod_LSTNR” to Server02, which was successful and databases are synchronized as well. As expected, there is no logshipping configuration on databases now as logshipping was only setup when databases were primary on Server01 to Server03. We decided to configure logshipping from Server02 to Server03 now, so that logshipping to Server03 happens when the AlwaysON AG databases are either on Server01 or Server02.

Disabled Logshipping Backup job on Server01, disabled logshipping copy and restore jobs on Server03, which disabled logshipping from Server01 to Server03. Took server01 down for maintenance and is no more available now. Configured logshipping from Server02 to Server03 which created new backup job on Server02 and new copy and restore jobs on Server03. All logshipping jobs started running without any errors.

Later, maintenance of Server01 finished and was online and got connected to AlwaysON AG “Prod_LSTNR” and successfully synchronized as secondary.

After some time, we started receiving alerts for logshipping that the copy and restore latency was above the thresholds. We checked and found backup, copy and restore jobs from Server02 to Server03 are all running successfully. Logshipping report shows that there was no backup, copy or restore performed since Server01 was back online. Checked the history of backup job and noticed that backup was not performing any backups, it was just skipping performing the log backup on Server02.

Started verifying the logshipping and AlwaysON settings and noticed that AlwaysON AG backup preferences was set to run backups on any replica with priority of 60 for Server01 and priority of 40 for Server02. As now, we have both Server01 and Server02 online and Server02 acting as AlwaysON Primary, log backups cannot be performed on Server02 due to low priority and that Server01 is online. Changed the AlwaysON AG backup preferences to run backups on Primary Server, after which backup job started creating the backup files successfully. Later copy and restore jobs also starting working without any issues or latency.

You can find AlwaysON backup preferences from SQL Server management Studion (SSMS) -> Expand Always On High Availability -> Availability Groups -> Open properties of Availability Group where the databases in part of -> Go to Backup preferences tab, where we can change the settings.

If we see the issue only from logshipping perspective, we could not have identified the issue, looking into the issue as AlwaysON along with logshipping helped us in identifying the issue.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

SQL Server 2014 Support for Cluster Shared Volumes (CSV) in a Failover Cluster

Starting SQL Server 2014, Failover Cluster Instances supports Cluster Shared Volumes (CSV) in both Windows Server 2008 R2 and Windows Server 2012.

What is Cluster Shared Volumes (CSV)? How Cluster Shared Volumes (CSV) works?
As per Technet below is the definition of CSV.

To understand how Cluster Shared Volumes (CSV) works in a failover cluster, it is important to understand how a cluster works without CSV. Without CSV, a failover cluster allows a given disk (LUN) to be accessed by only one node at a time. Given this constraint, each Hyper-V virtual machine in the failover cluster requires its own set of LUNs in order to be migrated or fail over independently of other virtual machines. In this type of deployment, the number of LUNs must increase with the addition of each virtual machine, which makes management of LUNs and clustered virtual machines more complex.
In contrast, on a failover cluster that uses CSV, multiple virtual machines that are distributed across multiple cluster nodes can all access their Virtual Hard Disk (VHD) files at the same time, even if the VHD files are on a single disk (LUN) in the storage. The clustered virtual machines can all fail over independently of one another.

The above mentioned description is interms of Windows Operating System, which can be better understood by a System Administrator, so let’s try to put it in terms of SQL Server, as CSV is supported by SQL Server 2014 or higher.

Without CSV, a traditional failover cluster which we already know about, allows a given disk (LUN) to be accessed by only one node at a time, meaning SQL Group will own the disk resource and that disk resource cannot be used by any other groups.

Now, with CSV support in SQL Server 2014, we can have multiple groups which are owning different SQL Server Instances, can all use single LUN. Now, we can failover one instance from one node to another node, without affecting or having to failover the disk resources along.

Let’s take an example for better understanding.

– Let’s say we have two nodes Node1 and Node2.

– We then ask System Admins to create a windows cluster/a> and then ask them to add Disk Resources as Cluster Shared Volumes(CSV).

– When SysAdmins add the CSV, we will see the LUN in a path like C:\ClusterStorage\SQLDBFilesDisk\ , where we can rename to VolumeX to more meaningful name.

– We can access this path from both the nodes.

– We then Install one SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in below image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance1\

Cluster Storage Volume CSV

Cluster Storage Volume CSV


– We then Install another SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in above image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance2\

– Once the installation completes, we can browse to the database files by going to C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\ from any of the nodes. Here we are using the same LUN for both the SQL Server Instances, which was not possible before SQL Server 2014.

– If we open SQL Cluster Manager, and go to SQL Server Group, we will only see SQL Server, SQL Agent, SQL Virtual Network Name and IP Address resoures, but the Disk resources will not be there anymore as there are now independent of any one group.

Hope this clarifies what is Cluster Shared Volumes (CSV) and How it can be used with SQL Server 2014.

References:
Cluster Storage Volume
Deploying SQL Server 2014 with Cluster Shared Volumes

Happy Reading,
SQLServerF1 Team