Transactional Replication And Temporal Tables

Transactional replication found Drew Furgiuele’s little black book of T-SQL syntax and went ballistic when it found out Drew was seeing temporal tables on the side:

So let’s say you ran this script (or, maybe someone checked it in as a database change to production). For a while, things are great: you’re making changes to data on your publisher and things are flowing nicely to your subscribers. Sooner or later though, someone’s going to ask you to set up a new subscription (or maybe you need to reinitialize one). Let’s simulate that on my lab: we’re going to remove Person.Address from replication and we’re going to put it back, and then create a snapshot. The key difference here is that now, Person.Address has system versioning turned on. When we try and add the table back to the publication, we’re in for a shock:

This could come back to bite you, so if you use replication and are interested in temporal tables, read this closely.

Related Posts

Using Replication With SQL Server In Containers

Andrew Pruski shows us how we can build up snapshot replication with SQL Server in containers: Last week I saw a thread on twitter about how to get replication setup for SQL Server running in a container. Now I know very little about replication, it’s not an area of SQL that I’ve had a lot […]

Read More

Configuring Snapshot Replication

Nisarg Upadhyay shows us how to configure snapshot replication: On the next screen, configure the SQL Agent security. To configure the Agent security, click the Security Settings button. The Snapshot Agent Security dialog box opens. In the dialog box, provide the account under which the subscriber connects to the publisher. Moreover, provide the account information under which the SQL […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930