Configure API Portal with Microsoft® SQL Server 2012 or Microsoft® SQL Server 2014
To customize the API Portal with Microsoft® SQL Server 2012 or Microsoft® SQL Server 2014 you need the following components:
An operating Microsoft
® SQL Server 2012 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.
To configure API Portal with Microsoft ® SQL Server 2012 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, 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_Add-ons\Add-ons\DatabaseScripts\Design&ConnectServer\mssql
You can find the following files in this directory:
cip_create_schema_for_tenant.bat envset.bat inst.bat 4. Open the envset.bat file, modify the following fields, and save the file:
SET MSSQL_SAG_MSSQL_SERVER_NAME=
Server NameSET MSSQL_SAG_MSSQL_LOGIN_NAME=
Server Name\User NameSET MSSQL_SAG_DATABASE_NAME=
Database nameSET MSSQL_SAG_FILEGROUP_FILE_DIR=
MS SQL file path in your machine (for example, C:\msqldata\<Database Name
SET MSSQL_SAG_APP_USER=
UserNameSET MSSQL_SAG_APP_PWD=
Password5. Run the database scripts. 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. The following two schema are mandatory; one for the master tenant and one for the default tenant. file.
cip_create_schema_for_tenant.bat CIP_MASTER cip_create_schema_for_tenant.bat CIP_DEFAULT 6. 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 "sqljdbc4.jar location"
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=servernamejmxEnabled=true
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.
