Should I define datasources in application or in app server?

Question

I've developed applications (running on Jboss server) with two different teams. One team had datasource configuration inside of the application WAR file and another one had it inside of the application server's standalone.xml. And I'm not sure which approach is better.

So, here are some advantages that I've found in defining datasource inside of the server's standalone.xml.

• Defining datasource in server's standalone.xml is more secure than in war file. If the database connection credentials stored in the server's standalone.xml, which is almost never modified, it is safer than having the password inside of the war file, which is often transferred from developer's machines to the server and database configuration is spread by all developers computers.
• By having datasource in standalone.xml, we can make war file smaller, as JDBC driver could be installed as a module and can be removed from war file. In addition, loading JDBC as module is more efficient as from classpath.
• We can put datasource inside of the standalone.xml if we don't want the application development team be aware of database connection settings.

By having datasource configurations in application WAR file, I see the following advantages, which are:

• Development team doesn't have permission to change Jboss configuration files in environment where Jboss is running. So DB connection only can be defined in application.
• It is useful in development state, when developers often need to switch between different database connections. For example, the developer can specify connection when building a WAR file.

So I'd like to know if there are another advantages in both approaches. And on your opinion which approach is better?

Answer 1

For me, the number one benefit of having all data-source configuration out of the war file is ease of deployment.

If I read your question correctly, you can't ever deploy the exact same build to multiple environments if you include any configuration in the build. A direct implication is you can never deploy you DEV build to QA and more importantly, you can't deploy your QA build to PROD or UAT. This might cause headaches if your process is audited.

If I mis-understood your question, please let me know, otherwise, I hope this helps.

Answer 2

In order to properly verify your application works, you should try in a staging server before sending it to production.

In that scenario, the war file you install into production, should be the same that you tested, so you shouldn't need to change anything for te application to work in a different environment with different database connections.

So, the database configuration shouldn't be in the war file but in the application server. Additionally, you make the system administrator's live easier because they don't need to manipulate (uncompress and change) your war to install it in a server.

In the very early development live of the application, it can be useful to add the database (and any other development configuration) to reduce the time a developer can put his/her hands on the project and starts programming without having to configure the application in a development application server.

Answer 3

I favour having the datasource exposed by the application server with a caveat.. You need your development team to at least know their way around with your application server or to have at least access to the jboss console to review the configuration or change it.. Reason being that for example they need to monitor the connection usage of the datasource connection pool.. Since you're speaking about jboss, I don't know if the "live" bean for the datasource with jboss AS exposes the same information natively of for example oracle ucp (ucp .getStatistics is a godSend for more than one reason..).

Consider that EVEN if you internalize the datasource in xml, using the concept of profiles you can make some field of the xml being "filled" with some specific value in one or another property file based on the profile the application is loaded with..

e.g with spring you can surely do

    <beans profile="local">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuser_DCI"/>
        <property name="password" value="mypassword_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <orcl:pooling-datasource id="dataAltSource"
        url="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA" username="some_OWN" password="some_OWN"/>
        <util:properties id="flyway.prop" location="classpath:config_local.properties"/>
</beans>

meaning on local profile load the properties from the config_loca.properties file inside the classpath

and have as well

    <beans profile="qa">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_DCI"/>
        <property name="password" value="myuserQA_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <bean id="dataAltSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_OWN"/>
        <property name="password" value="myuserQA_OWN"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
    </bean>     
    <util:properties id="flyway.prop" location="file:///${prefix.iam.dir}/${filecert.iam.path}/external.properties"/>
</beans>

thus in your QA environment or other non-dev environment you instead refer to the external xml file and not to the one integrated in the war.. You can even include username and password to be "filled" via the internal//external properties file to enhance the security if you're concerned.

Answer 4

In addition to the points noted in the question, another advantage in having the datasources outside the application is that it allows for using the same war file in different regions. This would allow the team to have different databases for different environments, like Test, Perf and Prod while using the same war file.

You can do a deployment once and then the war file which has been tested by your QA team can be promoted to the production region. This would make sure that no untested code goes into higher regions while avoiding the need for SCM forks and code freezes.