Why use HVR over MSSQL replication?
Why use a third-party tool over SQL Server Replication?
Most database vendors provide their own native replication solution. Microsoft is no exception. If your use case is to replicate data between two Microsoft SQL Server databases 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
There are different ways to implement 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: The first reason is easy: full SQL Server replication is only available on SQL Server Enterprise Edition 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: MSSQL 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 SQL Server’s snapshot replication. The disadvantage of this approach 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: MSSQL replication is great when it is up and running. But when it isn’t it keeps a lock on the log to prevent it 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. 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 log.
Reason #4: Tables with no primary keys cannot be replicated using SQL Server replication, but HVR supports these.
Reason #5: 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 replication.
See for yourself with a demo of HVR SQL Server Replication Capabilities
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.