how to use MSSQL for JMS

[uttam kumar]

hi,
could anyone please tell me the procedure to use mssql for messaging in JBoss 5. i am using mssql as my datasource. should i replace hsqldb-persistence-service.xml with mssql-persistence-service.xml?

[Peter Johnson]

Which version of JBoss AS, and if you are using 4.2.x, did you install JBoss Messaging and if so which version?

For JBoss AS 5.0.1.GA, the steps were recently discussed at http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4224936#4224936. While that discussion centered on MySQL, the steps for SQL Server should be similar.

[uttam kumar]

Hi Peter,
thanks for your suggestion, it works fine.
One more thing I want to know is that previously there was a file jbossmq-state.xml in %JBOSS_HOME%/server/xxx/conf folder in which we define users and their roles etc. in JBOSS AS 5, which is the corresponding file in which we can do the same?

[Peter Johnson]

Looks like this file was used to provide a means to initialize the database with some users and roles. You can do the same in the mysql-persistence-service.xml file, look at the POPULATE.TABLES.NN entries at the end of the file. Personally, other than for testing, I think that this is the wrong way to populate the database with users and roles.

[uttam kumar]

yes Peter, you are right, I am too thinking that this is not the right way to populate database with users and roles. I search on google but find no way to create users and roles. The only way i can see is through xxx-persistance-service.xml.
if we create users and roles in this way how we can connect a user with a durable topic subscription because i dont know which table is used for which purpose?

[Peter Johnson]

One possibility: populate the tables using SQL statements fed to the mysql utility.

In JBoss in Action, I define a completely separate database to hold the messaging users and use SQL statements to populate that database. Then I set up a database login module that uses that database for authentication, and reference that login module from JMS. Thus that example completely ignores that users and roles tables created by the messaging service. Actually, if I wanted to I could have simply remobed those tables and the SQL statements that use them from the *-persistence-service.xml file, but I didn't.