I'm looking for guidance on best practices because I need to combine ignition with a different SQL-driven database for a project I'm working on. The database will hold a significant amount of past information that Ignition will need to query and visualise. of is my first experience working with a database of size and complexity, even though I have used Ignition before for simpler applications.
I'm particularly interested in the following:
Performance Optimisation: When working with big datasets, how may Ignition's searches and information retrieval be optimised most efficiently? Are there any Ignition programming approaches or SQL best practices that I ought to be aware of in particular?
Data Synchronisation: In particular, how do you manage data synchronisation with real-time data when working with Ignition and an external database? Do you suggest any Ignition modules or tools for guaranteeing data consistency and integrity?
Security Considerations: When integrating Ignition with an external database, what security procedures are advised? Protecting confidential data and making sure the link is secure are my top priorities.
Scalability: What measures should I take as the project develops to guarantee that the integration will continue to be scalable? Are there any particular Ignition settings or features that work especially well for large-scale salesforce developer deployments?
Common Pitfalls: When associating Ignition with a different database, are there any typical errors or difficulties that I should be aware of and avoid?
From my experience Ignition itself is almost never the bottleneck when it comes to the scalability and performance as long as you have the right hardware for the Ignition Gateway(s)
Typically the scalability issues fall on 4 main factors.
Poor query optimization
Poor Database Design
Poor/No Database Management (MSSQL Maintenance jobs as an example)
Hardware/Settings
Too often we get called in to "fix" people Ignition because they say it is running slow and they almost always have one of those 4 issues, more often than not they at least 2 out of the 4.
With proper database architecting and implementation, Ignition can handle quite a bit of querying no matter what JBDC driver you are using. Occasionally we have to split off into a Gateway network when handling hundreds of machines with tons of data, but that is the situation with any system.
Database size will matter a lot depending on what indexing is setup as well. If you have a deep historian for example, you would want some good indexing to be able to retrieve what is considered high-availability data such as the last 60 days of data (depends on customer needs) and then there is deep historical data which is outside of that high-availability window. Often you can setup your indexing to assist with this and allow for better querying times since there is less lookup to be done.
Are there any Ignition programming approaches or SQL best practices that I ought to be aware of in particular?