Why use HVR over MSSQL Replication?
Why use a Third-Party Tool over SQL Server Replication?
(updated April 22, 2019)
Most database vendors provide their own native transactional replication solution. Microsoft is no exception. If your use case is to replicate data between two Microsoft SQL Server databases to keep them in sync, then a valid question to ask is:
“Why would I use a third-party replication tool such as HVR instead of SQL Server’s native software solution?”
Five Reasons to Consider HVR over MSSQL Replication
Note SQL Server supports multiple different kinds of replication for different use cases. Transactional replication, used for high availability, reporting, data warehouse, and data lake use cases and for which it is important to keep systems in sync, is the one HVR, as well as some other replication vendors, focus on.
There are different ways to implement transactional replication on SQL Server and depending on what method a third party vendor has selected some of the limitations of SQL Server’s own replication still surface in a third party product. In this post, I focus on five of the main reasons of why to consider using HVR instead of SQL Server replication.
Reason 1: Free..or Not?
The first reason is easy: depending on what exactly is your transactional data replication need, only SQL Server Enterprise Edition can satisfy your scenario. So if you don’t run that already then what may appear to be free functionality is not actually free. Of course, I realize HVR does not come for free either.
Reason 2: Replication Starting Point
MSSQL transactional replication is pretty strict about the starting point for the replication so that it can be sure systems are in sync once real-time replication catches up. A common way to achieve the starting point is to use Microsoft’s recommendation to start with SQL Server’s snapshot replication or start with a backup and an LSN. The disadvantage of either of these approaches is that it is all or nothing. If only a single table requires updating then you may want to refresh just the single table and replication should move on. For HVR this is a trivial thing to do. For SQL Server replication it isn’t and if your database is sizeable then this is a major challenge.
Reason 3: No Locks on Logs=Problems
MSSQL replication is great when it is up and running. But when it isn’t, and we assume that you still want to keep the databases in sync, it prevents the transaction log file from being truncated. This can be a problem if replication is down for an extended period of time–maybe because you run SQL Server databases in a distributed environment and it is expected that some databases are going to be inaccessible for some time, or there is a network outage. During this time the transaction log is only going to grow, and if your system runs out of disk space then it will stop working. HVR does not have this problem, because it does not rely on SQL Server’s replication functionality for its log-based capture, and it can read transaction file backups as well as the online log.
Reason 4: Primary Keys
Tables with no primary keys cannot be replicated using SQL Server transactional replication, but HVR supports these.
Reason 5: How Flexible?
Flexibility can be another reason. Maybe you want to perform some transformations between a source and a destination system. Or you want fewer columns and/or rows in the destination to serve a different purpose (e.g. the reporting server does not need all the scanned images of the source database). Or you want multiple replication streams out of one database into multiple other databases. With HVR it is easy to set up scenarios like this but you cannot do these things with SQL Server transactional replication.
So even though there is a native data replication technology with SQL Server it still may make sense to consider an alternative, even before we discuss heterogeneous environments.