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?
Best Answer
Interesting problem to solve. I don't have a lot of familiarity with contained databases but after some quick research I understand their general purpose and the challenges you're facing.
The few ideas that come to mind are possibly looking into Log Shipping or Database Mirroring. You might be able to schedule more frequent data synchronizations with one of these methodologies than routinely sending a BACPAC file.
Finally, it looks like that Contained Databases do support Availability Groups but I'm not sure if that'll be of much help since AlwaysOn Availability Groups require a Windows Failover Cluster to be setup between the servers of the Availability Group, which I'm assuming is not conducive to your business scenario.
After some more brainstorming, research, and re-reading what you wrote, there may be a way to leverage a cloud provider like Azure, as you mentioned. This DBA.StackExchange answer gives a few options for moving data into an Azure instance. Additionally it reminded me of SSIS which you should be able to leverage to synchronize data from your instance within your DMZ to an external server (whether that's in Azure or on-prem).