I have a project with lots of transaction groups for storing history data. It is storing history to a SQL server but the facility wants to install another SQL on another server as a backup. Since they are transaction groups, Tag Splitter cannot be used to save the data to both databases. Therefore, we will have to duplicate the transaction groups to store the same data to the other database. The question I have is whether there is a way on the client to detect which database connection is active in the case of a database failure and automatically switch to the active database connection. There doesn’t appear to be a way to do this built into Ignition. I am thinking I would have to go into every Table, Easy Chart, etc and use some indirection in the DATASOURCE based on a script that detects if a database connection is active.
They want a fully redundant historian with automatic switchover.
If the data is more important to you, then why not find a SQL HA solution and handle data replication at that level? Trying to make Ignition handle it seems the wrong way to do it.
Data sources already have a setting for failover to an alternate datasource. That might fit your needs. But I highly recommend @Paullys50’s solution – use an HA database cluster. That’s what my IT-savvy customers do.
Like mentioned in the other post: sql replication is a standard feature availible to all sql databases and the set up process for replication are all over the internet. Whats nice about the replication is that you can point all of your reporting to the replicated database. I once wrote a query so bad that it crashed the db server. At that time I was using the failover source feature and Ignition applied the query that crashed the main server to the replicated server resulting in two crashed sql databases. As mentioned before in the other post: A transaction group to SF to a replicated database covers 99.9% of your goals. Sending your questionable querys to the replicated database removes a liability that is often overlooked.
I once wrote a script so bad it destroyed the hardware of a server.
I would say to go ahead and not try keeping two SQL servers in sync by duplicating the transaction groups. Even if your system worked, what would happen if one transaction failed one time for one SQL server and then the two database were inconsistent? Either go for a pre-canned SQL replication solution or possibly write a script to read, compare and sync the databases.
We would like to have two servers in a SQL Server failover cluster instance, then use a Network Area Storage (NAS) device for shared storage between the two servers. Can we still have the Ignition Main and Backup gateways on the same servers as the database servers? That’s what we’re doing now only we’re not using a failover cluster instance.
I don’t put databases on the same servers as Ignition. Redundant or not. Possibly in separate VMs of one hypervisor, if the hypervisor is not overcommitted.