H2 database is a powerful Java SQL database that is very fast, implements JDBC API, provides an html console, and has a very small footprint, around 2MB.
Because of its size and the SQL compatibility that the H2 database provides, it can be used as an embedded database instead of HSQLDB or other, and of course in the test suite of an application instead of using the DB used in production (for example MySQL, Postgres etc)
The H2 database can connect to any remote or local DB that supports JDBC for checking DB connection, checking or modify data etc. The html console is a very handy tool for all those cases.
Make sure you change the folder location from /home/user/h2 to the actual location in your machine.
The h2 folder will look like this
Next we can start h2. Head to bin folder and use the startup script for your OS (Windows → /bat or for Linux/Mac → .sh)
When the h2 start, the console will start at the console and now we can setup the connection details.
First thing is to pick the proper DB server from the dropdown menu, for MS SQL DB you need to pick Generic MS SQL Server 2005. By setting the server, the Driverclass will be set for you automatically.
The only thing left for you to provide are:
JDBC URL, for example jdbc:sqlserver:/SQLSERVER.company.com\INSTANCEID;databaseName=MyDB
The Login screen will look like this:
Now press Connect and you are in!!!
Bonus, allow web connections from remote hosts
By default H2 database does not allow connections from other machines when starting the H2 Console. So if you deploy and start H2 on a remote server, lets say in a dev environment, you won't be able to reach the H2 Console unless you instruct H2 to allow connections from remote hosts.
Remote access can be enabled using the command line options -webAllowOthers
To use this option run H2 like this
This will allow web connection to H2 Console from remote hosts.