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.