Microsoft SQL Server

Nuxeo supports the following version of Microsoft SQL Server:

SQL Server 2014

System Database Collation

We've observed incorrect behavior (in particular with full-text search) if the SQL Server master database is not configured with a case-insensitive collation (a collation name with "CI").

To make sure this is the case, use:

SELECT collation_name FROM sys.databases WHERE name = 'master'

For instance the following collations have been checked to work correctly:

• SQL_Latin1_General_CP1_CI_AS
• French_CI_AS

Database Collation

To work properly Nuxeo need to have some columns with a case-sensitive collation.

To make sure this is the case, use:

SELECT collation_name FROM sys.databases WHERE name = 'nuxeo' -- or your custom database name

You need a case-sensitive collation (a collation name with "CS"), like French_CS_AS.

If this is not the case for your existing database you can change it like this:

ALTER DATABASE nuxeo COLLATE French_CS_AS

If you get database error related to rights issue, you can set it as a single user owner:

ALTER DATABASE nuxeo SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE nuxeo COLLATE French_CS_AS
GO
ALTER DATABASE nuxeo SET MULTI_USER

Row Versioning-Based Transaction Isolation

To prevent locking and deadlocking problems you need to enable the row versioning-based isolation levels. With row versioning readers do not block other readers or writers accessing the same data. Similarly, the writers do not block readers. However, writers will block each other. Whenever a transaction is started, Nuxeo adds a SET TRANSACTION ISOLATION LEVEL READ COMMITTED so the transaction sees only data committed before the query began.

To enable the row versioning submit the following SQL commands:

ALTER DATABASE nuxeo SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE nuxeo SET READ_COMMITTED_SNAPSHOT ON;

If you don't execute the above commands, you will get the following error at Nuxeo startup:

Snapshot isolation transaction failed accessing database 'nuxeo' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

Note that there must be no other open connection in the database until ALTER DATABASE is complete, otherwise the last command above will hang. You can work around this (when executing the command from SQL Server Management Studio for instance) by adding WITH ROLLBACK IMMEDIATE:

ALTER DATABASE nuxeo SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Recovery Model

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk_logged (see more details at http://msdn.microsoft.com/en-us/library/ms189275.aspx).

By default, recovery model is full, so you can get performance issues. You may want to change to the simple model if this is the case, but please consult your database administrator first.

Mode View:

SELECT recovery_model_desc FROM sys.databases WHERE name = 'nuxeo';

Mode Update:

USE master;
ALTER DATABASE nuxeo SET RECOVERY SIMPLE;

Full-Text

If you've configured your Nuxeo Platform instance to index full-text using the SQL database (by disabling the default configuration which uses Elasticsearch), you will need to make sure that your SQL Server instance has full-text search configured (it's an optional component during installation). See https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search for details.

Failing to do this will provoke errors like:

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'fulltext' because it is not full-text indexed.

Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable full-text search for the database. The functionality to disable and enable full-text search for a database is deprecated. Please change your application.

Here is the French version of these messages, for reference:

Impossible d'utiliser le prédicat CONTAINS ou FREETEXT sur table ou vue indexée 'fulltext', car il n'y a pas d'index de texte intégral.

La recherche en texte intégral n'est pas activée dans la base de données en cours. Utilisez sp_fulltext_database pour l'activer sur cette base de données. La fonctionnalité de désactivation et d'activation d'une recherche en texte intégral pour une base de données est désapprouvée. Modifiez votre application.

You can verify if your SQL Server instance has its full-text feature installed before creating your database:

SELECT SERVERPROPERTY('IsFullTextInstalled');

Full-Text Catalog

Nuxeo uses a full-text catalog named nuxeo by default, this can be changed in the Nuxeo configuration files (see configuration details).

Full-Text Analyzer

The language used to analyze full-text (called a LANGUAGE in SQL Server parlance) can be specified in the configuration for the database, instead of "english" in the section <fulltext analyzer="english">. The available languages in your database can be listed by using:

SELECT alias FROM sys.syslanguages

Incorrect Results Using Stopwords

If you do not get the expected results with a keyword like "table of contents", the problem could come from how SQL Server handles the stopwords ("of" in this case). It could be necessary to run this code:

USE master;
GO
EXEC sp_configure 'transform noise words',1
RECONFIGURE WITH OVERRIDE;

Additional Maintenance Operation

The SQL Server back end comes with ACL (Access Control List) optimization. This optimization works with cache tables to store rights for each users and keep tracking of documents and rights changes. Theses data are reset when the server is started.

For long-running instance or if you want to perform a hot backup without these unnecessary data, you can invoke the following stored procedure:

USE nuxeo;
EXEC dbo.nx_vacuum_read_acls;

Or you can exclude the following tables from your backup:

• aclr
• aclr_modified
• aclr_permissions
• aclr_user_map
• aclr_user

Deadlock and Lock Escalation

SQL Server is doing lock escalation: converting many row level locks to page lock or table lock. When doing concurrent write operations this can create deadlocks even when working on distinct data.

You can have more information on deadlock by enabling the following traces:

 DBCC TRACEON(1222,-1);
 DBCC TRACEON(1204,-1);

Then you can try to disable the lock escalation on the table impacted by deadlocks:

ALTER TABLE mytable SET (LOCK_ESCALATION=DISABLE)

Clustered Index

SQL Server uses a clustered index to defined how the data is organized physically on disk. Before Nuxeo 5.7 we didn't define a clustered index, so the primary key is used, however this primary key is a random UUID which means that data keeps getting reorganized on disk on practically every insert or delete.

This has been fixed for new instance since Nuxeo 5.7. For instance created before there are migration script to apply to add these index, see NXP-10934 attachments to get the script.

Indexes Maintenance

If the indexes are fragmented then the query response will be slow and the data storage will require more disk space. Microsoft recommends reorganizing an index with a fragmentation between 5% and 30%, and rebuilding an index with a fragmentation of more than 30%. Database administrators should always make sure that fragmentation of indexes is handled on time.

Limitations

Microsoft SQL Server is a good database however it has a few unfortunate hiccups.

Here are some limitations in the context of the Nuxeo Platform:

• Its snapshot isolation level is insufficiently isolated and not comparable to other MVCC databases, and may sometimes cause errors during concurrent writes.
• It is infamous for its lock escalation problems that cause no end of troubles and is a very poor locking design in the first place (MS184286).
• Only one full-text index is allowed per table (MS187317).
• Full-text indexing cannot be configured to be done synchronously with transaction commits.
• It does not support circular ON CASCADE DELETE constraints (KB321843).

---