Here is a walk-through that I made when I interfaced my Magnolia 3.5.4 instance to an MSSQL Server 2005 database. The walk-through assumes that you are running Magnolia on websphere, but it is still applicable to any other instance. Anyway, hope it helps someone.
1.1 Database instances
Two database instances need to be created respectively for the authoring and public Magnolia application instances.
A dedicated user for both databases also needs to be created. This account will be used by the application instances to connect to the database backend in order to read/write content.
1.2 MS-SQL server JDBC driver
The MS-SQL server JDBC driver jar file needs to be available to the magnolia application (included in the classpath). The JDBC driver may be installed on the target machine(s) running Websphere by uncompressing the driver archive such as sqljdbc_1.2.2828.100_enu.exe(available here) and copying the JDBC driver jar file (sqljdbc.jar) to the magnolia application lib directory eg:
<Websphere_dirs>/magnoliaAuthor.ear/magnoliaAuthor.war/WEB-INF/lib.
2. Configuration
Ensure that the MS- SQL server JDBC driver jar file (sqljdbc.jar) is located in the magnolia application lib directory eg (<websphere_dirs>/magnoliaAuthor.ear/magnoliaAuthor.war/WEB-INF/lib)
Modify Jackrabbit DB persistence manager config to use MS SQLServer 2005 as follows:
- Make a copy of jackrabbit-mysql-search.xml located in magnolia/WEB-INF/config/repo-conf and rename it to jackrabbit-mssql-search.xml.
- Edit jackrabbit-mssql-search.xml and make the following changes in the Persistence manager blocks (NB there are 2 of them!):
Change the class attribute value to org.apache.jackrabbit.core.persistence.bundle.MSSqlPersistenceManager
Change the driver parameter value to: com.microsoft.sqlserver.jdbc.SQLServerDriver
Change the URL parameter value to: jdbc:sqlserver://<database-host>:1433;DatabaseName=<database-name>
Change the schema parameter value to mssql.
Change the user and password parameter values to the values required to database username and password.
Modify magnolia.properties located in magnolia/WEB-INF/config/default. Update the following properties:
Magnolia.repositories.jackrabbit.config to point to the newly created XML file (i.e. jackrabbit-mysql-search.xml).
Side note: If you have previously started the magnolia application using the default Derby database configuration, you may need to back up and rename the repositories folder (magnolia/repositories) and/or manually modify the URL in the default repository configuration file (magnolia/repositories/workspace.xml) to update the URL value to the correct MS SQL Server instance JDBC URL
If you then start up your Magnolia instance it should now create the necessary schemas in your MSSQL database and you should be done.
