Micetro by Men&Mice

Latest versions

Search all documentation

Child pages
  • Using Microsoft SQL Server as a database server for the Men&Mice Suite
Skip to end of metadata
Go to start of metadata

This article describes how to connect an M&M Central to Microsoft SQL Server instance.

We recommend that the network latency between the SQL server and M&M Central is no more than 5 ms. Otherwise, the performance of M&M in conjunction with SQL Server can't be guaranteed to be acceptable

We recommend that the SQL Server database is managed and maintained by a database administrator (DBA)

Setting up the Database

Men & Mice requires that a blank database is created on the database server, with the collation SQL_Latin1_General_CP1_CS_AS, and a login (Windows or SQL server authenticated) that has db_owner access to that database and an effective default schema of mmCentral.

The attached CreateDatabase.sql is a suggestion for the database and database server setup.  Please note that this script also configures the SQL server itself, which is unnecessary and undesirable in most cases. Therefore, review the script with your database administrator and only run appropriate parts of it. 
Before you run the script please go through the script and change the necessary entries accordingly. This includes path strings (default C:/Data) and very important also the password, which is set by default to "1234" in the script!

The script is preconfigured for an 8 core processor machine, i.e. as a best practice we recommend to create for each processor core one temp file. If you have only a two core machine please comment out the last 6 temp file creation commands in the script.

The script will drop an existing database with the name "mmsuite", so take care when running the script. 

Subsequently the script will create:

  • a user "mmSuiteDBUser" with the password that you have inserted in the script.
  • an empty database with the name "mmsuite" and the following collation:
    COLLATE SQL_Latin1_General_CP1_CS_AS

For details please have a look into the CreateDatabase.sql script.

Configuring the connection parameters

If it's a dedicated server please don't forget to enable the TCP/IP protocol with the SQL Configuration Manager

Men&Mice Central running on Windows

Please note that the following needs to be run from the data directory, i.e. the same directory that contains the preferences file for Central. Otherwise the connection information for SQL Server will not be appended to the preferences file.

  1. Download the Men&Mice mmdbprefs.exe tool
  2. Copy the mmdbprefs.exe into the data directory of the Men&Mice Central.
    Usually located in one of these locations: 
    • C:\Program Files\Men and Mice\Central\Data
    • C:\Program Files(x86)\Men and Mice\Central\Data
    • C:\ProgramData\Men and Mice\Central (Windows Server 2008 R2 and later)
  3. Then start the mmdbprefs.exe tool by double-click. It will ask you a few questions
Choose database provider to use:

        1. SQLite
        2. MSSQL

For MS SQL Server please enter "2" and confirm with Enter.

Enter database server:

Please type in the database server in the following format:

<ip/dns name of SQL server><,port>\<Instance name>@<Database name>

For example:\INSTANCENAME@databasename

Confirm your input by pressing the Enter key.

Enter database user name:

Type in the username, default in the script is "mmSuiteDBUser", that will be used to connect to your database instance on the specified SQL server. Then press Enter. If you let the database user name empty Windows Authentication will be used instead of a user based connection (this requires to run the M&M Central service under credentials that have access by Windows Authentication to the database).

Enter database password:

Finally you need to provide the password (it will not be displayed in the shell) and then press Enter.

Now the preferences.cfg file in the data directory contains beside the fingerprint of Central (i.e. the "password" XML-tag) four additional XML tags: database, databaseserver, databaseusername and databasepassword
The preferences.cfg file for normal user/password authentication should look like:

<password value="the fingerprint hash"/>
<database value="MSSQL"/>
<databaseserver value="<name or ip of the SQL server>\<name of instance, e.g. SQLEXPRESS>@,<name of database, e.g. mmsuite"/>
<databaseusername value="mmSuiteDBUser"/>
<databasepassword value="password hash"/>   

An example preferences.cfg file for the Windows Authentication method should look like (databaseusername tag must be present and the value attribute must be set to empty string):


<password value="the fingerprint hash"/>
<database value="MSSQL"/>
<databaseserver value="<name or ip of the SQL server>\<name of instance, e.g. SQLEXPRESS>@,<name of database, e.g. mmsuite"/>
<databaseusername value=""/>

Men&Mice Central running on Linux

Please note that a ODBC driver will need to be installed on the Linux machine to be able to use Microsoft SQL Server

  1. Navigate to the data directory of the Men&Mice Central:
    Usually located in:
    • /var/mmsuite/mmcentral
  2. Edit the preferences.cfg file with in that directory with the following:

    Please note that the DatabasePassword value if prefixed by "plaintext:" will be swapped out by the Men&Mice Central for a password hash during start up.

    <Database value="MSSQL" />
    <DatabaseServer value="ip/dns name of SQL server><,port>\<Instance name>@<Database name>" />
    <DatabaseUsername value="mmSuiteDBUser" />
    <DatabasePassword value="plaintext:<your password here>" />
  3. Save your changes.

Establishing a connection to Men&Mice Central


Restarting the Men&Mice Central service with the new preferences file should connect the Men&Mice Central to your freshly created database. The Men&Mice Central creates the database schema (tables...) during the first connection.

Since the database was freshly created you can now follow the normal installation procedure. Please see the installation guide and the user manual of the M&M Suite for further information.

SQL Server in High Availability

Mirrored SQL Server instances

Please note that this feature is in maintenance mode by Microsoft and may be removed in a future version of Microsoft SQL Server.

Please note that Database Mirroring is only supported for Men&Mice Central running on Windows.

Men&Mice Central (v6.3.0.6 and above) can be made aware of mirrored instances of the database. In the case a failover occurs the Men&Mice Central will try to talk to the new principal server, i.e. the failover partner, if specified.

  1. Change the DatabaseServer value in the preferences.cfg or rerun mmdbprefs.exe and provide the value:

    "serverA@mmsuite;Failover_Partner=serverB", where "serverA" and "serverB" are the principal and mirror SQL servers, and "mmsuite" is the name of the database
  2. If the machine running M&M Central does not have SQL Server installed, install the Microsoft SQL Server Native Client tools.

Always On Availability Groups

Please note that Always On Availability Groups is only supported for Men&Mice Central running on Linux.

Men&Mice Central supports the use of Always On Availability Groups (v9.3.0 and above), In the case of a failover occurs the Men&Mice Central will refresh its database connections to the new primary replica.

  1. Change the DatabaseServer value in the preferences.cfg to the virtual IP address or the FQDN of the availability group listener:

    <DatabaseServer value="virtual ip/fqdn of availability group listener><,port>\<Instance name>@<Database name>" />

  • No labels