Using Microsoft SQL Server as the Database Engine

Note: This content is no longer updated. For the latest content, please go to the user guide for M-Files Online. For information on the supported product versions, refer to our lifecycle policy.

Instead of Firebird, you can use Microsoft SQL Server 2012, 2014, 2016, 2017, 2019, or later with its latest service pack as the database engine. M-Files supports all the SQL Server editions (Express, Standard, Enterprise, etc.). For the best performance, we recommend using SQL Server 2016 Service Pack 1 or later. M-Files supports the use of Microsoft SQL Server on Microsoft Windows.

Note: In new installations, we recommend using Microsoft SQL Server Enterprise Edition 2017 Service Pack 1 or later. If you are already using Microsoft SQL Server as the database engine for one or more vaults, and are interested in upgrading to a newer version for performance reasons, we recommend consulting our customer support at [email protected] before upgrading.

The Microsoft SQL Server Enterprise Edition versions 2008–2017 provide the possibility for compressing table data and indexes. This reduces the input/output activity of the disk, but also increases the CPU load by about 10 percent. Typically this means reduced database sizes.

Microsoft SQL Server 2016 Service Pack 1 and above support updateable columnstore indexes (in earlier versions, columnstore indexes are only available in Enterprise Edition), enabling better performance when opening sub-levels of views (such as Documents by project). This is especially beneficial when empty virtual folders are set to be hidden.

Using SQL Server means that the database server memory can be more efficiently used and the backup storage of large data vaults is improved. In the event of problems, errors etc., you can switch to the mirrored database server without delay.

We recommend using Microsoft SQL Server with large document vaults that contain several hundreds of thousands or more documents or other objects. With large document vaults, Microsoft SQL Server provides better overall efficiency than Firebird. However, use of the Microsoft SQL Server database engine requires that the administrator is already familiar with the Microsoft SQL Server management.

Note: Microsoft SQL Server licenses are not included in M-Files licenses and must be purchased separately.

Microsoft SQL Server may be located on the same machine as the M-Files Server, or it can be installed on another server. If SQL Server is installed on another server, M-Files Server and SQL Server must be linked with a fast network connection. Instructions for ensuring the efficient operation of SQL Server can be found in the Microsoft SQL Server documentation. Firstly, it is recommended to ensure that the SQL Server machine has a sufficient amount of memory. The number and speed of processors and hard drives also have a significant impact on the efficiency.

Note: If your SQL Server does not use the default port (1433), the server name must be given as <server name>,<port>.

When Microsoft SQL Server is used as the document vault database engine, M-Files Server stores data in the document vault in the associated database. Certain secondary data that do not require a backup, such as search indexes, are left outside the database.

File Data Location

File data can be saved in the Microsoft SQL Server database or other location, such as a network drive.

You can choose to:

Store file data in the vault database
Store file data in a file-system folder: With this option, you can freely specify the location for saving the files to a network drive or to another location. You can keep the file data secure by designating a specific account for processing the file data.
Note: If you want to use a network drive for storing file data, you must use the format //<server>/<path> for specifying the file data location.
Note:
In M-Files builds older than 12.0.6661.0, the vault is offline for the entire duration of changing the file data location. Note that if your vault contains large amounts of file data (for instance over one terabyte of data), changing the location may take several days, or even over a week, to complete.

With the build 12.0.6661.0 and later, the vault remains online and fully operational for the majority of the duration of changing the file data location. Only when the new file data location is taken into use, is the vault offline for the duration of taking the new location into use. If you cancel the operation of changing the file data location, you can always resume it by selecting the same location as you previously selected for file data.

For further instructions, see Changing the Location of the Vault File Data.

Backing Up

The administrator is responsible for making backup copies and timing the backup copies of the document vault database. Backup copying is performed using SQL Server's own management tools and backup copying solutions offered by third parties. When restoring a backup copy, the administrator first returns the document vault database to the SQL Server using the desired method, and then reattaches the document vault to M-Files using the Attach Document Vault function.

If you are using Microsoft SQL Server as the database engine and your file data is stored on the file system separately from the database, administrators must back up both the Microsoft SQL database and the files on the file system separately.

Important: Always back up the SQL database first and then the file system data to avoid references to non-existing object files.

The steps to be followed in this case are:

Back up the Microsoft SQL database first (metadata).
Do not run M-Files Server optimization at this point, as this would remove files that might have been marked for destruction after step 1 was performed.
Back up file system data (object files).

For more comprehensive backup instructions, see the M-Files knowledge base article M-Files Backup Policy.

Migrating to Microsoft SQL Server

The document vault database engine can also be changed from Firebird to Microsoft SQL. For more information, see Migrating the Vault Database to Microsoft SQL Server.