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 several hundreds of thousands of objects. With large vaults, Microsoft SQL Server provides better efficiency than Firebird. However, with Microsoft SQL Server, the administrator must be 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.
Supported Microsoft SQL Server versions
You can use Microsoft SQL Server as the vault database engine. Refer to our lifecycle policy for information about the supported versions. These 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. M-Files supports the use of Microsoft SQL Server on Microsoft Windows.
With the Microsoft SQL Server Enterprise Edition versions 2008–2017 table data and indexes can be compressed. 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 later support updateable columnstore indexes (in earlier versions, columnstore indexes are only available in Enterprise Edition). This enables better performance with 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.
Configuring Microsoft SQL Server databases
M-Files supports the use of Microsoft SQL Server on Microsoft Windows. With a cloud-based M-Files environment that you manage yourself, 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.
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.
Instructions for specialized setups
Refer to these documents in specialized Microsoft SQL Server environments:
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 set 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 for Microsoft SQL Server.
Backing up Microsoft SQL Server databases
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.
For more instructions, see the M-Files knowledge base article M-Files Backup Policy.
Important information
- 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 you have backed up the SQL database. Otherwise, the files that have been marked for destruction are removed.
- Do not back up an active M-Files system with a snapshot of the file system where its data is stored. This can create a damaged or unusable backup because write operations to files (most importantly, the database engine files) can be ongoing and, thus, incomplete. If you use full virtual machine (VM) snapshots for backups, make sure that the VM software fully supports creation of snapshots of an active system. This means that the software can restore the system to exactly the same state, including the memory and CPU states.
Changing the location of the vault file data for Microsoft SQL Server
If you use Microsoft SQL Server and your file data of the vault is stored either in the vault database or the file system, you may change the location of the vault file data.
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.
To change the location of the vault file data:
-
Click Properties.
Result:The Document Vault Properties dialog is opened.
-
Under the Use Microsoft SQL Server option, click the
Define... button.
Result:The Document Vault - Microsoft SQL Server dialog is opened.
-
Click the File Data Location... button.
Result:The File Data Location dialog is opened.
-
If you selected the option Store file data in a file-system
folder, complete the following steps:
-
In the File-System Folder dialog, click the
button to browse for
the new file data location, or type in the location in the text
field.
The location can also be a UNC path to a network drive.
-
In the File-System Folder dialog, click the
button to browse for
the new file data location, or type in the location in the text
field.
