Disclaimer: I am not an expert in this area either, but I believe I got these basic things right.
If I understand it right, you're creating a new database connection to serve every user request. This solution won't scale well beyond a handful of users.
Generally, you need to use a properly configured connection pool. The connection pool will facilitate two things: it will reuse connections, reducing the number of connection attempts to the database (creating a connection is quite expensive operation, so keeping a limited amount of connections open and reusing them saves database and server resources). Secondly, it will limit the number of concurrent accesses to the database. The connection pool usually defines some upper limit on the number of connections it will try to open; if a requests comes in a situation when all connections beyond this limit are in use, the application will wait till some connection currently in use will be returned to the pool; it will be immediately provided again to service the pending request. The total limit of connections must be low enough so that the database is not overwhelmed by too many active connections (a quad-core DB server probably cannot handle dozens of connections, for example).
If you've coded your application carefully, changing the datasource from direct connection to connection pool should be quite straightforward, as closing a connection obtained from connection pool simply returns the connection to the pool. You need to restore connections to a known state before closing them: for example, when you close a pooled connection, it is returned to the pool with whatever autocommit state it has at the time. If you rely on autocommit to be
true for new connections, but close some connections with autocommit set to
false, this will be a problem (and a hard one to reproduce!). The same is true with all other states a connection could have, should you rely on them.
If you're using any SQL Server specific methods on your
JDBC objects, this
might be a problem. A pooled object - connection, statement or result set - is enclosed in a "wrapper" object which integrates the original object with the connection pool. The wrapper class is, of course, not the original class and you might have problems casting it to the desired type to call the JDBC driver specific methods. Some connection pools/JDBC drivers handle this situation gracefully (I've used only one connection pool so far and didn't have problems with that, but it's easily imaginable for a connection pool not to behave in a compatible way).
And lastly, but probably most importantly: if your database code has scalability issues, it won't fly. You need to make sure you're using
PreparedStatement and properly binding parameters - this is probably the single most important point. As I don't work with SQL Server, I don't know which other things to take care of, but some best practices should be available in its documentation somewhere.
