SNAP Mirror Replication
SNAP Mirror Replication vs Log-Based Data Replication Tools
I came across the suggestion this week to use SAN-based SNAP Mirror for database replication instead of the type of log-based replication that other database replication tools, including HVR, perform.
Differences between SNAP Mirror and Log-Based
SNAP Mirror replication software is generally provided by a storage provider, and many providers such as EMC, HPE, IBM and NetApp provide solutions. You can, of course, replicate more than just databases with storage-based replication software. The high-level differences below focus on the use of SNAP Mirror replication for relational database replication.
- A first important difference is that a SNAP Mirror for a database creates an all or nothing copy. There is no option to select a subset of schemas or tables within a schema. Rather the entire database is copied. Database replication tools always provide the option to select a subset of the database. Note that the all-or-nothing copy can be beneficial because it does not worry about the data types that are in use, data objects to be replicated, and operations supported for replication, and it should work independently of the underlying database type or version.
- A SNAP Mirror copy does not provide the flexibility to change anything during the replication. I.e. source and target databases are identical, have the same version, and run on the same operating system. SNAP Mirror replication can be extremely fast, but there is no opportunity to perform any logical transformation such as to perform logical deletes or to obfuscate data. HVR and other database replication apply changes to the target database using SQL statements so there is no prerequisite to use the same OS, database version, or even database type (as long as the database type is supported by the technology). Also common scenarios include the introduction of logical deletes and other transformations, as well as consolidation of multiple source databases into one.
- A final major difference is continuity. Relational databases are very resilient to all kinds of issues so a SNAP Mirror can likely be started in a transaction-consistent state. However this requires downtime on the destination system, and it prevents the continuous application of changes to the target database. Also, any changes that are made to the target database will be lost next time the system synchronizes. Logical database replication tools, on the other hand, allow continuous replication and may not require any downtime on the databases to be put in place and start moving changes.
When to use what?
If you see a need for database replication and have the option to consider SNAP Mirror technology (i.e. it is available for your storage system) versus log-based database replication, what should you consider?
If your use case is to duplicate databases for development and/or testing, and no transformation are required, or for disaster recovery, then SNAP Mirror technology can be a great choice. In fact, if the database definitions change frequently and/or you need all definitions in the database including users, roles and privileges, and any programming code then SNAP Mirror technology may well be the far easier solution. If on the other hand, you are looking for continuous replication for scenarios such as real-time reporting, cloud integration, or other scenarios where heterogeneous replication is important, then consider a database replication tool.