History Redundancy with MSSQL Server using Transactional Replication

General Discussion
1

I am using a MSSQL database to store my tag history. This database sync its data with another MSSQL database using transactional replication.

Ignition is setup to for monthly partitioning of its tag history data to the MSSQL DB.

The problem I am having is, once I do the initial setup of the Distribution, Publication and Subscription, the Articles (DB tables I wish to sync) are static. Every month, a new table is created in the database but the Publication initially created does not contain the new table (Article).

The new article can be added to the subscription manually using Management Studio but that is not an option.

Removing tag history partitioning would prevent this issue but having a single table for tag history will cause problems later down with performance.

Is there a way to automate the process of adding the new tag history table in the database to an existing publication or possibly even create a new publication and subscription containing that new table automatically with no user intervention?

2

What's the database storing, if it is only tag history you're worried about it sounds like setting up a tag hisotry splitter would do this job.
Essentially it will tell Ignition to save any tag history in two places at the same time.

2 Likes
3

Oh this is pretty interesting.

Yes the database is only storing tag history. The tag history splitter may work instead of trying to synchronize two separate databases.

How would it handle fail over?

eg. I have 2 database connections: DB1 and DB2. Each database connection references each other as the failover.

DB1 ----Failover----> DB2
DB2 ----Failover----> DB1

With the history splitter config, I used DB1 as the first connection and DB2 as the second. Will failover be based on the DB1 and DB2 individual database connection or the primary to secondary connection specified on the history splitter?