We host a number of customer SQL Server databases that we need to replicate to external customer sites in near real-time. The main constraint we have is that, for security reasons, our databases are partially contained to ensure customer A can not see customer B's database.
Depending on how big the customer is, the databases range from 5GB to 200GB in size. There are usually up to 5GB of changes on any one day.
Other constraints are that our SQL servers are secured behind firewalls and DMZs. We don't want to expose them directly to customers. A proxy we host in our DMZ may be a workaround. In addition, Availability Groups, Mirroring and Log shipping have version compatibility constraints that would lead to tight coupling which would be undesirable with a single customer, let alone across multiple customers that have potentially a wide variety of SQL versions. We are running SQL Server 2019 Enterprise which AFAIK means customers would need to be running SQL Server 2019 for Availability Groups, Mirroring or Log shipping compatibility.
Options I've considered so far
- Change Data Capture (CDC). SQL Server 2019 does not support CDC with contained databases.
- Table replication. SQL Server 2019 does not support table replication with contained databases.
- Triggers. Triggers are synchronous and can have unwanted side effects such as slowing down transactions and failing transactions if replication fails.
- 3rd party software HVR Software and Qlik replication. These both rely on initially enabling CDC to add "supplemental logging" to the log stream. They do not actually rely on CDC for replication but need the additional metadata in the SQL log files that requires enabling CDC. Due to CDC not being supported with contained databases this rules out these options too.
- Sending a copy of the complete database (in compressed BACPAC format via SFTP) to the customer on a schedule. The databases are then restored on the customer site. This complete process takes approximately 45 minutes on a fast internet connection so we only schedule this overnight at present. It can't satisfy the near real-time requirement.
- Replicating each customer database to an Azure SQL database and opening this up for the customer to access. Is this even viable, can customers subscribe to changes from Azure SQL and how real-time would the data be? What costs are involved?
Has anyone dealt with such a scenario and how did you manage to solve it? What other options are there for replicating databases?