Configuring MS SQL Server

Nuxeo supported the following versions of Microsoft SQL Server:

2012 2012 (Azure)

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. Before each statement Nuxeo add a SET TRANSACTION ISOLATION LEVEL READ COMMITTED; so statement 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;

Note that there must be no other open connection in the database until ALTER DATABASE is complete, otherwise the last command 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;

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.

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 here: http://msdn.microsoft.com/en-us/library/ms189275.aspx)

By default, recovery mode is full, so you can get performance issues when enabling this mode

Mode View:

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ;
GO

Mode Update (if FULL):

USE master ;
ALTER DATABASE model SET RECOVERY SIMPLE ;

Full-Text

If you configure a full-text index in Nuxeo (which is the default), you will need to make sure that your SQL Server instance has full-text search configured (it's an optional component during installation). See http://msdn.microsoft.com/en-us/library/ms142571.aspx 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.

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 MSSQL 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 creates deadlock 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.

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

 

---