Appendix : Administration : Getting Started with the Business Analytics Server : Move the Business Analytics repository to a robust database solution : Move Business Analytics repository to Microsoft SQL Server
Move Business Analytics repository to Microsoft SQL Server
1. If you are using your LDAP Directory as the Business Analytics User Repository, make sure that at least one user in your LDAP Directory has administrator privileges for Business Analytics before you move the Business Analytics Repository. See Grant User Access to Business Analytics with Built-in Groups for instructions.
When the Business Analytics User Repository is your LDAP Directory, the default administrator account (Administrator user) is disabled.
2. If you are hosting the Business Analytics Repository or MashZone Repository in a new database, create the database following SQL Server documentation. Keep the following points in mind:
*Make sure this database is supported by Business Analytics. See Additional Business Analytics System and Software Requirements for details.
Note:  
The JTDS driver for SQL Server is the recommended driver for use with Business Analytics and SQL Server databases due to known issues with the default SQL Server JDBC driver.
*If you want Business Analytics to support international characters in meta-data for artifacts, make sure the database uses the UTF-16 character encoding and collation. See documentation for your database for specific instructions.
*It is a best practice to require passwords for every database account that can access the Business Analytics Repository.
3. Start the database that will become host to the Business Analytics Repository, if it is not already up.
4. Using the SQL tool for the database that will be host, add Business Analytics Repository tables with the scripts shown below from the corresponding folder in MashZoneNG-install/prestorepository/mssqldb:
*createDBTables.txt for MetaData and the default User Repository
*createSnapsTables.sql for Snapshots
*createSchedulerTables.sql for Scheduler
This folder also contains scripts to drop the corresponding Business Analytics Repository tables, if needed.
5. Copy the JAR file for the JDBC driver for your database to the following folder for each Business Analytics Server that uses this Business Analytics Repository:
MashZoneNG-install/apache-tomee-jaxrs/lib
6. Replace the JAR for the Business Analytics Repository:
a. Remove the web-apps-home/mashzone/WEB-INF/lib/jackbe-presto-rds-postgresql-derby.jar JAR file for each Business Analytics Server that uses this Business Analytics Repository. You can delete this JAR or simply move it to a folder that is not in the classpath for the application server that hosts Business Analytics.
b. Copy this JAR file:
MashZoneNG-install/prestorepository/jackbe-presto-rds-oracle-mysql-mssql.jar
To the web-apps-home/mashzone/WEB-INF/lib folder.
7. Update snapshot scheduler configuration for the Business Analytics Server:
a. In the text editor of your choice, open the applicationContext-scheduler.xml file in the webapps-home/mashzone/WEB-INF/classes/ folder for the Business Analytics Server.
b. Find the bean for org.springframework.scheduling.quartz.SchedulerFactoryBean.
c. Update the org.quartz.jobStore.driverDelegateClass property to the org.quartz.impl.jdbcjobstore.MSSQLDelegate appropriate delegate for this database:
d. Save this change.
e. If this is a clustered environment, copy the updated applicationContext-scheduler.xml configuration file to each Business Analytics Server in the cluster.
8. Open the MashZoneNG-install/apache-tomee-jaxrs/conf/tomee.xml configuration file in the text editor of your choice.
9. For the Business Analytics Repository, edit the <Resource> element with an ID of MashzoneNextGenRepository and:
a. Update the JDBC driver, URL and credential properties:
<Resource id="MashzoneNextGenRepository" type="DataSource">
JdbcDriver net.sourceforge.jtds.jdbc.Driver
JdbcUrl jdbc:jtds:sqlserver://host-name:port/database
UserName username
Password password
JtaManaged = false
</Resource>
The JTA managed property must be false.
b. If needed, update optional properties. See TomEE Datasource Properties for a complete list of optional properties and information on defaults.
Some common properties you may need to set include:
*validationQuery = select 1
*Common tuning properties for connections pools. See Tuning the Business Analytics Repository Connection Pool.
10. Save your changes to this file.
If the Business Analytics Server does not start up successfully, see Troubleshooting Connections to the Business Analytics Repository for suggestions.
11. Open the rdsJDBC.properties configuration file, from the web-apps-home/mashzone/WEB-INF/classes folder, in the text editor of your choice and:
a. Comment out the property definitions for the Derby database.
b. Uncomment the properties for MSSQL and update these properties to match the JNDI configuration your previously updated for TomEE.
c. Save your changes to this file.
You must keep JDBC and JNDI configuration synchronized for the Business Analytics Repository. The application server is using JNDI to connect to the Business Analytics Repository, but some components still use JDBC information.
12. Restart the Business Analytics Server to apply these changes.
If the Business Analytics Server wedoes not start up successfully, see Troubleshooting Connections to the Business Analytics Repository for suggestions.
13. Update connection information for the Snapshots Repository:
a. Open Business Analytics Hub and login.
b. Add a JDBC driver for the new database that should host the Snapshots Repository. See Add or Manage JDBC Drivers for instructions on adding JDBC drivers.
c. Expand the JDBC Configuration menu, if needed, and select Datasources.
d. Select SnapshotDatasource and click Edit.
e. Update configuration to point to the new database. See Add a Data Source for information on specific configuration properties.
f. Click Save.
14. Restart the Business Analytics Server to apply these changes.
15. Load macros required for the Snapshot feature in Business Analytics:
a. Open a command or terminal window and move to the MashZoneNG-install/presto-cli/bin folder.
b. Enter the appropriate command, shown below, for your operating system:
For Windows
For Linux, OS/X or UNIX
publish-global-macros.bat -u Administrator -p manage -url http://app-server:port/mashzone/edge/api
./publish-global-macros -u Administrator -p manage -url http://app-server:port/mashzone/edge/api
Copyright © 2013-2017 Software AG, Darmstadt, Germany. (Innovation Release)

Product LogoContact Support   |   Community   |   Feedback