Leaping before looking: Transactional Replication and Temporal Tables
They’re good features, Brent
One of the hottest changes in SQL Server 2016 that really got my shop really excited were Temporal Tables. Previously, we were managing changes by keeping archive tables with triggers on the main tables to track changes over time. Temporal Tables are really neat in that they allow you to query your data at pretty much any point in time. This makes things like auditing and reconciliation on your data really, really easy.
As a feature, it’s pretty simple: when you want to enable it all you have to do is add three additional columns to a table. For example, using AdventureWorks2014, you could enable it like so:
ALTER TABLE [Person].[Address] ADD [SysStartTime] datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysStartTime DEFAULT '1900-01-01 00:00:00', [SysEndTime] datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Inventory_SysEndTime DEFAULT '9999-12-31 23:59:59', PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]) ALTER TABLE [Person].[Address] SET (SYSTEM_VERSIONING = ON);
Simple, right? And it “just works.” Now you can do things like this to query history:
SELECT ProductId, QuantityInStock, QuantityReserved, SysStartTime, SysEndTime FROM dbo.Inventory FOR SYSTEM_TIME AS OF '2016-09-30 06:47:57' ;
We’ll get what the data looked like, for that time period, with this feature you could-
<record scratch>
This post isn’t about setting up and using Temporal Tables. No, it’s about when you do this without realizing you already have a table marked for replication.
Plot twist
Replication is like a super jealous boyfriend/girlfriend at times; it wants to be the only special person in your heart. It sees you flirting with temporal tables and before you know it, replication is keying swear words into your car door and leaving you drunken voice mails, begging you to come back. As far as playing with other SQL Server features, Microsoft is pretty clear on this: in the “Temporal Table Considerations and Limitations” documentation, they spell it out in pretty certain terms:
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.
So there it is: don’t enable it on publishers. Which is fine… except there’s nothing to stop you from doing it. No warnings, other than replication just straight-up breaking. Let’s look at an example.
For this demo, I have three SQL Server instances: SQL Server A is my publisher, B is my Subscriber, and C is my distributor. I already have a table marked for replication (this is the key here), and I’ve done a snapshot to set everything up so that things are moving between the tables on server A and B.
Now let’s get weird: I’m going run the statements above on my publisher and let’s see what happens:
Okay, you’re thinking: wow, I just enabled temporal tables for a table I have marked for replication. SQL Server just treated our statement like any other DDL change. “But wait”, you might think, “I thought this wasn’t supported?” If we look at SQL Server B, we can see the columns are, in fact, there:
All but one: the Period column. And that’s a problem.
All aboard the pain train
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:
If this is a table has been enabled for replication for a while, this is a big problem: how can you get the table back into replication without losing your temporal data? Let’s see: well, we could temporarily turn off system versioning, which means no new changes will be logged but we won’t lose the versioning data. We run this statement:
ALTER TABLE [Person].[Address] SET (SYSTEM_VERSIONING = OFF);
And now we can add the table back. We’re not out of the woods yet though. When the snapshot agent runs, we have new problems.
Issue #1: Getting more than you bargained for
If you’re replicating from a database that is set to compatibility mode 130 (SQL Server 2016), when you generate a snapshot, you’re going to see some errors:
What does that mean? Why are there temporal columns on our subscriber, even though we didn’t set them up? Well, let’s go take a look at our snapshot folder. Inside, you’ll want to look for the .sch file generated for the table in question (in my case, it’s Address_6.sch). Let’s crack it open and have a look:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressLine2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SpatialLocation] [geography] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[SysStartTime] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEndTime] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)
This is interesting! It looks like it copied, verbatim, all of our temporal columns. The code applied fine: it basically created a skeleton of our temporal table with the columns from source. The BCP process to load the snapshot though won’t work, since these values are for GENERATED ALWAYS (and can’t have explicit inserts). We can fix that though by excluding the columns from the article:
Now let’s try generating the snapshot again. This time, we get another error. We excluded our temporal definition columns, but it looks like it’s still trying to apply a PERIOD. Here’s the error, and below that is the schema output from the snapshot:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AddressLine2] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SpatialLocation] [geography] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)
Interesting: so it won’t let us add this period column, and there’s no way to remove it from the article properties. So how do we fix this? Well, you have two options:
- Disable system versioning on the source table, and lose your temporal data in the process. This is the nuclear option, since you can’t modify the precision of the columns since they’re used in the temporal definition. You can’t modify these columns unless you completely discard everything you already have. That doesn’t mean you need to lose all your existing date: you can select out of the temporal table into a new table… and then start over. I doubt your users (or worse, your auditors) would approve of this.
- Trick the snapshot agent into applying a different schema file. If you find yourself in this situation and you NEED to keep everything working, this might be your only option. To make it work, you need to modify the schema file that the snapshot is trying to apply. You’ll need to open the .sch file of the table in question, and comment out the PERIOD FOR line (and the comma above it) and save the file. When the agent retries (and it always retries, FOREVER) it should apply.So if we browse to the snapshot folder, and make our changes like so:
Then it should apply, and SQL Server assumes everything is fine again.
I’m pretty sure it’s because the fixes involve options 1 and 2 here Microsoft says “don’t do this.” You have to chose between losing data or “hacking” replication. Neither of which are good for your health.
Issue #2: Replicating from a non-SQL Server 2016 Compatibility Model
The other issue you might run into if you’re replicating temporal tables is if your published database is running in a a different compatibility mode. For example, let’s say I have AdventureWorks2014 running as mode 120 instead of 130. The good news is that when I run my snapshot, my schema files don’t have the PERIOD FOR column scripted out. But you will still have problems with the GENERATED ALWAYS columns and with BCP trying to write data. Your best bet here is to remove those columns from the article.
Learn from my fail
I wrote this blog post because this exact situation happened to me: we had some tables that had system versioning turned on via a release, and a couple releases later we were required to do a snapshot of the tables. It caught me completely by surprise, and I had to think fast. This is why understanding the different replication models and processes is so important: it led me down a path where I was able to get things flowing again without losing any data. Does this mean you should enable replication on temporal tables? Probably not, if you can help it, since it’s not officially supported.
I feel it could be supported, though, if articles had the option to not replicate the PERIOD FOR columns. After all, non-130 versioned databases don’t do it… why can’t we have the option on version 130 to exclude it?
One last thing: don’t forget, once your snapshot is completed, to re-enable system versioning on your published tables! In fact, you might want to script out all your process and run it in a transaction (disabling versioning, adding the article, kicking off the snapshot, and then enabling versioning) so that you absolutely won’t lose any changes in the process. Your script might look like this:
BEGIN TRANSACTION ALTER TABLE [Person].[Address] SET (SYSTEM_VERSIONING = OFF); -- Adding the transactional articles use [AdventureWorks2014] exec sp_addarticle @publication = N'AdventureWorks2014_TR', @article = N'Address', @source_owner = N'Person', @source_object = N'Address', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Address', @destination_owner = N'Person', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [sp_MSins_PersonAddress]', @del_cmd = N'CALL [sp_MSdel_PersonAddress]', @upd_cmd = N'SCALL [sp_MSupd_PersonAddress]' -- Adding the article's partition column(s) exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'AddressID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'AddressLine1', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'AddressLine2', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'City', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'StateProvinceID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'PostalCode', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'SpatialLocation', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 exec sp_articlecolumn @publication = N'AdventureWorks2014_TR', @article = N'Address', @column = N'ModifiedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 -- Adding the article synchronization object exec sp_articleview @publication = N'AdventureWorks2014_TR', @article = N'Address', @view_name = N'SYNC_Address_1__54', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1 GO ALTER TABLE [Person].[Address] SET (SYSTEM_VERSIONING = ON); COMMIT
And then you can run the snapshot. As always, test before running something like this in production, and by wary of how much of a snapshot you’re going to generate.
So what about you? Are you replicating temporal tables? Or are you… and you don’t know about it yet?
Are you sure?
It’s also worth mentioning that this post just tackles replication of temporal tables from the publisher. What about subscribers? That’s a topic for another day…. thanks for reading!
Pingback: Transactional Replication And Temporal Tables – Curated SQL