How to configure mail store to use MS SQL Server?

[lilili]

Would you please give me some examples which show how to configure mail store to use MS SQL Server?

Thanks.

[gohip]

you might, be able to copy the "mssql-ds.xml" from "E:\JBossMailServer\versions\1.0M4\docs\examples\jca" (my path)

to (my path)

"E:\JBossMailServer\versions\1.0M4\server\default\deploy"

there is also, another file in the first dir that I am not sure if you need it or not, but is labeled MS, i.e. "mssql-xa-ds.xml"

then you also need to copy, oh, oops, not there, if you were doing mySQL you'd need "mysql-connector-java-3.1.12-bin.jar" from "E:\JBossMailServer\versions\1.0M4\server\default\lib"

but i dont see one for MS SQL, you might need to go to mysql.org, and find a connector to use.

then I think in this file "persistence.xml" within folder "E:\JBossMailServer\versions\1.0M4\server\default\deploy\mail.ear\mail.par\META-INF" you tweak this line, but dont know what to tweak it too



but alas, all this is probably completely irrelevant, if they (JBoss mail Server authors) havent writeen a hibernate mapping file or whatever for ms sql.

you might try, searching the design forum, and if you cant find anything there, search JBoss forums...

If you figure it out, and dont mind, you might post the steps for the rest of us slackers

good luck!

[acoliver]

Do everything for MySQL except the JDBC driver. copy your MSSQL JDBC driver into server/default/lib. Edit server/default/deploy/mail.ear/mail.par/META-INF/persistence.xml and change the MySQL string to SQLServer. Change the server/default/deploy/*-ds.xml to be similar to $JBCS_HOME/examples/jca/mssql-ds.xml only with the details/connect string for YOUR db server. That SHOULD work. However we have done no testing with it as of yet.

[lilili]

Thanks! Andrew.

It works. I could see tables have been created in SQL Server db. I still get some errors such as
[org.jboss.mail.mailhandler.localmailbox.LocalDelivery] Could not deliver local mail

It's probably due to installation problem.

[gohip]

go on with your bad self then, you could post your steps, if you had any issues, on top of what andrew told yeah to do...

[lilili]

These are steps to configure MS SQL Server for anyone interested:
1. Create a database, jboss in MS SQL Server200 with jboss/jboss as login/password and set jboss as db_owner.

2. Configure JBoss Queue using MS SQL Server according to Topic "MSSQL 2000 with JBOSS 3.2.1 configuration steps" in JBoss Forums. Jdbc driver is "jtds-1.2.jar"

Here is the content of mssql-ds.xml:

<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:jtds:sqlserver://localhost:1433/jboss;tds=8.0;lastupdatecount=true;prepareSQL=0;appName=JBossMQ</connection-url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<user-name>jboss</user-name>
jboss
<min-pool-size>10</min-pool-size>
<max-pool-size>25</max-pool-size>
<idle-timeout-minutes>10</idle-timeout-minutes>

<!-- Validate an existing pooled connection -->
<!-- check-valid-connection-sql>SELECT 1</check-valid-connection-sql -->

<type-mapping>MS SQLSERVER2000</type-mapping>

</local-tx-datasource>



2. Edit /server/default/deploy/mail.ear/mail.par/META-INF/persistence.xml and change "org.hibernate.dialect.MySQLDialect" into "org.hibernate.dialect.SQLServerDialect" according to Andrew.

[gohip]

awesome, your so cool....have you noticed any performance gains, as compared to mySQL, or havent had anytime to benchmark, andrew said postgresql is better than mysql, but wondering whats better when it comes to postgre or ms sql...

again, thanks alot for the steps, and congrats...

[mikezzz]

PostgreSQL will probably still be quicker. We need to do some testing of SQL Server's support for JDBC3 Blobs. With the configuration that lilili is using it will be using the Paged Store which is not as efficient as really good large object support.

Mike.

[gohip]

sweet, thanks mike! hey, any ideas on my post in users forum with regards to the queueing of mail bodies?

the subject is tips on message queues...

here it is...
http://www.jboss.com/index.html?module=bb&op=viewtopic&t=84834

[acoliver]

SQL Server's blobs are MySQL like.