Steps

To configure XWiki to use Microsoft SQL Server on a database server you administer:

1. Make sure you have already installed and configured a servlet container for XWiki (e.g. Tomcat or Jetty), with the XWiki WAR file extracted/deployed.
2. Connect to the SQL Server instance as an administrator, with sqlcmd or SQL Server Management Studio, and create the database and the login XWiki will connect with. The steps below use xwiki as the password to keep them readable; pick a real one for anything but a test instance.

   CREATE DATABASE xwiki;
   GO
   CREATE LOGIN xwiki WITH PASSWORD = 'xwiki', DEFAULT_DATABASE = xwiki;
   GO

3. Make that login the owner of the database, which is what lets XWiki create its tables and the schema below.

   ALTER AUTHORIZATION ON DATABASE::xwiki TO xwiki;
   GO

4. Create a schema named after the database. XWiki qualifies the tables it maps with the name of the wiki's database, which SQL Server reads as a schema, and Hibernate creates tables but never a schema; skipping this stops the first start-up with an "Error executing DDL" failure.

   USE xwiki;
   GO
   CREATE SCHEMA xwiki AUTHORIZATION dbo;
   GO

5. Download the Microsoft JDBC Driver for SQL Server, either from Microsoft or from the Maven Central repository, and copy the jar built for the Java version your servlet container runs into the WEB-INF/lib directory of the expanded XWiki WAR. XWiki ships no SQL Server driver.

   $ cp mssql-jdbc-*.jre11.jar /path/to/webapps/xwiki/WEB-INF/lib/

6. Edit the WEB-INF/hibernate.cfg.xml file of the expanded XWiki WAR, comment out the database block that is currently active, and add the block below inside <session-factory>. SQL Server, like DB2, has no commented-out block of its own in that file, so you write it rather than uncomment it. Replace localhost with the host running SQL Server if it is not the XWiki machine. The connection pool, the connection provider and hibernate.jdbc.use_scrollable_resultset are already set in the generic section at the top of the same file, the last one for SQL Server in particular, so the block does not repeat them.

       <property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;databaseName=xwiki;encrypt=true;trustServerCertificate=true</property>
       <property name="hibernate.connection.username">xwiki</property>
       <property name="hibernate.connection.password">xwiki</property>
       <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
   
       <property name="hibernate.connection.charSet">UTF-8</property>
       <property name="hibernate.connection.useUnicode">true</property>
       <property name="hibernate.connection.characterEncoding">utf8</property>
   
       <mapping resource="xwiki.mssql.hbm.xml"/>
       <mapping resource="feeds.hbm.xml"/>
       <mapping resource="instance.hbm.xml"/>
       <mapping resource="notification-filter-preferences.hbm.xml"/>
       <mapping resource="mailsender.hbm.xml"/>

7. Leave hibernate.dialect out: Hibernate reads the SQL Server version from the connection and picks the matching dialect by itself. Naming one by hand, as older instructions did, pins a dialect older than the server and is what makes a wiki search case-sensitive on instances set up that way.
8. Decide how the connection is encrypted. The driver has encrypted connections on by default since version 10.2, so a server presenting a self-signed certificate is refused unless you either install a certificate the JVM trusts and drop trustServerCertificate from the URL, or keep trustServerCertificate=true and accept that the certificate is not checked.
9. Start or restart the application server. XWiki creates its tables on the first run.
10. Verify that the tables are there, by listing the ones created in the xwiki database.

    $ sqlcmd -S localhost -U xwiki -P xwiki -d xwiki -Q "SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME"
    
    TABLE_SCHEMA   TABLE_NAME
    -------------- --------------------
    xwiki          xwikiattachment
    xwiki          xwikidoc
    xwiki          xwikiobjects
    xwiki          xwikispace

FAQ

Do I need to download a Hibernate mapping file for SQL Server?

No. XWiki ships xwiki.mssql.hbm.xml inside itself and keeps it in step with the schema, so the <mapping resource="xwiki.mssql.hbm.xml"/> line above is all you need. Copies of that file offered for download elsewhere are old forks of it, and an instance started against one of them misses the tables and columns XWiki has added since.

Why does the SQL Server mapping file differ from the default one?

Because SQL Server would otherwise store XWiki's long strings as varchar, which cannot hold them. The shipped mapping gives strings over 255 characters Hibernate's text type and strings over 32K its materialized_clob type, and keeps indexed columns at 255 characters. The dialect then decides which SQL Server type each of those becomes.

Why does filtering a Live Data table return no rows?

Because of how Hibernate maps one HQL function to Transact-SQL, which no configuration setting avoids. Live Data Filters Return Nothing on SQL Server gives the mechanism and the workaround.

Does each subwiki get its own database?

Yes, and each one also needs a schema of the same name inside it. XWiki runs create database itself when a subwiki is created, but it then qualifies that subwiki's tables with the same name, which SQL Server resolves as a schema and which nothing creates for you. Run CREATE SCHEMA in the new database, as the steps above do for the main wiki.

More

To find more about the current topic, you can search or use the table below and filter the columns to narrow your choices.

Related

• Configure DB2
• Configure Derby
• Configure H2
• Use a Container-Managed Data Source