Creating and initializing a Microsoft SQL Server database

Complete the following steps to create and initialize a Microsoft SQL Server database on a Windows server:

Before you begin

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

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

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

• The TMTM 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 TMTM installation and to the database server. TMTM services are 64-bit applications on Windows and Linux (SLES, RHEL). 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 TMTM services connect successfully, create a 64-bit ODBC connection to the TMTM 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)).

   You must select the Native API for SQL Server (SNAC) for SQL Server 2008R2/2012 or the ODBC driver for SQL Server 2014/2016/2017 in order to connect to the SQL Server database. If this driver is not installed, ask your SQL Server Administrator to install it. The version of SNAC/ODBC driver should match the version of the database server.

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.