Collaboration for Business Console - Preparing SQL Server Database

Knowledge base
,
1

Plugin insertion failed: Could not find plugin TablePlugin insertion failed: Could not find plugin Table

1. Pre-requisites #

To configure MS SQL Server, you need the following components:

  •   Working Microsoft SQL Server database system.
  •  JDBC driverJAR file for MS SQL Server. The examples mention sqljdbc4.jar as the JDBC driver JAR file. If you are using a different driver JAR file, make sure you use the correct JAR filename in the commands. You can download the driver JAR file from the MS SQL Server website to any directory on a physical drive  (not a mounted drive).
  • SQL scripts and all additional files. These scripts and files are located in <Software AG_directory>\ecp\server\scripts\mssql directory.
  • Connection to SQL Server DBMS from the system on which you are running the scripts.If SQL server is running in a remote machine (not in the same machine where Collaboration is installed), then do the following:
    a.    Copy the DB server script folder to the Database server machine.
    b.    Run the DB server scripts using the command prompt.

2. Configuring envset.bat #

Before you create an application user and create an empty schema by using the scripts, configure the settings used by the inst.bat script.

1.    Edit the envset.bat file to specify the connection data for the SQL Server instance. Locate the following lines in the file, and replace the variables specified in angular brackets with the settings appropriate for your environment:

SET MSSQL_SAG_MSSQL_SERVER_NAME=<SQLServerHostname(Or)IPAddress>

SET MSSQL_SAG_MSSQL_LOGIN_NAME=<SQLServerHostname(Or)IPAddressS>\<SQL

ServerAdminUserName>

For example, if your SQL Server instance is running on ‘mysqlserver.example.com’ machine, and  the administrator user name is ‘Admin’, you would use these settings: 

SET MSSQL_SAG_MSSQL_SERVER_NAME=mysqlserver.example.com
SET MSSQL_SAG_MSSQL_LOGIN_NAME=mysqlserver.example.com\Admin

2.    Edit the following lines to change the username and password of the application user. 

SET MSSQL_SAG_APP_USER=<AppUserName>
SET MSSQL_SAG_APP_PWD=<AppUserPassword>

For example, if you want to create an application user with username “ecpuser”  and password  “TopSecret”, you would specify: 

SET MSSQL_SAG_APP_USER=ecpuser
SET MSSQL_SAG_APP_PWD=TopSecret

Collaboration uses this user to access the DBMS.

3.    Edit the following line to change the database name. 

SET MSSQL_SAG_DATABASE_NAME=<Database Name>

For example, if you want to name the database as “COLLABORATION”, specify: 

SET MSSQL_SAG_DATABASE_NAME=COLLABORATION

4.    Edit the following line to change the filegroup location. 

SET MSSQL_SAG_FILEGROUP_FILE_DIR=<File Group Folder location>

For example, if you would like to create the file group specific files in the C:\msqldata\collaboration directory, specify: 

SET MSSQL_SAG_FILEGROUP_FILE_DIR= C:\msqldata\collaboration

3. Creating Application User and Schema #

After you modify the envset.bat file according to your environment, perform these steps:

1.    Run the prepare_database.bat file to create the database and the application user.2.    Run the create_schema_for_tenant.bat  file to create the schema for the default tenant, and pass the schema name as parameter. It is recommended that you do not change this schema name.3.    Run the cip_create_schema_for_tenant.bat file, and pass the schema name as parameter: 

cip_create_schema_for_tenant.bat <schemaUserName>

For example, if you have not changed the default schema user name, 

specify:cip_create_schema_for_tenant.bat ecp_default

4. Adding SQL Server JDBC Driver #

SQL Server JDBC driver is required for connection between Collaboration and the SQL Server.

Per-form the following steps:

1.    Copy the SQL Server JDBC driver JAR file to a local physical drive, not a mounted volume.

2.    Click Start > All Programs > Software AG > Administration > Collaboration Cloud Controller 9.8 to start the Cloud Controller. You will get this prompt:ACC+ localhost>

3.    Stop Collaboration, if it is running.

4.    Type command: 

enhance ecp with commonsClasspath local file <pathToJDBCDriverFile>

Where <pathToJDBCDriverFile> is absolute path of the SQL Server JDBC driver JAR file.

For example, if the SQL Server JDBC driver JAR file is located in c:\temp\sqljdbc4.jar file, type command: 

enhance ecp with commonsClasspath local file c:\temp\sqljdbc4.jar

If the command was successful, you get this message: 

Successfully executed Local Enhancement c:/temp/sqljdbc4.jar for instance ecp.

5.    Registering SQL Server DBMS with Collaboration #

To register the SQL Server DBMS in the Collaboration service registry, start the registry, and perform these steps:

1.    Click Start > All Programs > Software AG > Administration > Collaboration Cloud Controller 9.8 to start the Collaboration Cloud Controller.

2.    At the ACC+ localhost> prompt, type the command: 

start zoo

3.    Type command: 

list

You get an output similar to: 

ACC+ localhost>list
On node localhost 4 runnables are installed.
zoo        : STARTED (com.aris.runnables.zookeeper-run-prod-98.0.0)
ecp        : STOPPED (com.aris.runnables.ecp-run-prod-98.0.0)
elastic      : STOPPED (com.aris.runnables.elasticsearch-run-prod-98.0.0)
umcadmin     : STOPPED (com.aris.umcadmin.y-umcadmin-run-prod-98.0.0)

The “zoo” component should be shown as STARTED. If not, wait a little longer, and retype “list” until zoo is shown as STARTED.

4.    To register the SQL Server DBMS, type the command: 

register external service DB_ECP
host=<SQLServerHostnameOrIP>
url=
"jdbc:sqlserver://<SQLServerHostnameOrIP>:<SQLServerPort>;databaseName=<DatabaseName>;instanceName=<InstanceName>"
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver jmxEnabled=true
maxActive=100  maxIdle=15 logAbandoned=true rollbackOnReturn=true maxWait=10000 removeAbandoned=false defaultAutoCommit=false
username = <AppUserName>
password = <AppUserPwd>//

Where <SQLServerHostnameOrIP>,<SQLServerPort>, <DatabaseName>, <InstanceName>, <AppUserName>, and <AppUserPwd> specify the hostname (or IP address), port, database name, database instance name, application user name, and application user password (set  in the envset.bat file).

For example, if SQL Server DBMS is running on mysqlserver.example.com  machine on port 1433, with MSSQL instance name, and database name is COLLABORATION, the command would be: 

register external service DB_ECP
host=mysqlserver.example.com
url=  
 "jdbc:sqlserver://mysqlserver.example.com:1433;databaseName=COLLABORATION;instanceName=MSSQL"
 driverClassName= com.microsoft.sqlserver.jdbc.SQLServerDriver jmxEnabled=true ////
maxActive=100  maxIdle=15 logAbandoned=true
rollbackOnReturn=true maxWait=10000
removeAbandoned=false defaultAutoCommit=false
username = ecpuser
password = TopSecret//

Note: Type the command in one single continuous line.

If the command completed successfully, you get the following output: 

New external service DB_ECP registered with ID db-ecp0000000000 via node localhost

Note the ID returned by this command.

5.    To specify Collaboration which SWL server schema to use, type command: 

assign tenant default to service <ServiceID> schema=<tenantSchemaName>

Where <ServiceId> is the ID returned by command in the previous step, and <schemaUserName> is the name of the schema user.

For example, if the service ID is db-ecp0000000000, and you used the recommend default schema name “ecp_default”, the command would be: 

assign tenant default to service db-ecp0000000000 schema=”ecp_default”

If the command succeeds, you get an output similar to: 

Successfully assigned tenant default to service db-ecp0000000000.

6.    Type “exit” to exit the ACC console.

See also: #