Nuxeo supports the following version of Microsoft SQL Server:
SQL Server 2008
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 "
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:
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 "
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;
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:
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.
SELECT recovery_model_desc FROM sys.databases WHERE name = 'nuxeo';
USE master; ALTER DATABASE nuxeo SET RECOVERY SIMPLE;
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 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.
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:
Nuxeo uses a full-text catalog named
nuxeo by default, this can be changed in the Nuxeo configuration files (see configuration details).
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:
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)
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.
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.
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).