Is partial synchronisation possible between two databases on different servers?

Question

Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups.

My question is whether I could add additional objects to the secondary instance - in particular a materialised (indexed) view - without needing that view to synchronise back to the primary.

Is this possible?

("Why?", I hear you ask? Because I don't want the performance hit of maintaining the view to impact the primary instance. It would be find for the secondary to use async-commit mode, if needed.)

Answer 1

The only built-in data synchronization technology that meets your requirements, I think, is Transactional Replication.

See the FAQ on replication:

How do I add or change indexes on publication and subscription databases?

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

This means you can have different objects (indexes, views, etc) on each replica.

Answer 2

This is possible if your synchronization methodology is via the Replication features of SQL Server, such as with Transactional Replication.

One minor limitation though is any schema bound objects (such as a Materialized View or a Row-Level Security Policy) need to be dropped whenever a new snapshot is generated or when the schema of one of those bound objects changes. For the former case, I normally handle it with pre and post replication scripts, for the latter case, I rarely have schema bound objects whose schemas change, but should it happen once in a blue moon, I just manually drop and recreate the schema binding object.

With Availability Groups, this is not possible - all changes to the primary database are automatically made to the secondary database. And changes can't be made to the secondary database directly, because it is read-only.

Other benefits of Replication over Availability Groups is you can granularly choose which objects you want to synchronize from the Publisher Database to the Subscriber Database, as opposed to synchronizing the entire database like you are required to with Availability Groups.

There are also other synchronization features in SQL Server that would likely allow you to create schema binding objects on the destination database without needing to create them on the source database, like SSIS. But there are pros and cons to using this feature vs the other ones as well, such as it may be a little more involved to initially setup, and can be a little clunkier to emulate near-realtime synchronization like Availability Groups and Replication can.