Four things to know about SQL Server Replication
Many database administrators assume that a vendor’s native replication solution, like Microsoft SQL Server, will cover all of their needs. If you only intend to replicate data between two Microsoft SQL Server databases, you may ask yourself why you would use a third-party replication tool. This appears to be a valid question, but you should take some things into consideration.
1. If you only have the free edition of SQL Server, you’re missing out
Full replication is only available on SQL Server Enterprise Edition. If you don't run that already then what you may believe to be free functionality actually isn’t. If you need the additional features and are going to invest in a replication solution, you should find out which is best suited to you.
2. You need to be supported in real-time
SQL Server is strict about the starting point for replication. This is so 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. Unfortunately, this is not very helpful when only a single table requires updating. If your database is sizeable then this becomes a major challenge. A third party replication tool, like HVR, allows single table resynchronization quickly and easily
3. You need to be able to rely on your SQL Server system
SQL Server replication is great when it is up and running. When it isn't, it locks the log to prevent it from being truncated. This can be a problem if replication is down for an extended period of time. This can happen if you run SQL Server databases in a distributed environment. The transaction log is only going to grow, and if your system runs out of disk space, it will stop working altogether. HVR does not rely on SQL Server's replication functionality for its log-based capture. It can read transaction file backups as well as the on-line log.
4. SQL Server: It needs to be suited to you
Most out-the-box systems aren’t perfect for everyone’s needs. They need to be flexible and customizable. For example, you may need to perform transformations between a source and a destination system. Or, perhaps you need fewer columns and rows in the destination to serve different purposes. You may even need multiple replication streams from a single database into multiple other databases. HVR can easily set up scenarios like these where SQL Server struggles.
What You Can Do Right Now with SQL server
This touches the surface of scenarios you must consider when deciding if SQL Server is able to support your business needs. There are other areas that HVR covers that SQL Server cannot, like replicating tables with no primary keys. Request a free trial
to see whether HVR is right for your business. At the very least you will have a better understanding of the options out there.