0

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?

Improve this question

3 Answers 3

Reset to default
1

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).

Improve this answer
6
  • 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 an option. 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 2019 Enterprise which AFAIK means customers would need to be running SQL 2019 for Avail Groups, Mirroring or Log shipping to work.
    – Simon Brangwin
    Commented Apr 27, 2021 at 4:39
  • @SimonBrangwin Unfortunately most of the data synchronization features are only forward compatible in SQL Server Instance versions as well, so it sounds like you may be out of luck as far as real time synchronization and leveraging out-of-the-box features of SQL Server. You likely will need to find a third party synchronization tool, or develop your own, if you must stay within the bounds of all the constraints you've listed so far.
    – J.D.
    Commented Apr 27, 2021 at 11:24
  • @SimonBrangwin Re-reading your post sparked another idea in my brain, so I updated my answer with some additional information. But yes, I think you are starting to run thin on options, unless you can use one of the options I mentioned to a server outside your DMZ (either on-prem or in Azure) and then provide access or implement another data synchronization feature to flow the data out to your customers. At least you'll be a little less constrained outside your DMZ. I'm assuming you'll still want the databases to be contained once they're on an external server, in such a scenario.
    – J.D.
    Commented Apr 27, 2021 at 11:32
  • Thanks for the suggestions. I've already started looking into Azure Data Factory actually and replicating to Azure as an intermediary point. Another option is Availability Groups. These don't need a Windows Failover Cluster if running SQL Server 2016 or newer which we are. You can setup a read scale AG and replicate to Azure, or to a customer site if we have connectivity. However, this couples our SQL Server to theirs too tightly e.g. have to be same version. Will check out the other options you linked.
    – Simon Brangwin
    Commented May 13, 2021 at 6:02
  • 1
    @JD Clusterless read scale AGs are available in SQL 2017. There's some info here learn.microsoft.com/en-us/sql/database-engine/…
    – Simon Brangwin
    Commented May 13, 2021 at 21:24
1

I used to work for Attunity Now a division of Qlik), and their Replicate product can nicely solve this problem (for a price). Replicate tasks, likely running on a dedicated server, would connect to a source database with customer specific or 'god' credentials and write to a customer specific target database. The tasks can be setup to perform full-load and log based CDC to provide near real-time replication. The target databases need not be SQL Server. They could be Oracle, Snowflake, mySQL - whatever is desired.

Improve this answer
3
  • Appreciate the recommendation but Qlik (Attunity) replicate requires Microsoft CDC which I can't enable due to the constraint of using partially contained databases as per my question. They have a query-based change detection but it likely preform very poorly on our databases which are not optimally indexed and can grow over 200GB.
    – Simon Brangwin
    Commented May 13, 2021 at 5:53
  • Ok, could be. I have not yet read up on partially contained databases. I just wanted to point out that Replicate does not actually use CDC, but the database does have to be enabled for CDC. Replicate needs a (local) publication on the database and adds articles for the replicated tables. This ensures that all change data will end up in the Transaction Log, and that's where Replicate mines the changes. Replicate does NOT use change tables.
    – Hein
    Commented May 13, 2021 at 13:19
  • Understood. But unfortunately, it is not possible to enable CDC for a contained database even if not using the change tables.
    – Simon Brangwin
    Commented May 13, 2021 at 21:17
0

I would suggest to create some kind of manual procedure. You have to develop and deploy in the target database a kind of ETL process made with SSIS packages. With set of packages with detect changes from each tables at the suorce and merge them into the target db.

the work plan could be something like that:

  1. create database snapshot at the source
  2. run the etl process in each target db.
  3. drop the snapshot at the source

It could run every x minutes depending on the time needed to detect and replicate changes. But it is a big task (to develop and maintain)

Improve this answer
1
  • Thanks. Good suggestion to use a snapshot for consistency. Managing an SSIS job might be fine for a small set of tables but our databases have hundreds of tables and grow over 200GB in size and produce 5GB of transactions a day. I want to avoid having to roll our own if possible as we'd need to maintain this at our site and all customer sites for each version of SQL they run. I'm really looking for a built-in capability or COTS product.
    – Simon Brangwin
    Commented May 13, 2021 at 5:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.