Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?
I have a production system, running SQL Server 2014. I want to have a replica database and I also want to track historical changes.
It would be ideal to replicate the production Database to a SQL Server 2016 instance and to track changes with temporal/historical tables.
Is this possible?
Is this possible on Azure?
Edit: Part 2
After David Browne's excellent answer and doing some further reading, I have more direction and more questions. I will be using an Azure SQL Database / Logical Server. I will be using transactional replication because 1. it is supported on Azure SQL Database and 2. we can have temporal tables disabled on the publisher (Sql Server 2014) and temporal tables enabled on the subscriber (Azure SQL Database / Logical Server) As per David Browne's excellent answer.
Part 2 Questions: How much additional load is placed on the "publisher" database when it becomes a publisher?
How can I avoid downtime and locks on the publisher when setting this up?
I have multiple "publisher" databases. Can I have multiple subscriber databases on one Azure SQL Database / Logical Server instance?