TomEE in the cloud with Azure and Azure SQL DB

This blog post shows how to deploy the FaceID project on +Windows Azure. We will use +Apache TomEE, Ubuntu 12.04 and the Windows Azure SQL Database.

We are going to:

• Prepare a Linux box on Azure
• Install Java
• Install TomEE 1.6 Snapshot
• Install our FaceID application
• Create an Azure SQL Database
• Install MS SQL Server JDBC driver
• Link the FaceID application and the Azure SQL Database
• Deal with the clustered index issue
• Have fun! 

Build the VM and install FaceID

First, create your virtual machine on Azure. This is the official tutorial: http://www.windowsazure.com/en-us/manage/linux/tutorials/virtual-machine-from-gallery/. Once you have it running, open a ssh session (Windows users can use putty) and upgrade your server.

After restarting your virtual machine, install java.

Some of the features used by the FaceID project are available on TomEE 1.6 Snapshot (and older). The latest snapshot versions of the server are available here. Go ahead and download TomEE Plus 1.6 Snapshot.

Unzip the distribution file.

Start your TomEE server.

You can also tail the log file.

Your TomEE server is now running but it is still not visible from the outside world. You will need to open the port 80. You can map the public port 80 to the private port 8080. The official endpoints how-to tutorial is available here: http://www.windowsazure.com/en-us/manage/windows/how-to-guides/setup-endpoints/.

After creating the endpoints, you will be able to access your server.

It is time to install the FaceID application on it. You can build it yourself (see previous blog posts) or you can download this pre-built war file: http://people.apache.org/~tveronezi/posts/20130514-azuredb/faceid.war.

Move the war file to the webapps directory.

If you access the faceid (in the case above it would be http://tomee-on-azure.cloudapp.net/faceid/) application now, you should see the popup security window.

We have no user for this application yet. Shutdown your server and update the /apache-tomee-plus-1.6.0-SNAPSHOT/conf/ tomcat-users.xml file. You should create an user with the solution-admin role.

 <?xml version='1.0' encoding='utf-8'?>  
 <tomcat-users>  
  <role rolename="solution-admin" />  
  <user username="myadmin" password="mypassword" roles="solution-admin" />  
 </tomcat-users>  

After restarting TomEE, you will be able to access your application.

Create and link an Azure SQL Database

The official how-to tutorial on how to use an Azure SQL Database with java is available here: http://www.windowsazure.com/en-us/develop/java/how-to-guides/using-sql-azure-in-java/.

Create your database.

Download Microsoft JDBC Driver 4.0 for SQL Server (http://www.microsoft.com/en-us/download/details.aspx?id=11774) and upload it to your virtual machine.

Uploading file with filezilla

Extract the file.

Copy the driver to the lib directory.

The FaceID points to a datasource called userJTA. If you don't define this datasource, TomEE will do it for you. The generated datasources use the default hsqldb database. In order to use Windows Azure SQL Database, you need to specify the datasource yourself. Edit the conf/tomee.xml file.

Replace the content of this file by...

 <?xml version="1.0" encoding="UTF-8"?>  
 <tomee>  
  <Resource id="userJTA" type="javax.sql.DataSource">  
 JdbcDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver  
 JdbcUrl=jdbc:sqlserver://s9oprf6lip.database.windows.net:1433;database=tomee-azure;encrypt=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;  
 UserName=tomee  
 Password={your password here}  
  </Resource>  
 </tomee>  

Restart your server. The application is connected to the Azure DB server, but you cannot persist data yet. As soon as you try to create a new user, TomEE throws an exception.

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. {prepstmnt 221417245 INSERT INTO User_securityGroups (USER_UID, element) VALUES (?, ?) [params=?, ?]} [code=40054, state=S0001]  

Go to the Azure Console and connect to your database.

Create the clustered index for the table that Azure is complaining about.

 CREATE CLUSTERED INDEX User_securityGroups_Index ON User_securityGroups (USER_UID)  

If you try to use your application again, you should be able to persist data on it.

Read more about Datasource Configuration: http://tomee.apache.org/datasource-config.html.
FaceID source code: https://github.com/tveronezi/faceid
Apache TomEE: http://tomee.apache.org/

The Apache TomEE project always welcomes contributions. Join the fun!

[]s!