# Multiple databases **URL:** \ **Category:** Ignition\ **Created:** [August 5, 2022, 3:45pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725 "2022-08-05T15:45:26Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 5, 2022, 3:45pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/1 "2022-08-05T15:45:26Z")
If I have 100 machines, the Microsoft SQL database cpu is spiking 75%, and I have multiple projects using both the same database, tag provider, and historian- Then, would I benefit from having a second SQL database for some of the queries? Would it be possible to split my tables in this manner to reduce CPU strain? **I want to learn about scaling up what I can do with queries, and how fast I can do those things.** ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 5, 2022, 3:58pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/2 "2022-08-05T15:58:14Z")
Would help, but would also be a nightmare to maintain. Throw more cores at your current DB. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [August 5, 2022, 5:34pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/3 "2022-08-05T17:34:45Z")
Scaling vertically is far, far, **far** easier than scaling horizontally. Get familiar with profiling SQL server and the diagnostic tools you have available, also. It’s very likely you’re leaving performance on the table in some way(s). ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 5, 2022, 5:56pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/4 "2022-08-05T17:56:27Z")
When you say scaling vertical, you mean adding cores? I want to better understand the options and tradeoffs. What is the nightmare part for more than one database? I thought it was typical to have more than one database. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 5, 2022, 9:20pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/5 "2022-08-05T21:20:42Z")
Have you checked out the server sizing guide? Search the main IA site or this forum, should be a link to it. Multiple databases are common, but they are generally designed for architectural reasons, net performance issues. Say you have two sites, both have Ignition both have their own DB. Site A DB has site A data, and also a second SQL server which is a replication of Site B. Site B has the opposite. Maybe you would have one DB for core Ignition, another for MES and another for ERP. DBA is a full time job. I wouldn’t claim to be one, especially for MS. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 8, 2022, 12:56pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/6 "2022-08-08T12:56:40Z")
Thanks **What is an example of a nightmare situation that people want to avoid with having a second database?** Locations having different databases makes sense to me. I am wondering about situations where **someone wants to use a database in a more traditional way**. **Another person wants to stretch the boundaries** of the realtime data collection and display while not disrupting the core usages. So **I want to learn about the nightmare situations, and tradeoffs. I want to be informed about the tradeoffs, advantages, and disadvantages such that I can take a position for or against different options and that I can guide away from perilous options, or at least be aware of intelligent ways to mitigate those.** edited for streamlining ---
**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\ **Post date:** [August 8, 2022, 1:13pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/7 "2022-08-08T13:13:54Z")
Stupid question but are you using SQL Server **Express**? Some things you should be doing now: - Don’t use star queries ( **SELECT \* FROM table;** ) - Don’t let your tables grow excessively; separate active tables from historical tables. - Call queries from the gateway; not the clients. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 8, 2022, 3:36pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/8 "2022-08-08T15:36:04Z")
I appreciate the help. For the purpose of this thread, I want to learn about having more databases. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 8, 2022, 4:05pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/9 "2022-08-08T16:05:51Z")
More work. With one, you have the default DB, and only have to specify the DB connection based on what scope you’re working in. Potential for error. Writing to the wrong DB, dropping a table on the wrong DB, deleting data on the wrong table. More management, your DB should be backed up and or replicated. Add more DBs’ and your backups and replication needs grow too. I’m trying to be constructive here, I hope it comes across that way - but your OP said DB CPU is spiking 75%. The _ **easiest** _ way to solve that is to throw more CPU/vCPU at that DB server. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 8, 2022, 4:21pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/10 "2022-08-08T16:21:26Z")
Thanks, yes. **Goal** Have one database to really push the limits of. Have one database to use more conservatively. Avoid the two impacting their separate performance. **Concerns** If I understand correctly, potential errors in designating the proper database are the primary concern, deleting and dropping specifically. Second concern is backups and replication. Needs more memory. **Mitigation** Projects can be isolated from using each other’s database while still sharing the same tag provider? I think I should know this, but I am not certain. Feels like walking on ice, unsure of solid foundation or thin ice. Sorry if my question or logic is silly. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 8, 2022, 4:42pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/11 "2022-08-08T16:42:02Z")
Sure, one tag provider, just select on a **per tag basis** in the history option, which DB it is going to. If you use transaction groups, the same thing, you can only select one. Note that, to my knowledge, a named query etc can **only** select from one DB. If you have data A in DB A and data B in DB B, and you want to run a “join”, you have to do this in a script, by running two separate selects, and then appending your own dataSet. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [August 8, 2022, 4:44pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/12 "2022-08-08T16:44:06Z")
Can I block one project from seeing one of the databases? ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 8, 2022, 4:47pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/13 "2022-08-08T16:47:47Z")
Don’t think so, DB connections are global. Willing to be sat corrected if someone else wants to chime in. You can, however, in your DB, give _permissions_ that if you are using that connection (JDBC driver), they can only read/write to those tables, if that helps - don’t think it does because if User A on Project A wants to read/write DB B, if it’s a global connection as above they can go ahead and do so. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [August 8, 2022, 4:51pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/14 "2022-08-08T16:51:17Z")
> [@Using more than one database connection in a project](https://forum.inductiveautomation.com/t/using-more-than-one-database-connection-in-a-project/3372): > > Hi, I am working in a project where I need to consult data from more than one database (Postgres and MySQL). I am not using tags because I need to change the text of the queries dinamically, so I am writing the queries as text in some invisible labels, and making my charts execute those texts as SQL queries. I have several connections defined in the gateway configuration (and they really work). The problem is that in the “Projects” menu I can choose the default database for the project, but I c… > [@Restricting access to specific projects](https://forum.inductiveautomation.com/t/restricting-access-to-specific-projects/22153): > > Hi, I have a couple of different projects on our gateway. Is it possible to make the projects visible depending on the user who is logging in both on the gateway and in the designer? The motivation behind this is to have the client log in to our gateway and open their project from there in order to preview it and we can present it to them like that. I am obviously not interested in showing the client the other projects we are working on. Is such an restriction possible in Ignition? I have pl… ---
**Author:** ![tom.leijen](https://avatars.discourse-cdn.com/v4/letter/t/e495f1/32.png) [@tom.leijen](https://forum.inductiveautomation.com/u/tom.leijen)\ **Post date:** [August 11, 2022, 10:12pm UTC](https://forum.inductiveautomation.com/t/multiple-databases/62725/15 "2022-08-11T22:12:52Z")
If you are looking to push databases to their boundaries, have you considered using a cloud-based database service like Azure - they allow massive scaling, load balancing etc with less administration overhead than running on-prem DBs (cloud based servers handle backups and guarantee uptime and can be configured to scale automatically), you could then also take things even further by adding cloud based data analytics and presentation, possibly even AI systems (predictive maintenance for your plant etc). If your budget doesn’t allow for the level of cost associated with a cloud-based database, I would suggest running two separate database servers (on different VMs or different hardware), one to push the limits of and another to use more conservatively. As already mentioned in this thread, there is a LOT to be gained from setting up your DB structure right and writing efficient queries. Think about things like, how much detail is an operator really needing to visualise and how often would this need to be refreshed, how quickly does your data really change, what level of precision in data do you need to capture etc etc. For example: - Temperature data is slow to change, rather than querying your database every 100ms, or for every 0.1 degree change, you could take an average every 5 minutes and only query the database every 5 minutes to insert the data. - When you have a graphic on a page to show some data from your database, you only need to refresh it when someone is looking at it, you could write some scripts to only query the database when the graphic is visible, or caching data by running a query that appends to a local dataset object, so that you only ever query new data rather than continuously querying the full dataset from the database.