Creating and initializing a Microsoft SQL Server database

Before you begin

• The database server software must be installed and configured.
• Ensure that the database is for the exclusive use of the MainView Middleware Monitor (MVMM ) product.
• You must have created a SQL Server login and user for use by MVMM .

• A Microsoft SQL Server database cannot be paired with a Linux service set.

  MVMM monitors objects with case sensitive names. Therefore, the server and database collation must be case sensitive. A case sensitive server collation is required because MVMM uses tempdb.

• The MVMM server must be in close physical proximity to the database server with a network latency of less than 5ms between them (100 Mb LAN). A gigabit LAN is recommended.
• Database clients must be appropriate to the MVMM  installation and to the database server. MVMM services are 64-bit applications on Windows and Linux. It is recommended that the database client version match the database server version.

To create and initialize a Microsoft SQL Server database

1. Install the database client software on all computers that need access to the database.
2. Run mssql.create.sql and mssql.init.sql using the command line as the database owner.
   The database objects should be created by a database owner and thus qualified with dbo.
   
   sqlcmd –I -S DBServerName -d Database -i mssql.create.sql -o mssql.create.log
   sqlcmd –I -S DBServerName -d Database -i mssql.init.sql -o mssql.init.log
3. Include the following database security settings for the TMTM database user:
   • db_writer role
   • db_reader role
   • db_ddladmin role
   • execute permissions for all stored procedures with the PRC_ prefix and all stored functions with the FN_ prefix.
4. To have the MVMM services connect successfully:

   • Windows: Create a 64 bit ODBC connection to the MVMM database.
     The 64-bit ODBC configuration tool (ODBC Driver Manager) can be started with %SystemRoot%\system32\odbcad32.exe from the command prompt, or using the GUI (Control Panel > Administrative Tools> ODBC Data Sources (64-bit)).

     Note

     Select the right version of the SQL Server Client driver (SNAC or ODBC) according to the following table:

     SQL Server Client driver (SNAC or ODBC) name as it appears in the ODBC Driver Manager Drivers Tab	SQL Database Server version that should be used with each SQL Server client driver
     SQL Server Native Client 11.0	SQL Server 2012
     ODBC Driver 11 For SQL Server	SQL Server 2014
     ODBC Driver 13 For SQL Server	SQL Server 2016
     ODBC Driver 17 For SQL Server	SQL Server 2017, SQL Server 2019

     If no driver is installed, ask your SQL Server Administrator to install it.

   • Linux: Install Microsoft ODBC Driver 17 for SQL Server for Linux (Database-server-requirements), and then add a new entry to /etc/odbc.ini file (under root authority). 

     Example

     [MVMM_DB]
     Driver = ODBC Driver 17 for SQL Server
     Server = tcp:mvmm_db_hostname,1433
     Database = mvmm_db_name
     MultipleActiveResultSets = true

     mvmm_db_hostname is the MVMM database server hostname.

     mvmm_db_name is the MVMM database name the way it was created in SQL Server.

     MultipleActiveResultSets is required to be set to "true" for the MVMM services to function properly.

5. When the scripts are complete, ensure that your entire database network is functioning and all of the clients can access and write to the server.

Where to go from here

Run scripts to create and initialize the database.