Synchronising subset of database tables between 2 databases on different domains

• zoggling

  SSCrazy

  Points: 2243

  More actions

  October 14, 2022 at 11:42 am

  Go to Answer

  #4099563

  We have a requirement to synchronise data changes from our source database to a client database which sits on a separate domain elsewhere.

  The high-level requirements/constraints are:

  • We are using on-premise SQL Server 2016 Standard Edition for our source instance.
  • The source and target databases are on different domains, separated by the WAN.
  • We want data to be synchronised in near real-time (or as close to as possible).
  • We only want to synchronise data for a subset of tables/columns, and not objects such as stored procedures, functions, triggers, etc.

  In the past, we have achieved this by publishing data changes for a subset of articles to a local database using transactional replication, and then using cross-domain log shipping to feed transaction log updates to a standby database client side via SFTP transfer. Clearly there are lots of moving parts to such a solution.

  In 2022, is there an alternative and simpler way of achieving our requirements, whilst keeping within the constraints that we have?

• Grant Fritchey

  SSC Guru

  Points: 398882

  More actions

  October 14, 2022 at 1:21 pm

  Answer

  #4099610

  To only do a subset of the data, you're very limited. Transactional replication is probably your best bet, but you will need to deal with the separation of the domains, as you did in the past. Advanced, new, stuff like Availability Groups isn't going to help because it does entire databases, not subsets of a database. It's roll your own, buy a third party tool, or rely on replication.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• November 3, 2022 at 6:50 am

  This was removed by the editor as SPAM