Local installation database requirements
When Crash Magic is installed on a client server, the client database must provide storage for crash data and Crash Magic system tables. This article describes these tables and required login information.
Crash Magic requires two databases with specific logins:
- Crash database. This database contains your crash data. Database size is dependent on your crash data structure and record counts. It is not influenced by the Crash Magic application.
- Crash Magic requires a login with read-only access to this database. It will be used by Crash Magic exclusively for querying with SELECT statements.
- In some cases, Crash Magic will be required to import into or allow data entry access to this database. In this latter case, the program will require a login with SELECT, UPDATE and DELETE access. A separate login is suggested for this purpose.
- System database. This database is for managing the Crash Magic application state. System tables are used by the program to store system state, user settings, configuration, reports, etc. For most users this database will consume between 300 and 800MB in an MS SQL Server. In our production system with dozens of agencies and hundreds of clients per system database, the size is about 4GB. Depending on your backup schedule and maintenance, the log files will also consume space.
- Crash Magic requires a login with SELECT, UPDATE and DELETE access to these tables.
- When provided with appropriate credentials, Cash Magic includes a maintenance mode interface that can create and/or update the system tables. To utilize this feature, a login is required with DDL (CREATE, UPDATE, DROP tables, indexes, columns, etc) permissions to the system database. This login is not stored by Crash Magic, but is utilized during the install/upgrade process. If Pd’ Programming is installing / upgrading your Crash Magic instances, it is strongly suggested that this login be provided to them to facilitate maintenance of the system.
The following information is required prior to installation of Crash Magic. Suggested names do not take into account possible desire for different database instances such as Test, QA, Prod, etc. Modify as required. Maintaining the pattern shown will make it easier for Pd’ Programming to record and access logins for each agency.
- Connection information for the crash database and for the system database. While these can be the same database, best practices suggest that they be in different databases. (or Oracle “schemas”)
- For MS SQL Server, this will be the server name or IP address, the instance (if any) and the database name.
- For Oracle, this will generally be the TNS Name.
- Login account information for the crash database
- read-only account. Suggested name: CM__Reader. (e.g. CM_MDDOT_Reader, CM_AZMesa_Reader, etc.)
- read-write account. (optional) Suggested name: CM__Writer. (e.g. CM_MDDOT_Writer, CM_AZMesa_Writer, etc.)
- Login account information for the system database
- read-write account. Suggested name: cmSys (e.g. cmSysMDDOT, cmSysAZMesa, etc.)
- DDL account. Suggested name: cmSysDDL (e.g. cmSysMDDOTDDL, cmSysAZMesaDDL, etc.)
- Important 1: It must be possible to create a query that joins crash database tables to system database tables. For Microsoft, this means that the databases must exist in the same instance. For Oracle, this means they must be in the same database, but being in different schemas is okay.
- Important 2: The crash database read-only account must also have read-only access to the cmSys database cmCID table.