This has been tested on MSSQL 2000 and MSSQL 2005 (text types are only deprecated in MSSQL 2005; this tutorial won't work when they'll be dropped).
Case-insensitive search does not work with this database; see below for more info. You may want to use the the Lucene search application.

Create the xwiki user and database

  • Use the multi-wiki feature to create a database named "XWiki" and a user named "xwiki"
  • Set xwiki's password to xwiki
  • Give database ownership of the XWiki database to the "xwiki" user

Install the JDBC driver

  • Download the jtds jdbc driver and install the jar file into your server's lib directory (for JBoss this could be server\default\lib\, for Tomcat this might be common\lib\)
    The connection was successfully tested with version 1.2.1 of the driver and version 1.5 of XWiki. Some problems occurred when using drivers > 1.2.1 so it might be better to use the old one.
  • Alternatively, you can use the JDBC driver provided by Microsoft, however this has not been thoroughly tested

XWiki configuration

  • Configure XWiki to use MSSQL. To do this, edit the WEB-INF/hibernate.cfg.xml file. Replace the matching properties with the following ones (or uncomment them if they are present):

JTDS

<property name="connection.url">jdbc:jtds:sqlserver://<server-url>:1433/XWiki;tds=8.0;lastupdatecount=true</property>
<property name="connection.username">xwiki</property>
<property name="connection.password">xwiki</property>
<property name="connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
<property name="connection.pool_size">2</property>
<property name="statement_cache.size">2</property>
<mapping resource="xwiki.mssql.hbm.xml"/>
<mapping resource="feeds.hbm.xml"/>
<mapping resource="activitystream.hbm.xml"/>

MS JDBC driver

Copy sqljdbc.jar from the downloaded package into \webapps\xwiki\WEB-INF\lib\. If you have installed Sun JRE 1.6 (or above) you need to copy sqljdbc4.jar instead!
<property name="connection.url">jdbc:sqlserver://localhost:1433;DatabaseName=XWiki</property>
<property name="connection.username">xwiki</property>
<property name="connection.password">xwiki</property>
<property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="connection.provider_class">com.xpn.xwiki.store.DBCPConnectionProvider</property>
<property name="connection.pool_size">2</property>
<property name="statement_cache.size">2</property>
<mapping resource="xwiki.mssql.hbm.xml"/>
<mapping resource="feeds.hbm.xml"/>
<mapping resource="activitystream.hbm.xml"/>

Troubleshooting

Fix search function

If you try to do a search, via WebSearch, you will get an error. This is because the UPPER() function doesn't work on TEXT or NTEXT as used by MSSQL 2000 for blobs. The only solution I have found is to remove all the calls to UPPER() in WebSearch. This is however not very practical, because it makes search case-sensitive.

Alternatively, you may want to use the Lucene search application instead of the default search.

Fix filter in Livetable Macro

The filter function in the Livetable Macro is not working on MSSQL. The macro function livetable_addColumnToQuery (defined in XWiki.LiveTableResultsMacros) uses in an HQL Statement the function str (Line 576) with an string parameter. This function call is not correctly mapped to the SQL statement.
A solution to fix the problem is to implement an extension of the SQLServerDialect class to be able to overwrite the registration of the STR function through the following one:

public class IWikiSQLServerDialect extends SQLServerDialect
{
   /**
     * constructor
     */

   public WikiSQLServerDialect()
   {
       super();
        registerFunction("str", new SQLFunctionTemplate(StandardBasicTypes.STRING, "cast(?1 as varchar)"));
   }
}

An XWiki extension is available for XWiki 6.2.2 (and later): SQL Server Hibernate Dialect for XWiki

For details see also Issue XWIKI-10606 on Jira.

Hints for upgrading to MS SQL 2005

In MS SQL 2005 the standard schema in all databases is dbo by default. However, if you upgrade your server with the standard Microsoft software, all tables in your xwiki database will be configured to use a schema whichs name is equivalent to the database name. (If your database name is "xwiki", the schema name will also be "xwiki" and the full qualified tablenames will be "xwiki.xwikidoc", and so on).

Since the standard schema is configured to be "dbo", xwiki will not find any tables (since it searches for "dbo.xwikidoc", ...). This will result in an exception when calling the xwiki webapp (in the logfiles you will see an "object not found" - exception for xwikidoc, because xwikidoc is the first table to be mapped via hibernate).

There are 2 possible solutions to this problem:

  • Reconfigure your server to use "xwiki" as the standard scheme inside the xwiki-database
  • Adopt the hibernate file (xwiki.mssql.hbm.xml). Look for the table definitions, eg. table="xwikidoc" and set it to table="xwiki.xwikidoc" or to whatever your schema/database is called. Do this for all the tables you can find in the file.
Tags:
Created by Vincent Massol on 2017/09/06
   

Get Connected