January 19, 2022 at 3:53 am
Hi,
We have just incorporated 2017 Transactional Replication into our product for Archiving purposes. Schema updates are enabled. On each product update we run a set of Db Schema migrations (via application-hosted Fluent Migrator SDK) on the Publisher database.
However, Transnational Replication does not support all schema changes. For example, adding Identity columns to a table. There's also a bit of jumping through hoops to update published articles programmatically (e.g. on Table rename, new article, etc) followed by re-running a snapshot (albeit I believe we can do this with an update to changed/new table only). Some of our clients have > 2TB databases so this is not a great option.
I'm interested to hear how others might have addressed this problem. That is, how replication configuration was programmatically updated following publisher schema updates resulting from their application product updates.
Thanks
Brian
Version: Ms Sql Server 2017
January 19, 2022 at 6:17 am
When an identity column is created on a published table, look at the generated code. It will include the NOT FOR REPLICATION clause.
You do not want the column to be an identity on the subscriber. Remember, the data is being inserted into the subscriber in the same manner as the publisher. If the column was also an identity, it will create a new identity with each insert. You can modify the insert procs that are created with replication to contain SET IDENTITY_INSERT ON.
I suspect you are making schema changes that, by default, should not be replicated.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply