Preconfiguring SQL Server Manually

You must prepare your Microsoft SQL Server database before using the 3DOrchestrate deployment utility.

This task shows you how to:

Before you begin: Complete the 3DOrchestrate Distribution Server installation, but make sure you have not yet run the deployment utility.

Create the Database

  1. Start the Microsoft SQL Server Management Studio. Connect to the studio application with the administrative account "sa".
  2. Click New Query on the toolbar.
  3. Copy the SQL code below, and paste it into the new query window. 

    USE [master];
GO

CREATE DATABASE [<db_name>]
ON PRIMARY (
    NAME = '<db_name>',
    FILENAME = '<db_path>\<db_name>.mdf',
    SIZE = 1GB,
    FILEGROWTH = 50%
)
LOG ON (
    NAME = '<db_name>_LOG',
    FILENAME ='<db_path>\<db_name>_LOG.ldf',
    SIZE = 10MB,
    FILEGROWTH = 25%
);
GO

ALTER DATABASE [<db_name>] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [<db_name>] SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

    In this code, substitute your desired values for the following:

    <db_name>
    Name for the 3DOrchestrate database; for example, 3Dorchdb.
    <db_path>
    Folder location in which to install the database; for example, C:\3DExp_db.

  4. Click Execute on the toolbar.

Create the Database User and Grant User Access

  1. Continue working as the administrative user sa in Microsoft SQL Server Management Studio.
  2. Run the following SQL code to create a new database user account for 3DOrchestrate. 

    USE [master];
GO

CREATE LOGIN <db_login>
    WITH PASSWORD    = '<db_password>',
    CHECK_POLICY     = OFF,
    CHECK_EXPIRATION = OFF,
    DEFAULT_DATABASE = [<db_name>];
GO

USE [<db_name>];  
GO  

CREATE USER [<db_login>] FOR LOGIN [<db_login>];  
GO 

GRANT CREATE TABLE,
      ALTER,
      REFERENCES,
      SELECT,
      INSERT,
      UPDATE,
      DELETE
ON DATABASE::<db_name> TO <db_login>;
GO

    In this code, substitute your desired values for the following:

    <db_name>
    Name of the 3DOrchestrate database you just created.
    <db_login>
    Login name for the new user account.
    <db_password>
    Password for the new user account.

    Notes:

    • The user script does not grant database administrator (DBA) permissions to the user account.
    • In SQL Server, it is necessary to create a LOGIN first, then a USER; they are two different entities with different purposes.
    • The CREATE DATABASE statement purposely omits any COLLATION option, so it picks up the default collation specified during the SQL Server installation.

    • All string literals surrounded by simple quotes use the default character encoding scheme. To force SQL Server to use Unicode for these string literals, prepend the N prefix as explained in the following Microsoft support article:

      https://support.microsoft.com/en-us/kb/239530

      For example, 

      NAME = '<db_name>'

      becomes

      NAME = N'<db_name>'

  3. Do the following to install and grant XA transaction rights to the new database user account:
    1. Run the following SQL statements: 

      EXEC sp_sqljdbc_xa_install;
EXEC sp_addrolemember [SqlJDBCXAUser], [<db_login>];
GO

    2. When the SQL statements run to completion, search for the following error messages in the Catalina logs: 

      [main] org.apache.geronimo.transaction.manager.TransactionImpl.enlistResource Unable to enlist XAResource  XAResourceID:1, errorCode: -3
javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection.

    If the errors are present, rerun the sp_sqljdbc_xa_install and sp_addrolemember SQL statements.

Create the Database Tables for the 3DOrchestrate Application

  • Microsoft SQL Management Studio methodology:
    1. Use the database login and password you created above to connect to the database using the Microsoft SQL Server Management Studio console.
    2. Select the newly created database (<db_name>) from the Available Databases list on the toolbar.
    3. Use the open file command and find the createtables.sql script in the 3DOrchestrate Distribution Server installation:

      <server_install_dir>\win_b64\reffiles\SMAExeServer\database\mssql\createtables.sql

    4. Click Open to execute this script.

      You must execute the createtables.sql script with the 3DOrchestrate user login you defined (<db_login>). Do not execute this script as the sa user account or any other administrative user.

    Microsoft sqlcmd utility methodology:

    1. Use the database login and password you created above along with the database name as command-line arguments for sqlcmd.

    2. Append the –I option to the createtables.sql script to enable quoted identifiers. For example:

      ./sqlcmd -d test -U testUSER -P 'testUSER' -i 
          /a/COS/linux_a64/reffiles/SMAExeServer/database/mssql/createtables.sql -I

    Open TCP Connections on Port 1433

    The 3DOrchestrate Distribution Server application communicates with the SQL Server database through port 1433.

    1. Start the Microsoft SQL Server Configuration Manager.
    2. Select SQL Server Network Configuration > Protocols for SQLSERVER2012 in the left-hand pane.
    3. Right-click on TCP/IP in the right-hand pane and select Properties.
    4. Under the Protocol tab, set Enabled to Yes.
    5. Under the IP Addresses tab, scroll down to find the IPAll properties.

      Set the TCP Port to 1433.

    6. Restart the SQL Server database.

    Copy the JBDC Driver and Deploy the Application

    1. Start the deployment utility GUI from the command line by executing the following batch file:

      <server_install_dir>\win_b64\reffiles\SMAExeServer\deploy\smaexe-deploy.bat

    2. In the General Settings tab, choose MSSQL Preconfigured as the database type.
    3. Switch to the MSSQL Settings Preconfigured tab and enter all required information. The MSSQL Connect Port should be 1433.
    4. Switch to the Deploy tab. Clear all options, then select only the following Server Actions:

      • Build EAR/WAR files
      • Create profile
      • Configure server
      • Deploy application

      Confirm that no Combined Actions or Database Actions are selected.

      Click Execute.

    5. In the Deploy tab of the deployment utility, clear all options and then select Start server.

      Click Execute.

    Enable Encryption for Data Transfer between 3DOrchestrate and MSSQL Server

    You can enable encrypted communications for data transfer between 3DOrchestrate and an MSSQL Server. The MSSQL host must be a fully qualified domain name (FQDN) of the MSSQL server.

    1. Start the deployment utility GUI from the command line by executing the following batch file: 

      <server_install_dir>\win_b64\reffiles\SMAExeServer\deploy\smaexe-deploy.bat

    2. In the MSSQL Preconfigured tab, select Encrypt connection.