I know why you’re here. You’re the other person on the planet that is attempting configure Glassfish 3.x to store all your JMS messages in a Microsoft SQL Server database. The good news is that this is totally doable and should be quite quick (for you :-)!

If you were doing all this on PostgreSQL, you’d have this fantastic quickstart to get you going. Sadly for us, Postgresql is one of the blessed few database that has OpenMQ SQL creation scripts bundled with your Glassfish distro (oracle, mysql, derby and hadb are the others), so things are a bit simpler there. You can read about those on the Oracle config page.

So what will we need to do to get this happening for us?

Well, let’s template off that fabulous quickstart, you’ll first need to:

  • Make sure your SQL Server jdbc driver is deployed to your server somewhere (if you’re using the standard MS SQL driver, you would typically have deployed it to c:\glassfish-3.x\glassfish\domains\domain1\lib\sqljdbc4.jar), and if you’re using integrated security, make sure you have sqljdbc_auth.dll somewhere in the path (typically the same folder).

  • Modify c:\glassfish-3.x\mq\etc\imqenv.conf to add the jdbc driver to the OpenMQ path:

    1
    IMQ_DEFAULT_EXT_JARS=../../glassfish/domains/domain1/lib/sqljdbc4.jar

  • Now we need to do some work to tell OpenMQ all about our new mssql server type, so it works just like one of the built in drivers. We do that magic in c:\glassfish-3.x\mq\lib\props\broker\default.properties. If you have a look in there, you’ll see it defines all the driver settings and table schema creation sql that we’ll need to have in play. Go find the section that relates to postgresql, and we’ll use that as a template. It starts with something like:

    1
    2
    3
    #
    # Beginning of properties to plug in a PostgreSQL 8.1 database
    #

    I’ve basically copied and pasted from that header to the end of that section which ends with:

    1
    # End of properties to plug in a PostgreSQL 8.1 database

    Then I rejigged things to be happy for MS SQL Server (bascially changing the BYTEA fields to VARBINARY(MAX) and setup the drivers/etc, rekeying all the property fields to start with imq.persist.jdbc.mssql). I ended up with something like this which I could add below the postgres section (doing all this in notepad here, it might need some tweaking on your machine):

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    #
    # Beginning of properties to plug in a MS SQL Server database
    #

    # User name used to open database connection. Replace username.
    #imq.persist.jdbc.mssql.user=<username>

    # Optional property to specify whether the database requires a password.
    #imq.persist.jdbc.mssql.needpassword=[true|false]

    # Vendor specific JDBC driver.
    # imq.persist.jdbc.mssql.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
    imq.persist.jdbc.mssql.driver=com.microsoft.sqlserver.jdbc.SQLServerDataSource

    # Vendor specific properties.

    # Vendor specific database url to get a database connection.
    # Replace hostname, port and database in imq.persist.jdbc.mssql.opendburl.
    # imq.persist.jdbc.mssql.opendburl=jdbc:sqlserver://localhost:1433;databaseName=glassfish-jms;integratedSecurity=true;
    imq.brokerid=mssql
    imq.persist.jdbc.mssql.property.serverName=localhost
    imq.persist.jdbc.mssql.property.databaseName=glassfish-jms
    imq.persist.jdbc.mssql.property.integratedSecurity=true

    # Properties to define the tables used by the broker. Do not modify the schema.

    # Version table
    imq.persist.jdbc.mssql.table.MQVER41=\
              CREATE TABLE ${name} (\
                  STORE_VERSION INTEGER NOT NULL,\
                  LOCK_ID VARCHAR(100),\
                  PRIMARY KEY(STORE_VERSION))

    # Configuration change record table
    imq.persist.jdbc.mssql.table.MQCREC41=\
              CREATE TABLE ${name} (\
                  RECORD VARBINARY(MAX) NOT NULL,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(CREATED_TS))

    # Broker table
    imq.persist.jdbc.mssql.table.MQBKR41=\
              CREATE TABLE ${name} (\
                  ID VARCHAR(100) NOT NULL,\
                  URL VARCHAR(100) NOT NULL,\
                  VERSION INTEGER NOT NULL,\
                  STATE INTEGER NOT NULL,\
                  TAKEOVER_BROKER VARCHAR(100),\
                  HEARTBEAT_TS BIGINT,\
                  PRIMARY KEY(ID))

    # Store session table
    imq.persist.jdbc.mssql.table.MQSES41=\
              CREATE TABLE ${name} (\
                  ID BIGINT NOT NULL,\
                  BROKER_ID VARCHAR(100) NOT NULL,\
                  IS_CURRENT INTEGER NOT NULL,\
                  CREATED_BY VARCHAR(100) NOT NULL,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(ID))

    imq.persist.jdbc.mssql.table.MQSES41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (\
                  BROKER_ID)
    imq.persist.jdbc.mssql.table.MQSES41.index.IDX2=\
              CREATE INDEX ${index} ON ${name} (\
                  BROKER_ID, IS_CURRENT)

    # Destination table
    imq.persist.jdbc.mssql.table.MQDST41=\
              CREATE TABLE ${name} (\
                  ID VARCHAR(100) NOT NULL,\
                  DESTINATION VARBINARY(MAX) NOT NULL,\
                  IS_LOCAL INTEGER NOT NULL,\
                  CONNECTION_ID BIGINT,\
                  CONNECTED_TS BIGINT,\
                  STORE_SESSION_ID BIGINT,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(ID))

    imq.persist.jdbc.mssql.table.MQDST41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (\
                  STORE_SESSION_ID)

    # Interest (consumer) table
    imq.persist.jdbc.mssql.table.MQCON41=\
              CREATE TABLE ${name} (\
                  ID BIGINT NOT NULL,\
                  CLIENT_ID VARCHAR(1024),\
                  DURABLE_NAME VARCHAR(1024),\
                  CONSUMER VARBINARY(MAX) NOT NULL,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(ID))

    # Interest list (consumer state) table
    imq.persist.jdbc.mssql.table.MQCONSTATE41=\
              CREATE TABLE ${name} (\
                  MESSAGE_ID VARCHAR(100) NOT NULL,\
                  CONSUMER_ID BIGINT NOT NULL,\
                  STATE INT,\
                  TRANSACTION_ID BIGINT,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(MESSAGE_ID, CONSUMER_ID))

    imq.persist.jdbc.mssql.table.MQCONSTATE41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (\
                  TRANSACTION_ID)

    imq.persist.jdbc.mssql.table.MQCONSTATE41.index.IDX2=\
              CREATE INDEX ${index} ON ${name} (\
                  MESSAGE_ID)

    # Message table
    imq.persist.jdbc.mssql.table.MQMSG41=\
              CREATE TABLE ${name} (\
                  ID VARCHAR(100) NOT NULL,\
                  MESSAGE VARBINARY(MAX) NOT NULL,\
                  MESSAGE_SIZE INTEGER,\
                  STORE_SESSION_ID BIGINT NOT NULL,\
                  DESTINATION_ID VARCHAR(100),\
                  TRANSACTION_ID BIGINT,\
                  CREATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(ID))

    imq.persist.jdbc.mssql.table.MQMSG41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (\
                  STORE_SESSION_ID, DESTINATION_ID)

    # Property table
    imq.persist.jdbc.mssql.table.MQPROP41=\
              CREATE TABLE ${name} (\
                  PROPNAME VARCHAR(100) NOT NULL,\
                  PROPVALUE VARBINARY(MAX),\
                  PRIMARY KEY(PROPNAME))

    # Transaction table
    imq.persist.jdbc.mssql.table.MQTXN41=\
              CREATE TABLE ${name} (\
                  ID BIGINT NOT NULL,\
                  TYPE INTEGER NOT NULL,\
                  STATE INTEGER,\
                  AUTO_ROLLBACK INTEGER NOT NULL,\
                  XID VARCHAR(256),\
                  TXN_STATE VARBINARY(MAX) NOT NULL,\
                  TXN_HOME_BROKER VARBINARY(MAX),\
                  TXN_BROKERS VARBINARY(MAX),\
                  STORE_SESSION_ID BIGINT NOT NULL,\
                  EXPIRED_TS BIGINT NOT NULL,\
                  ACCESSED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(ID))

    imq.persist.jdbc.mssql.table.MQTXN41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (\
                  STORE_SESSION_ID)

    # JMS Bridge TM LogRecord table
    imq.persist.jdbc.mssql.table.MQTMLRJMSBG41=\
              CREATE TABLE ${name} (\
                  XID VARCHAR(256) NOT NULL,\
                  LOG_RECORD VARBINARY(MAX) NOT NULL,\
                  NAME VARCHAR(100) NOT NULL,\
                  BROKER_ID VARCHAR(100) NOT NULL,\
                  CREATED_TS BIGINT NOT NULL,\
                  UPDATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(XID))
    imq.persist.jdbc.mssql.table.MQTMLRJMSBG41.index.IDX1=\
              CREATE INDEX ${index} ON ${name} (BROKER_ID)
    imq.persist.jdbc.mssql.table.MQTMLRJMSBG41.index.IDX2=\
              CREATE INDEX ${index} ON ${name} (NAME)

    # JMS Bridges table
    imq.persist.jdbc.mssql.table.MQJMSBG41=\
              CREATE TABLE ${name} (\
                  NAME VARCHAR(100) NOT NULL,\
                  BROKER_ID VARCHAR(100) NOT NULL,\
                  CREATED_TS BIGINT NOT NULL,\
                  UPDATED_TS BIGINT NOT NULL,\
                  PRIMARY KEY(NAME))

    # End of properties to plug in a MS SQL Server database

Once again, if you need to deep-dive on all of those property settings, there some good info on the Oracle config page (half way down under the JDBC section).

Ok. Now that we have all our properties good to go, the final step is to create the actual MQ setup in the Glassfish admin console.  Going off our template:

  • Head into the Glassfish admin console (normally http://localhost:4848), then go to Configurations->server-config->Java Message Service->
  • Change your JMS Server type from EMBEDDED to LOCAL in the top section.
  • Then enter the following two properties in the bottom section:
1
2
imq.persist.store=jdbc
imq.persist.jdbc.dbVendor=mssql

The first line tell Glassfish that we’re using a JBDC backing for our JMS service, and the second tells it to retrieve all those default settings from our default.properties using the key imq.persist.jdbc.mssql.

If all that went to plan, you should now be able to restart Glassfish and it’ll create the required tables for you in your datasource. You can have a browse with MS SQL Server Admin Console to make sure everything is there to your liking!

Enjoy the Queuing!