Prepare an SQL server

SQL server version

Your version of SQL Server must be compatible with Dimensions CM. For details, see Support.

SQL server collation restrictions

The physical storage of character strings in Microsoft SQL Server Enterprise is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared.

For an OpenText Dimensions CM for SQL Server Enterprise installation to succeed, the following restrictions apply to collations:

  • The SQL Server instance must be configured to use mixed authentication mode.
  • The default collation of the SQL Server instance must be case-insensitive (for example, Latin1_General_CI_AS).
  • The server installer creates a database in the specified SQL Server instance. By default, the collation of the database is the same as the default collation of the SQL Server instance. Before the database is created you can specify a non-default collation. Both the SQL Server instance and the CM database must use case-insensitive collations. The CM server installer does not proceed if you specify a case-sensitive collation. If you plan to use a SQL Server database collation name with a collation designator other than Latin1_General, contact Support.before proceeding.

Prepare local and remote nodes

Set trustworthy mode

To successfully install an OpenText Dimensions CM schema into a SQL Server Enterprise database, the database should be in "trustworthy mode". For a local SQL Server Enterprise database, the OpenText Dimensions CM installer automatically sets trustworthy mode to 'true'. For an upgrade or installation with a remote SQL Server Enterprise database, there is no installer support for setting trustworthy mode to ’on’.

To check that trustworthy mode, set it to 'true':

  1. Open SQL Server Management Studio.

  2. Connect to a SQL Server database instance.

  3. In Object Explorer, expand Databases and select the database to use.

  4. Right-click and select Properties and then Options.

  5. Under Miscellaneous, look for Trustworthy and verify that it is set to True.

To change or modify trustworthy mode:

  1. Check there are no connections to the database.

  2. Open the SQL Server Management Studio.

  3. Connect to a SQL Server database instance.

  4. In Object Explorer, expand Databases and select the database to use.

  5. Right-click and select New Query.

  6. Enter the following text in the query window:

    alter database <dbname> set trustworthy on

    where <dbname> is the name of the database.

  7. On the toolbar, click Execute.

  8. Exit SQL Server Management Studio.

Note: If you backup your database and then restore it into the same or a different database, by default trustworthy mode is set to 'off'.

Local and remote node prerequisites

Check that both machines are in the same Windows domain and that there is a network user-id available that can be assigned to be the Dimensions CM system administrator, also known as dmsys (referred to here as <DOMAIN\DSA>). This user needs to be an operating system administrator user on the Dimensions CM server (the local node) but does not need to be an operating system administrator on the SQL Server database machine (the remote node).

SQL Server Enterprise roles

Allocate the following SQL Server roles to the user performing the installation with SQL Server Enterprise:

  • public

  • sysadmin

You must also allocate the same roles to local administrator accounts, as SQL Server Enterprise does not automatically give administrative rights.

Remote SQL Server prerequisites

Before installing Dimensions CM, complete the SQL Server pre-installation steps, which includes the following:

  • Create the database.

  • Create the OpenText PulseUno user.

  1. On the remote machine where the SQL Server database is installed, log in as the <DOMAIN\DMSYS> user.

  2. Verify that <DOMAIN\DMSYS> has a login to connect to the SQL Server:

    1. Open SQL Server Management Studio and connect to a server.

    2. In Object Explorer, expand Security | Logins.

    3. Verify that <DOMAIN\DSA> is listed.

  3. Verify that <DOMAIN\DSA> has the appropriate SQL Server Roles (public and sysadmin) to perform the installation:

    1. In Object Explorer, expand Security | Server Roles.

    2. Right-click sysadmin and select Properties.

    3. In the Server Roles dialog, click Add.

    4. In the Select Logins dialog, add <DOMAIN\DSA> as a member of the sysadmin role.

  4. Copy these files from the DVD or the download folder: 

    db_preinstall\mssql\win32\mssql_pre_install.cmd
