Software based database replication

DBMS: Microsoft SQL Server 2014

My company will soon be looking for a new way to replicate some database over to a different server to use for reporting. I know there are many ways to do this, and other people will be looking at some of them.

My specific task is to research software-based database replication product that isn't Quest's ApexSQL Log but works at the same level. Meaning, it reads the transaction logs on the source and recreates the transactions to be loaded into the destination (replicated) database on another server.

Do any of you have any direct experience with a software-based product that can replicate multiple databases and handle both DDL and DDM changes and can work with custom (ie. non-DBO) schemas? Even if it's "Do not use Product ABC because XYZ." It would need to keep the databases in sync, although some lag is expected and OK. Currently, we have a 5-15 minute lag on the sync and that is acceptable.

Thanks,
-BEP

I don't fully understand the nuance of your question, but does SQL Server's built-in support for mirroring not work for your situation?

To my limited knowledge on Microsoft's Mirroring, I don't think it would work in our case. The replicated databases need to be live 24x7 because all of our reporting services will be reading from them instead of our normal databases.

I think in Microsoft's mirroring, the second database is in a stand-by mode waiting to become the live database via failover in case there is an issue with the principal database. Regardless if that's correct, our infra department will be looking at "higher" level replication solutions like Microsoft's Mirroring or Replication services, etc..

The reason I'm asking about software solutions like Apex SQL Log is to help cover all options. We want to check out all possible solutions so we can get together later and compare notes. We are currently running ApexSQL Log, but it has some issues and one glaring bug we pointed out to them that that have told us they aren't going to fix. That bug impacts us quite a bit.

-BEP

If I read this properly, you're looking to have a bunch of secondary replicas of the database available for reading (possibly only reading) by your application. I am not up on SQL Server, but I looked up "sql server active replica" and found https://docs.microsoft.com/en-us/sha.... From that:

Replicas and failover

The primary replica makes the availability databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases.

All replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode.

hope this helps

Thanks, FunkyDuck.

We use Availability Groups on some of our servers, and may go that route with these. Infra will be looking at whether it makes sense in this situation.

-BEP

I was looking through my subbed threads looking for a specific one and saw this old one.

We ended up using Transactional Replication to get our databases replicated to a reporting server. It's easy to set up when not using Availability Groups and just a little more setup to get working with AGs.

We did nothing to try to tune it and we were sitting around 0.5 - 3 seconds of lag on the sync.

I ended up doing all the learning and work for it so I got pretty good at configuring a replication.

Down side is yesterday was my last official day at that job and now I won't be working with Microsoft technologies anymore, except to occasionally support some older code at the new job. So, while it's good information to know, I won't have an opportunity to implement MS Transactional Replication anymore.

-BEP