Filtering/partitioning/segment column to support (temporary) segmented processing

[ramonsmits]

Describe the feature.

After a call with a customer they shared their needs to test their active/active where when deploying they transition to an active/passive environment where they deploy a new versions first to the passive node and only deploy to the active side after a test suite is run successful.

The issue is that messages generated on the "passive" side are replicated and processed to the active side.

This can be mitigated by:

• Temporarily disabling bi-directional replication
  • Changing replication on a live database is very complex
• Disabling networking
  • Not an option for the customer as they want the data replicated to the passive node for DR
• Inspecting a header to not process messages originating from a specific side OR by a version that is newer then the processor
  • An option, but relies on retries and round-robin to eventually get messages processed and can cause a lot of latency
• Each side to have its own queueing infrastructure
  • The whole message flow would only be processed on one side and not benefit an active/active environment

It would be interesting if the query that fetching messages could do so with a filter.

SELECT .. WHERE FILTER IS NULL OR FILTER=@FILTER

Now instances at different sides can still scale-out and do competing consumer but could do so as EAST or WEST

In normal operation the filter would always be NULL, but for testing or any reasons to keep message processing in a specific silo the sender/publisher would set a FILTER value.

While something similar could be accomplishes using discriminators (router to specific instance) that only works for commands but not for events.

The idea here is that the the FILTER gets propagated to all outgoing messages until filtering is disabled (thus not propagated) and the consumer will process all messages or the filter would include both EAST and WEST.

Additional Context

If SQLT would support the following, customers could achieve something similar with custom queries:

• Allow customizing SQL queries #1483