db_preinstall\mssql\win32\pulse_mssql_pre_install.cmd

    Copy the files to the <SQL Server Home>\binn folder on the remote database machine, for example: 

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\binn

  5. Do the following:

    1. Open a command prompt on the SQL Server database machine.

    2. Navigate to the <SQL Server Home>\binn folder where you copied the files in a previous step.

    3. Run this command:

      mssql_pre_install.cmd <SQL Server instance>
<Dimensions database name to be created>
<Database files directory to be created>
<DOMAIN\DSA>
<PCMS_SYS SQL password>
<Database filesize to be allocated (MB)>
<Logfile size to be allocated (MB)>
<Collation of the database>
<Language of the database account>

      Example 1: for the named instance NWB-VADYMK\DMSQL2K

      mssql_pre_install NWB-VADYMK\DMSQL2K dim14 \
C:\mssql\datafiles NWB-VADYMK\DMSYS pcms_sys \
30 15 Latin1_General_CI_AS us_english

      Example 2: for the default instance (local) 

      mssql_pre_install (local) dim14 \
C:\mssql\datafiles NWB-VADYMK\DMSYS pcms_sys \
30 15 Latin1_General_CI_AS us_english

  7. From the same command prompt, run this command: 

    pulse_mssql_pre_install <SQL Server instance>
<Dimensions Database name>
<Path to the database files directory to be created>
<DOMAIN\DSA>
<PULSE SQL username>
<PULSE SQL user password>
<Database filesize to allocate (MB)>
<Logfile size to allocate (MB)>
<Collation of the database>
<Language of the database account>

    Example: 

    pulse_mssql_pre_install NWB-VADYMK\DMSQL2K dim14
"C:\mssql\datafiles" NWB-VADYMK\DMSYS pulse pulse 30 15
Latin1_General_CI_AS us_English

Configure a local server for remote SQL

To perform database service operations, a Dimensions CM server requires access to an SQL Server database through an SQL Server client. The client can be:

  • An SQL Server client

  • SQL Server Enterprise

OpenText Dimensions CM performs all database operations with that remote schema utilizing ODBC connectivity using a Microsoft ODBC driver. To check the driver and SQL version compatibility, see the Microsoft help.

OpenText Dimensions CM for SQL Server Enterprise does not ship with any SQL-related modules or software and relies on the installed ODBC driver to manage and access its base databases.

The OpenText Dimensions CM for SQL Server Enterprise installer offers you the option of using an existing ODBC connection or creating a new one. When you perform a remote install, we recommend that you create the ODBC before installing.

Creating an ODBC driver

Set up an SQL Server client locally to perform operations between the local CM server and the SQL remote database. Create a local ODBC DSN that has the same name as the remote Dimensions CM database.

  1. In Administrative Tools, open Data Sources (ODBC).

  2. On the System DSN tab, click Add.

  3. In the Create a New Data Source dialog box, select ODBC Driver <version> for SQL Server and click Finish.

    To check the driver and SQL version compatibility, see the Microsoft help.

  4. In the Create a New Data Source to SQL Server dialog box, provide the following details, and click Next:

    • Name: Enter the name of the Dimensions CM database that is the source of the data.

    • Description: Enter a description of the data source.

    • Server: Select the SQL Server to connect to.

  5. Accept the default option for verifying login authenticity, and click Next.

  6. Select the option Change the default database to. Then select the database name from the list. Click Next.

  7. Select the option Change the language of SQL Server system messages to. Then select English from the list.

  8. Click Finish.

  9. (Optional) Test the connection.

  10. Perform the Dimensions CM installation as the user <DOMAIN\DSA>, for example: DOMAIN\dmsys.

Remove Oracle registry keys

If you are installing CM with SQL Server Enterprise on Windows, and Oracle was installed on the system at the time of the original Dimensions installation, before installing, run regedit and remove the appropriate the key:

HKEY_LOCAL_MACHINE\SOFTWARE\Serena\Dimensions\
<version>\Install\INSTALL_OracleSid