We’re running the ‘mission critical’ setup here and recently lost our primary server. This would not be a problem except that our database (SQL Server) also resides on this server so clients were without trending or database access through out the duration of the outage.
I was wondering how others here on the forums have set up replication for their databases. I have set up replication before but this is one directional only and as I remember was table specific. I’d like to have a true failover datasource that can be logged to if the primary goes down and will merge its information back to the primary when the primary is restored. (and will provide trending and historical information seamlessly to Ignition clients while the primary is down)
How have people configured their databases, what changes if any had to be made in order to accomplish this and how did you cope with Ignition’s changing table names on the sqltags historian related tables?
I only have two servers so I’m wondering if mirroring is the way to go? http://msdn.microsoft.com/en-us/library/ms189852 I want to have ignition reading and writing to the backup database while the primary is down.
It appears from the MSDN documentation that the databases will automatically determine which is the principal and redirect any client connections to the current principal. Is this correct? http://msdn.microsoft.com/en-us/library/ms175484
In this case do I need to specify the failover datasource in Ignition or just leave it as is and let the JDBC driver figure out which is the primary and which is the failover datasource?
Do you need to to make it a high safety mode to enable automatic failover or can I make it high performance? (I am not worried about losing a few data entries). The link below seems to indicate this, but I am concerned about overhead. Does using high performance mode prevent logging to one of the databases when the other is down? http://msdn.microsoft.com/en-us/library/ms189850.aspx
Yes, I believe that for SQL Server, Mirroring is the feature you want. We have customers using this feature with Ignition with success - it seems to work well.
You should really be talking to Microsoft about the details of setting it up though - that’s beyond our scope of expertise.
As far as Ignition goes, I’ve seen it both ways: 2 connections set up in Ignition with failover (one to primary, one to mirror backup) and the connection to the backup is always faulted until the primary fails. But I’ve also heard there is a way to do it with 1 connection to a virtual server that manages the failover. Again - consult Microsoft for the details.
Sorry to bring back an old thread, but I think this is important to note that for Ignition to leverage MS SQL Mirroring, the connection string (Connect URL) needs to be modified to include the failover partner by modifying the string as so. Be sure to use the fully qualified server hostname and you also MUST specify the InstanceName. Don’t use the default ‘localhost’ for host.
Note that the Username you specify in the connection must exist on both servers. And even more critical…the Username MUST HAVE THE SAME SID. You can do this by going to your primary server and getting the SID of the login:
SELECT name, sid from sys.sql_logins WHERE name = ‘YourDBLogin’
Then on the secondary server, create the user (if you already created it beforehand, you’ll have to delete that one). Don’t worry about mapping this new login as that will be done automatically when the primary fails over…besides, you can’t do this because the databases are in NORECOVERY mode.
CREATE Login YourDBLoginWithoutQuotes WITH password = ‘SamePasswordAsPrimary’, SID = SIDhexValueReturnedByPreviousQueryWithoutQuotes
Now you only have to maintain 1 DB connection in the Ignition Gateway and when a failover occurs on SQL Server, the connection stays valid and doesn’t even report an error in the logs. This also works when Ignition is set up with Redundancy as both Primary and Secondary SQL servers are mentioned in the connection string.