Simplifying the backup and Log Shipping of SQL Server via Commvault LiveSync Replication

In this article we explore how customers who want backups, but are also reliant on Transaction Log Shipping for replication instead of SQL Server Availability Groups clusters.  The Commvault Command Centre replication group removes almost all the complexity of configuring Log Shipping within the SQL Server Management Studio.  DBA’s will already be familiar with the many steps within SQL Server Management Studio required to configure Log Shipping between two SQL Server Instances, as well as the configuration of  a third SQL Server Instance for Log Shipping monitoring and alerts.  Commvault Data Replication Technology for databases removes the majority of that configuration and the requirement for dedicated disks to hold log shipping. It is also more secure, as it doesn’t require opening SMB ports, along with 135 for T-SQL Debugging and more importantly port 1433 (SQL Server Database Engine) is not used.

Commvault is able to achieve highly granular Recovery Point Objectives through creating a checkpoint in the Transaction Log for each SQL Server Database. That is followed by a Transaction Log backup of the ‘checkpoint-ed’ contents which marks the protected Virtual Log file pages as inactive. To prevent the log disk from filling Commvault will instruct SQL Server to truncate the inactive Virtual Log file pages.

Now, Microsoft SQL Server database ‘Log Shipping’ replication uses the exact same checkpoint process as Transaction Log backups.  This means that combining these two technologies is not possible because their respective truncation operations interfere with the Log Sequencing chain.  Commvault addresses this issue by introducing SQL Server ‘Live Sync’ backup and replication.  Transaction Log backups are written to Commvault Storage and the Commvault replays the Transaction Logs onto the destination SQL Server, whilst maintaining the SQL Server Transaction Log Sequence Chain integrity.  This approach also means that the source and destination computers can be hosted on different physical, virtual or cloud hardware.

By installing and registering Commvault Intelligent Data Agent (iDataAgent) software on the client Commvault uses Microsoft SQL Server VDI API’s to automate the backup, export, restore and replication of both the Databases to a destination SQL Server.  If you are not using the Commvault SQL Server iDataAgent, then before installing, you will need to meet these system requirements and check if your Commvault license supports LiveSync Data Replication. If you are certain your license does not cover LiveSync Data Replication, then you can review the most recent information about the Commvault Licensing program here and get in touch with your Account Manager to help you make the transition.

Once the backup and replication of SQL Server has been configured within Commvault.  The Commvault Replication Monitor dashboard shows the health the of the replication group. If there are any failures Commvault will alert and send notifications.  So effectively we will be using the central Commvault CommServe to also provide the Transaction Log Shipping Monitoring capability.

Step-by-Step how to create a LiveSync Replication Group 

Create a SQL Server Subclient with the Source Databases you want to backup and LiveSync and associate it to a Command Center Plan.

Create a Replication Group

Give the Replication group a name and choose the SQL Server client, Instance and the Database(s) that will be replicated.

Choose the destination Microsoft SQL Server client and Instance.

Choose where you want to write the database to, sync delay and the Recovery Type.  In this example, the source and destination will use the same name and local path.  The Recovery Type “No Recovery” will leave the destination database in a write-only restoring state.

Replication group is created immediately and replication will immediately follow the backup of the databases in the replication group.

The status this SQL Server LiveSync is tracked in the Replication Monitor.

Commvault then creates the target Database as per the Recovery Type chosen previously.

 

Note that connections to the destination SQL Server Database are rejected whilst it is in restoring mode. This is because it is effectively in Single-User mode with exclusive access to facilitate the replaying of the Logs Shipped via the LiveSync.

Now that the Replication Group has been configured, any changes written to Source Database will be periodically synchronised to the destination.  To demonstrate, I have added data into new tables.

USE AdventureWorksDW2017;

CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100));

INSERT INTO TestTable (FirstName, LastName) SELECT FirstName, LastName FROM DimEmployee;

(296 rows affected)

CREATE TABLE SalesOrderTable ([SalesOrderNumber] VARCHAR(100));

INSERT INTO SalesOrderTable ([SalesOrderNumber]) SELECT [SalesOrderNumber] FROM [FactInternetSales];

(60398 rows affected)

 

After the next backup and synchronisation

We disable the LiveSync

With the replication disabled, the Destination Database needs to be taken out of NORECOVERY mode and put into RECOVERY mode.

   

We can now see that the destination database can be read and the new tables have been shipped.

Step-by-Step how to Failback of a replicated database.

At the time of writing, there is no Failover Utility for replicated backups from this iDataAgent.  So we simply configure the LiveSync in reverse.

Firstly, let’s add some data to be replicated back

USE AdventureWorksDW2017;

CREATE TABLE SalesOrderTable2 ([SalesOrderNumber] VARCHAR(100)) ;

