What Database Does Spiceworks Use?
The answer to the question ‘what database does spiceworks use’ is not so simple. There is a technical and a logical reason for it. The legacy edition Spiceworks Inventory Online cannot run side by side with its fully supported public cloud counterpart. The reason is that the database would get confused and ticket data may end up in devices table or alerts in users table etc.
DB2
DB2 is a relational database management system (RDBMS) developed by IBM. It is a multi-platform database, meaning it can run on many different operating systems. It is also a highly scalable database, supporting up to 32GB of real memory on some platforms. It is one of the most widely used RDBMS in the world.
Spiceworks uses a SQL backend, which can be accessed with SQL browsers and other tools. This can cause problems because SQL doesn’t differentiate between connections and will mix up data in the DB; ticket data can end up in alerts tables, device information in tickets, etc.
MySQL
IMHO the current SQLite solution is right for most SpiceWorks users (although there are some serious power shops that would like central DB management). MS SQL support does not really add anything beyond what is already available in SpiceWorks and it adds a whole host of costs for things such as a separate physical platform running centralized DB (and thus another licenses Windows server) and a significant performance loss.
It also complicates monitoring because SQLite does not have a “management” system that can be monitored in the same way as MySQL or Oracle or Microsoft SQL Server. i.e. the amount of queries, R/W, memory usage etc.
Additionally, if users are using different methods to connect to the centralized DB (SQL browsers, reporting tools etc…) then the database can become confused and start putting data in the wrong place in SpiceWorks. Ticket data can end up in devices table, alerts in the user table etc.
PostgreSQL
The open-source database system PostgreSQL is a strong choice for complex operations, such as data warehousing and online transaction processing. It is ACID-compliant and implements multiversion concurrency control, which ensures the atomicity, consistency, isolation, and durability of transactions.
PostgreSQL also supports a wide range of data types, including numeric and string values, dates and times, bit strings, text searches, JSON entries, and more. In addition, it offers advanced encryption features such as transparent data encryption (TDE) and always encrypted.
Although PostgreSQL has become more popular in recent years, it still lags behind MySQL in terms of overall adoption. As a result, there aren’t as many third-party tools available to simplify working with the database and few database administrators who have experience managing PostgreSQL. In addition, PostgreSQL can be more difficult to use on some operating systems. For example, it forks a new process for each new client connection and requires a substantial amount of memory.
SQLLite
Spiceworks uses a SQL database, but it doesn’t use an RDBMS. It uses a file system type of database called SQLite. SQLite is similar to MS Access or JetDB. It isn’t as flexible as the other databases mentioned above, but it is fast and easy to use. The problem with SQLite is that it can’t differentiate between different connections to the same DB. So if you connect to the DB using another tool (such as an SQL browser or a reporting tool) while Spiceworks is running, the data can get confused and end up in the wrong places. For example, ticket data can end up in the devices table and alerts in the users table. This can cause odd errors when you try to use the app.
To work around this problem, you need to clear your SQLite connection before opening Spiceworks. You can do this by launching the Query Wizard.