SQL Server Replication
Forgive us, we get pretty excited about what we can do with SQL Server
There are numerous options if you are looking for data replication out of and into SQL Server. One of these has been (and continues to be) HVR, enabling replication in both homogeneous (SQL Server to SQL Server) and heterogeneous environments.
To support SQL Server databases in high volume environments with minimal impact on the database we developed log-based Change Data Capture (CDC) technology. To identify what is in the logs, HVR provides a parser that runs outside the database.
HVR is used in several high volume, busy SQL Server environments. Customers take advantage of many generic capabilities HVR provides for all platforms. With SQL Server as one of our strategic source platforms, we provide some pretty exciting capabilities for SQL Server users. Here are a few of the features we are most excited about:
- Log-based Change Data Capture at the File System Level
- DDL Propagation
- SQL Server type and version support
Log-based Change Data Capture at the File System Level
At a high level, there are two methods to retrieve transaction log fragments from a SQL Server Database:
- Perform calls through SQL, using Microsoft’s functions fn_dblog and f_dump_dblog.
- Access the files directly on the file system.
The first approach involves continuous, frequent interactions with the database to retrieve fragments of the log at relatively small increments. HVR supports this method to enable customers with just a database connection to benefit from log-based CDC.
The second approach directly accesses the transaction log backups and the TLOG on disk. Storage systems provide caching, and reading the files from disk can be performed with very low impact, using large block access as needed. HVR supports this method for log backups and the online log, both on a primary instance and on a standby node of an AlwaysOn cluster. Depending on your setup you must assign HVR elevated operating system and/or instance privileges to support this method.
Which method is best depends on your environment. The first method introduces quite a bit more CPU overhead on SQL Server, but given sufficient resource availability, that may be acceptable. If your database generates more than single-digit GB transaction log changes per hour, then most likely the first approach will not be able to keep up. With the second method, HVR provides several options to allow you to trade latency requirements with higher-level system privileges.
HVR supports DDL propagation in heterogeneous environments from a number of relational databases, including SQL Server. You can benefit from this when your target is SQL Server, or when you source data out of a SQL Server database.
For example, with a source, Oracle Database new or modified tables and columns in Oracle can automatically be propagated to a SQL Server target database. Likewise, if data from SQL Server is sent to let’s say Snowflake, then new or modified tables and column definitions can automatically be propagated as well.
This automation makes management of your replication setup a lot easier!
SQL Server Type and Version Support
SQL Server provides a very rich and flexible database environment. In Azure, Microsoft extended this with support for even more different options to run SQL Server. There are different editions for on-premises deployments, including Express, Web, Development, and Enterprise Edition. Then there is Azure SQL, and the Azure Managed Instance. Likewise, AWS and the Google Cloud Platform offer a relational database server similar to either one of these. And there now is SQL Server on Linux. HVR provides solutions for all different options and flavors. For details, refer to HVR’s documentation.
Microsoft started supporting logical replication with SQL Server 2005. This is the lowest version HVR supports for log-based data replication. But occasionally, we still come across SQL Server 2000. Customers also run the latest and greatest SQL Server 2019, as well as anything in between.
With HVR’s modular approach to data replication, you can mix and match types and versions on the source and on the target side. There are no restrictions in connecting SQL Server sources with SQL Server or non-SQL Server targets. Likewise, are there no limitations to deliver changes into SQL Server from non-SQL Server sources.
To fully take advantage of HVR’s data replication for SQL Server, we invite you to connect with us for a free replication consultation to discuss your real-time data integration needs.