INSERT INTO SalesOrderTable2 ([SalesOrderNumber]) SELECT [SalesOrderNumber] FROM SalesOrderTable;

(60398 rows affected)

Create a new Subclient or use the default.

Choose the database(s) and the Command Center Plan.

Configure a new Replication Group

The previous destination content is now the source

The previous source instance is now the destination instance

Typically, your DBA team would drop the original source Database to avoid having to recreate connection strings used by the applications connecting to the database. However, in this instance I have chosen to write to let Commvault create a new Database.

Then choose the Recovery Type.  Here I have chosen “Stand by” mode which makes the database read-only whenever there is a break from replaying shipped logs.  When the database is ready to replay the shipped logs, all users are forcibly disconnected from reading database to briefly turn the database into NORECOVERY mode whilst the replay logs can commence.

Note that by using Standby mode you need to provide a path where the Undo Logs be written to. After the Live Sync operation is complete, the SQL Server Agent use the data from the undo file and the transaction log to continue restoring the incomplete transactions. After the restore completes, the undo file will be re-written with any transactions that are incomplete at that point.

Once the replication has completed

We can see that our database is in Standby / Read-Only mode

And we can see that the database tables can be queried.

There we go, it’s that easy! Even without a Failover Utility (like CommServe LiveSync and Virtualisation “Failover Groups”).

Configuring the LiveSync Replication from the CommCell Console.

Configuring the replication group is easiest from the Command Center, but those options are also available within the classic CommCell Console.

LiveSync Configuration is performed at the Instance level.

The timing for when the replication runs is configured via a standalone “Site Replication” schedule.

By default the replication occurs immediately after the backup

The Site Replication Schedule also contains the client, instance and database configuration.

Also, the Advanced Copy Precedence dialog presents a very useful feature that will allow for a DASH/Aux Copy to complete before restoring from a non-primary copy.

Under that configuration, Commvault will perform granular transaction log backups, make auxiliary copies, replay the replicated logs into the destination database, and do all the monitoring/alerts all through a simple HTML5 management interface.

For more information, please reach out to us or check out https://documentation.commvault.com and its “Scheduling Transaction Log Restores on a Standby SQL Server” whitepaper.

 

Opening the Commvault CommCell Console remotely

Long-time users of the Commvault CommCell Console whom have managed their environment off host would have been using the Java Web Start application. It was simple and easy. Just enter http://yourcommserve/console and open ‘galaxy.jnlp’. Unfortunately that galaxy is now far, far away. The ‘Java Web Start’ and ‘Java Plug-in’ have been deprecated since March 2019, and the continued use of Java SE requires an Oracle Subscription. So what is the best way to connect to the CommCell Console, without having to Remote Desktop into the CommServe each time?

Firstly, I cannot stress this enough – do not install the Commvault installation software to install the Commvault CommCell Console package onto your desktop. The risk here is that any patching of the CommServe also means the desktops must be patched at the exact same time which cannot be guaranteed, and using it this way can cause fatal errors including data loss.

The best way is to use the netx.jar bootstrap file. The simplest way to get the netx.jar is download it directly from the Commvault Cloud, and conveniently you don’t need to have a Maintenance Advantage login to download.

You can also elect to download the netx.jar file directly from your CommServe Web Server https://yourcommserve.company.local/console/netx.jar. If you are using Chromium based browser, you will likely be unable download the netx.jar file if your CommServe Web Server is using Self-Signed Certificates. If you have direct access to the CommServe, then you can copy the file located at “%CV_Instance001%\..\GUI\netx.jar” (e.g. C:\Program Files\Commvault\ContentStore\GUI\netx.jar”).

Now a number of times I’ve seen instances where people are using netx.jar to still be launching with Java 8 SE. At the time of writing it may seem like the Console works but you may run into Console Related Errors or expose yourself to the kind of risks mentioned earlier.

What you should be using is OpenJDK 11 instead. For almost two years Commvault CommCell Console has been compiled for Java 11. Currently OpenJDK 11.0.7 gets installed with the CommCell Console and is upgraded periodically with new Feature Releases of Commvault. OpenJDK 11 can be downloaded here thanks to the fantastic contributors to the AdoptOpenJDK community.

You can download the JDK as an MSI, but I prefer to download the zipped Binary instead because I would rather choose at runtime which Java Version I want to run. In this example the netx.jar and extracted zipped JRE are in my ‘downloads’ folder (don’t judge me) and created a shortcut on my desktop to.

 "%HOMEPATH%\Downloads\OpenJDK_11.0.7\bin\java" -jar "%HOMEPATH%\Downloads\netx.jar"

From here, just enter the CommServe Hostname and click OK.

Then wait for Java to run a few commands

and within a few seconds you are prompted to log into your CommCell Console.

Whilst not as simple and convenient as the old Java Web Start way, it is the safest way of running the console remotely without having to Remote Desktop into Commvault Servers.