Microsoft SQL Server

To provide a database for ConSol CM or for CMRF the same steps are required. For the CMRF database just change the database/database user name.

General preparations

• JDBC driver
  

  Execute the stored procedure sp_sqljdbc_xa_install.

  For Microsoft SQL Server versions prior to 2017, you need to install the XA features of the JDBC driver:

  • Download the JDBC driver from Microsoft and unpack it. Check the system requirements for the correct version.
    

  • There is a directory xa which has several subdirectories for the XA driver DLLs. Choose the correct one according to the architecture your server is running on (e.g., x86\sqljdbc_xa.dll) and copy the DLL file into the binn directory of your Microsoft SQL Server installation, for example:

    C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn

  • Then execute the xa_install.sql script from the above unpacked JDBC driver directory.

• Enable XA Transactions
  (Source: Execute the steps below Running the Microsoft DTC Service.)
  The Microsoft DTC service should be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started. To enable Microsoft DTC for XA transactions, you must follow these steps:

  1. Click the Start button, type dcomcnfg in the Start Search box, and then press Enter to open Component Services. You can also type %windir%\system32\comexp.msc in the Start Search box to open Component Services.

  2. Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.

  3. Right-click Local DTC and then select Properties.

  4. Click the Security tab on the Local DTC Properties dialog box.

  5. Select the Enable XA Transactions check box and then click OK. This will cause a Microsoft DTC service restart.

  6. Click OK again to close the Properties dialog box and then close Component Services.

  7. Stop and then restart Microsoft SQL Server to make sure that it syncs up with the Microsoft DTC changes.

• Database and database user for CM

  • Create a new database (placeholder <CM_DB>). Execute the following statement on this database:

    ALTER DATABASE <CM_DB> SET READ_COMMITTED_SNAPSHOT ON;

  • Create an application user (placeholder <CM_USER>) with the following authorizations:

    • Grant user <CM_USER> access to database medium master (role public and role SqlJDBCXAUser).

    • Grant user <CM_USER> access to database medium <CM_DB> (role public and role db_owner).

Post-installation tasks

After the CM-specific database objects have been created during the first successful start of the CM application (see Setting up the ConSol CM application), you can set the starting ticket number. By default, the ticket numbers are assigned starting with 1, but you can determine that they should start with another number.

For example, execute the following statement using SQL Management Studio to start the ticket numbers with 10000:

dbcc checkident ('cmas_ticket', reseed, 10000)