All but the simplest application will connect to a database to store and retrieve data. Some applications utilize a proprietary database, but the majority will connect to one of the major commercially available relational database management systems. Oracle, Microsoft’s SQL Server, IBM DB2, and MySQL are some of the most commonly used database products.
As the Application Administrator, you will want to know as much about the database as you can. Some of the details that you want to learn about are listed here.
- What server is the database software running on? It most likely won’t be on the application server.
- What ports are used for communications between the application and database servers?
- Can you access the database independently of the application? What tool will you use?
- Is the design of the database well documented?
- Can you update or refresh statistics on the database tables?
- Can you add, drop, or modify indexes?
- How can you access any error logs that are generated by the database engine?
- Can you access database performance statistics?
- What configuration settings can be tweaked to improve performance? Are they documented?
- What information is available regarding traffic between the DB server and the application server?
- Are tools available to see the actual queries being submitted to the database server?
- Can database traffic be pinned down to specific application users?
- What account actually owns the application’s database? This is frequently referred to as the DBO (DataBase Owner).
- How big is the database currently?
- How much larger can it grow before it runs into problems?
Connection pooling
When a user initiates an activity that requires that data be acquired from the database, the application could establish a connection with the database, request the data, and then close the database connection. This approach is simple, but inefficient because establishing connections with the database is relatively time consuming, it would be more efficient to reuse the connections rather than continually create and destroy them. The concept of reusing connections is called connection pooling.
To determine if your application uses “connection pooling” when it connects to the database, you need to refer to the documentation. Start by checking the Installation Manual or the Administrator’s Guide. If you can’t find anything about connection pooling in any of the manuals, then contact the vendor’s support group.
If the application uses it, you may be able to control the maximum number of connections that can be established and pooled. This setting might be specified when the application is installed. Alternatively, it might be in a configuration file. If you change this setting, then you’ll very likely have to stop and start the application for the change to be put into effect.
Be aware that each connection consumes a certain amount of memory. Setting this value too high can result in wasted memory, but having it set too small can impact performance. If you think this setting is impacting application performance, you’ll need to work with the vendor and your DBAs to identify the number of connections that are needed at any given time.
The database being used by the application also has to have been written to allow connection pooling. The following relational database packages all support pooling:
- IBM DB2
- Microsoft SQL Server
- Oracle
- MySQL
