Impact of database server issues
Many issues can impact the proper functionality of the back-end database, and that’s exactly the source of the majority of the product reported issues and outages.
Database server resources The database server has to be correctly provisioned and its requirements as specified by Microsoft have to be met. There are some notes about what can affect application services in the next section, and for the database, the important resources are RAM size and speed, single-CPU speed and disk I/O.
Free disk space available has to be 1.5 times the size of the database files. Even more space is needed on the disk where the database’s tempdb files are stored, depending on the frequency of maintenance. During auto discovery, several large tables are completely replaced, which leads to large transactions, with tempdb files growing and shrinking very fast.
Database configuration Follow procedures as detailed in Installing database and database client software . If your database administrator does not agree with the settings, check with support@bravurasecurity.com on what impact any deviation from the listed configuration may have.
Database-user mapping If the database Login which is configured for use by the Bravura Security Fabric does not have enough privileges or even has sysadmin role granted, it can fail to run stored procedures in the correct context, or not even find them. The correct settings are also listed in Installing database and database client software.
Database maintenance This is important, especially after the count of table row passes the one million mark. Maintenance like reindexing, defragmenting, cleanup of obsolete historical data, and so on, can double or triple performance. Such maintenance has to be scheduled outside of working hours for most users, and also outside of application-scheduled tasks which change the database drastically, like
psupdate,idtrack,autores, and so on.Ideally, the Database Service (
iddb) on all the application nodes that use a database where maintenance is performed, should be stopped and disabled in order to prevent table and cross-sproc locking, and to avoid changing indexing while an already-calculated complex sproc execution plan is already running.It is also recommended to wait for the replication queues to empty after database maintenance completes and the affected application nodes are brought back online, before taking down another database node for maintenance.
Database schema and stored procedures These are also very important. They are designed with existing use-cases in mind, and as time and versions go by, both are improved and optimized for the types of loads and data patterns our clients require. The MSSQL Activity Monitor tends to point to "missing indexes" or "better index configuration" whenever long-running sprocs are involved. However, in most cases those "solutions" would increase the database size considerably.