Configure API Portal with Microsoft® SQL Server 2016 or Microsoft® SQL Server 2014

Using API Portal 10.3 | API Portal Administrator's Guide | Configuring API Portal | Configuring API Portal with External Databases | Configure API Portal with Microsoft® SQL Server 2016 or Microsoft® SQL Server 2014

To customize the API Portal with Microsoft® SQL Server 2016 or Microsoft® SQL Server 2014 you need the following components:

An operating Microsoft® SQL Server 2016 or Microsoft® SQL Server 2014 database.

The Microsoft® JDBC Driver sqljdbc4.jar. You can download this driver from the Microsoft Web Site to a directory of your choice.

SQL scripts and all additional files. These scripts can be downloaded from the ARIS Download Center.

The SQL scripts creates a database and necessary database objects required by the API Portal components.

Note: Database scripts should be executed from the machine where sqlcmd client is running.

To configure API Portal with Microsoft ® SQL Server 2016 or Microsoft® SQL Server 2014

1. Back up data from API Portal.

a. Log on to API Portal as an Administrator.

b. Click

in the right top corner of the API Portal window to display the menu options.

c. Click Administration > Manage data.

d. Select Backup.

e. Select the relevant options and click Backup.

A success message appears when the backup process is completed. The backup file with an extension .acb is created and saved in the downloads section. You can move the file and save it in another location of your choice.

2. Open API Portal Cloud Controller and execute the following commands.

stopall
deconfigure postgres_s
start zoo_s

3. To configure envset.bat, login to the machine where MSSQL server is running and go to the directory where the script files are downloaded from ARIS Download centre. Scripts are present in the folder, download_root_folder\ARIS.xxx.DatabaseScripts\DatabaseScripts\Design&ConnectServer\mssql

Note: For API Portal 10.3, download ARIS.10.0.SR6.DatabaseScripts.zip file.

4. Open the envset.bat file, modify the following fields, and save the file:

SET MSSQL_SAG_MSSQL_SERVER_NAME=Server Name (Machine name where MSSQL server is running)

SET MSSQL_SAG_MSSQL_LOGIN_NAME=User Name (MSSQL DBA username)

SET MSSQL_SAG_DATABASE_NAME=Database name (Ex: Portal103DB)

SET MSSQL_SAG_FILEGROUP_FILE_DIR=MS SQL file path (for example, C:\msqldata\<Database name>

SET MSSQL_SAG_APP_USER=UserName (Application username which will be created. Ex: dbuser)

SET MSSQL_SAG_APP_PWD=Password (Password of the application user. Ex: dbuser123)

5. Before running the database scripts ensure that the Microsoft SQL Server client (sqlcmd) is available in the command prompt. Run the inst.bat file; this drops the existing schema and creates new schema. Run the following 2 commands, which will create 2 schemas, one for the master tenant and one for the default tenant.

cip_create_schema_for_tenant.bat CIP_MASTER

cip_create_schema_for_tenant.bat CIP_DEFAULT

6. Switch to the machine where API Portal is installed. Add the JDBC drivers to API Portal classpath.

a. Start API Portal Cloud Controller.

b. Run the following command:

enhance apiportalbundle_s with commonsClasspath
local file "sqljdbc_jar_location"

Example:

ACC + localhost> enhance apiportalbundle_s with commonsClasspath
local file "C:/jars/sqlserver/sqljdbc4.jar"

7. Register the external service database.

a. In API Portal Cloud Controller, run the following commands:

register external service db url="jdbc:sqlserver://
servername:port;
DatabaseName=databasename"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
username="<username>" password="<password>"
maxIdle=15 maxActive=300 maxWait=10000
removeAbandoned=false removeAbandonedTimeout=600
logAbandoned=true defaultAutoCommit=false
rollbackOnReturn=true host=servername jmxEnabled=true
database.admin.user="<database.admin.user>"
database.admin.password="<database.admin.password>"

An external service identifier is returned once the above command is executed, for example, it returns the service id as db0000000000.

b. Run the following command to assign the service to the default and master tenants:

assign tenant default to service db0000000000 com.aris.cip.db.schema=CIP_DEFAULT
assign tenant master to service db0000000000 com.aris.cip.db.schema=CIP_MASTER

8. In API Portal Cloud Controller, run the following command to start all the runnables:

startall

9. Restore the backed up data.

a. Log on to API Portal as an Administrator.

b. Click

in the right top corner of the API Portal window to display the menu options.

c. Click Administration > Manage data.

d. Select Restore.

e. Click Upload and select the relevant backup file to be uploaded.

f. Select the relevant options and click Restore.

A success message appears when the restore process is completed.