9 Replies Latest reply on Jun 23, 2006 2:11 PM by Andrew Oliver

    How to configure mail store to use MS SQL Server?

    Lili Li Newbie

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

      Thanks.

        • 1. Re: How to configure mail store to use MS SQL Server?
          Jay Glass Novice

          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!

          • 2. Re: How to configure mail store to use MS SQL Server?
            Andrew Oliver Master

            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.

            • 3. Re: How to configure mail store to use MS SQL Server?
              Lili Li Newbie

              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.

              • 4. Re: How to configure mail store to use MS SQL Server?
                Jay Glass Novice

                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...

                • 5. Re: How to configure mail store to use MS SQL Server?
                  Lili Li Newbie

                  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.

                  • 6. Re: How to configure mail store to use MS SQL Server?
                    Jay Glass Novice

                    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...

                    • 7. Re: How to configure mail store to use MS SQL Server?
                      Michael Barker Apprentice

                      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.

                      • 8. Re: How to configure mail store to use MS SQL Server?
                        Jay Glass Novice

                        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

                        • 9. Re: How to configure mail store to use MS SQL Server?
                          Andrew Oliver Master

                          SQL Server's blobs are MySQL like.