Using Microsoft SQL Server as the Database Engine

Instead of Firebird, you can use Microsoft SQL Server as the database engine. We recommend that Microsoft SQL Server is used if the vault contains more than 50,000 objects. With large vaults, Microsoft SQL Server provides better efficiency than Firebird. However, use of the Microsoft SQL Server database engine requires that the administrator is familiar with Microsoft SQL Server management.

With Microsoft SQL Server, the database server memory can be more efficiently used and the backup storage of large data vaults is improved. You can also switch to the mirrored database server without delay if necessary.

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

Supported editions and versions

You can use Microsoft SQL Server 2012, 2014, 2016, 2017, 2019, or later with its latest service pack as the vault database engine. These Microsoft SQL Server editions are supported: Microsoft SQL Server Express, Standard, and Enterprise. Refer to Microsoft documentation to make sure that your Microsoft SQL Server edition has the necessary features and capabilities for your environment.

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.

Guidelines for version selection, updates, and upgrades:

  • When you take Microsoft SQL Server into use as the database engine, we recommend that you use the latest version of Microsoft SQL Server that M-Files and your operating system support. For Microsoft SQL Server software requirements, refer to Microsoft documentation.
  • Make sure that the server machine always has the latest service pack and cumulative updates installed. To do this, refer to Latest updates for Microsoft SQL Server.
  • To upgrade your version of Microsoft SQL Server, refer to Upgrade SQL Server.

Migration to Microsoft SQL Server

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

Configuration

M-Files supports the use of Microsoft SQL Server on Microsoft Windows. With a self-hosted cloud-based SQL database, you can also use Microsoft Azure SQL Database Managed Instance as the vault database engine.

Microsoft SQL Server can 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. For instructions on the efficient operation of SQL Server, refer to Microsoft SQL Server documentation.

Make sure 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.

Before you take Microsoft SQL Server into use as the database engine, see Microsoft SQL Server Requirements and Database engine and data storage.



The Microsoft SQL Server properties dialog.
Important: If your SQL Server does not use the default port (1433), you must give the server name in the format <server name>,<port>.

M-Files Server stores data in the 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.

Select one of these options:

  • Store file data in the vault database
  • Store file data in a file-system folder: With this option, you can specify the location for saving the files to a network drive or to another location. You can designate a specific account for processing the file data to keep the file data secure.
    Important: If you want to use a network drive for storing file data, you must use the format //<server>/<path> to specify the file data location.
    Note: 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 more 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 vault database. Backup copying is done with SQL Server's own management tools and backup copying solutions offered by third parties. When restoring a backup copy, the administrator first returns the vault database to the SQL Server, and then reattaches the vault to M-Files with the Attach Document Vault function.

If your file data is stored on the file system separately from the database, you must back up both the Microsoft SQL database and the files on the file system separately.
Important: Always back up the SQL database (metadata) first and then the file system data (object files) to avoid references to non-existing object files. Do not run M-Files Server optimization after backing up the Microsoft SQL database. Otherwise, the files that have been marked for destruction are removed.

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