2

Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Details:
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?

Improve this question
3
  • what type of replication are you using?
    – CR241
    Commented Jan 9, 2019 at 23:53
  • @CR241 Nothing at the moment. I only have the prod db for now.
    – power
    Commented Jan 9, 2019 at 23:54
  • @CR241 We will be using transaction replication because 1. it is supported on Azure SQL Database and 2. we can have no history on the publisher and history on the subscriber, as per David Browne's excellent answer.
    – power
    Commented Jan 10, 2019 at 6:23

2 Answers 2

Reset to default
5

Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Yes. If you use an AG Replica, the primary database and replicas will all have the same Temporal Tables.

If you use Transactional Replication, you can have a Temporal Table at the subscriber, where the table at the publisher is non-temporal.

If you use Change Tracking or Change Data Capture you can use the change data to ship and apply to a temporal table in a reporting database.

See

Usage of replication technologies is limited.

Always On: Fully supported

Change Data Capture and Change Data Tracking: Supported only on the current table

Snapshot and transactional replication: Only supported for a single publisher without temporal being enabled and one subscriber with temporal enabled. In this case, the publisher is used for an OLTP workload while subscriber serves for offloading reporting (including 'AS OF' querying). Use of multiple subscribers is not supported since this scenario may lead to inconsistent temporal data as each of them would depend on the local system clock.

Merge replication: Not supported for temporal tables

Temporal Table Considerations and Limitations

Improve this answer
2
  • David Browne, thank you very much for your excellent answer. I have up-voted it. I have done some additional reading and now I have some further questions. I have updated my question. Could you please answer my part two questions?
    – power
    Commented Jan 10, 2019 at 6:37
  • If you go the Transactional Replication / Temporal Table at the Subscriber route, do you need to add the ValidFrom / ValidTo DATETIME2 columns to the table on the Publisher side too, or replication will be happy enough with the schemas not matching for that table between the Publisher and Subscriber?
    – J.D.
    Commented Aug 15, 2022 at 19:41
2

How much additional load is placed on the "publisher" database when it becomes a publisher?

It depends. The commands for replication are read by the log reader agent from the transaction log on the published database. This means you can cause write performance degradation if your database workload is write-heavy, but you can alleviate this with correct configuration of your log files and underlying disk subsystem as well as configuration of the log reader agent. The only way to determine the load increase is to do load testing.

How can I avoid downtime and locks on the publisher when setting this up?

The initial snapshot will require some locking of the schema to produce the snapshot files for initialising your subscriber, so it is best to schedule these during periods of low usage.

Transactional Replication uses, by default, concurrent snapshot processing, which limits the locks required to allow users to continue using the publisher database during snapshot generation. See the doco here:

Snapshot replication places shared locks on all tables published as part of replication for the duration of snapshot generation. This can prevent updates from being made on the publishing tables. Concurrent snapshot processing, the default with transactional replication, does not hold the share locks in place during the entire snapshot generation, which allows users to continue working uninterrupted while replication creates initial snapshot files.

And lastly:

I have multiple "publisher" databases. Can I have multiple subscriber databases on one Azure SQL Database / Logical Server instance?

Your Azure SQL Database instance can host multiple databases, but be aware that each database incurs costs, so you need to take that into account.

Improve this answer
1
  • Thanks HandyD! I have upvoted your answer as well. (I wish that I could accept both)
    – power
    Commented Jan 11, 2019 at 4:49

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.