Configuring MSSQL database using the Maestro user interface

To use the Maestro user interface to customize Maestro for a MSSQL database, follow these steps:

1. Make sure Maestro is not running. If it is, stop Maestro. Please see Starting and Stopping Maestro for instructions.
2. Copy the JDBC connector, jtds-1.2.jar, to the [maestro_home]/projectserver/lib directory.
3. Open SQL Server Enterprise Manager, then connect instance.
4. Create a database for Maestro and add a user.
5. Make sure the mapping in [maestro_home]/projectserver/apps/continuum/WEB-INF/classes/META-INF/plexus/application.xml for the database is present and not commented out.

   Users and Builds role-hint connectors

   database section:	Search for:
   users	<role-hint>users</role-hint>
   builds	<role-hint>continuum</role-hint>

   Search for the <component> that contains the <role-hint> for the users and builds (Continuum) databases in the application.xml file and add the mapping shown in the code snippet below. The mapping should be put in the <otherProperties> section for the users component and/or the Continuum component.

     <otherProperties>
       <property>
         <name>javax.jdo.option.Mapping</name>
         <value>mssql</value>
       </property>
       <property>
         <name>org.jpox.autoCreateSchema</name>
         <value>true</value>
       </property>
       [...]

6. Start Maestro. Please see Starting and Stopping Maestro for instructions.
7. Login to Maestro. From a browser, navigate to http://localhost:8080/ and login as the administrative user.
8. On the Project Server page, click Global Settings from the menu on the left.
9. Under the Database Settings section, select Specify database for any or both of the databases you want to put on your external database.

   Note: You can have both the users and builds databases be external to Maestro. However, if you intend to share a database with another instance of Maestro you can only share the users database. The builds database cannot be shared, but can be external.

10. Provide the appropriate values for the fields needed:
    • JDBC URL: jdbc:jtds:sqlserver://[database_host]:1433/[db_name];selectMethod=cursor
    • JDBC Driver Class: net.sourceforge.jtds.jdbc.Driver
    • Username: [db_username]
    • Password: [db_password]

    Take note of the warning on the page saying:

    “Changing the database settings will switch to the new database, not move your existing data. If the new database does not exist, you will be prompted to import data on startup. You should backup the current database before making any changes.”

    If you have not already done so you can backup your current database by clicking on the backup link found in the warning note. Details for the back up and restore procedures are discussed in the Backing Up the Maestro Project Server section.

    Then you will have to go back to step 7 and proceed from there.

11. Click Change Database and Restart.

    Note: Changing the database settings logs you out and restarts Maestro. It might take some time for the application to restart.

These settings are stored in the [maestro_home]/projectserver/apps/root/WEB-INF/jetty-env.xml file.

Configuring MSSQL database manually

To manually configure a MSSQL database, perform the following steps:

1. Stop Maestro. Please see Starting and Stopping Maestro for instructions.
2. Open SQL Server Enterprise Manager, then connect instance.
3. Create a database for Maestro and add a user.
4. Open the jetty-env.xml file for editing. You will edit two locations: one that corresponds to the users database and one that corresponds to the builds database. The jetty-env.xml file is located here:

   [maestro_home]/projectserver/apps/root/WEB-INF/jetty-env.xml

5. Search for the appropriate locations to edit the jetty-env.xml file, as explained in the following table. Then, edit as explained in the next step.

   Users and Builds JDBC connectors

   To edit the section that corresponds to this database:	Search for:
   users	<New id="users" class="org.mortbay.jetty.plus.naming.Resource">
   continuum	<New id="continuum" class="org.mortbay.jetty.plus.naming.Resource">

6. Make the changes in each section (for the users and builds databases) to define the external database. The following code snippet shows the lines that have been edited to configure for a MSSQL database:

   To use a MSSQL database, edit these properties and save the file:

     <New id="users" class="org.mortbay.jetty.plus.naming.Resource">
       <!-- set scope to JVM -->
        <Arg></Arg>
        <Arg>jdbc/users</Arg>
        <Arg>
          <New class="com.exist.maestro.projectserver.util.SecuredDataSourceAdapter">
            <Set name="dataSource">
              <New class="org.apache.commons.dbcp.BasicDataSource">
                <Set name="driverClassName">net.sourceforge.jtds.jdbc.Driver</Set>
                <Set name="url">jdbc:jtds:sqlserver://[database_host]:1433/[db_name];selectMethod=cursor</Set>
                <Set name="username">dbuser</Set>
                <Set name="password">dbpassword</Set>
              </New>
            </Set>
            <!-- Uncomment this when using password obfuscation!
            <Set name="password">OBF:obfuscatedPassword</Set> -->
          </New>
        </Arg>
     </New>
   
     <New id="continuum" class="org.mortbay.jetty.plus.naming.Resource">
       <!-- set scope to JVM -->
        <Arg></Arg>
        <Arg>jdbc/continuum</Arg>
        <Arg>
          <New class="com.exist.maestro.projectserver.util.SecuredDataSourceAdapter">
            <Set name="dataSource">
              <New class="org.apache.commons.dbcp.BasicDataSource">
                <Set name="driverClassName">net.sourceforge.jtds.jdbc.Driver</Set>
                <Set name="url">jdbc:jtds:sqlserver://[database_host]:1433/[db_name];selectMethod=cursor</Set>
                <Set name="username">dbuser</Set>
                <Set name="password">dbpassword</Set>
              </New>
            </Set>
            <!-- Uncomment this when using password obfuscation!
            <Set name="password">OBF:obfuscatedPassword</Set> -->
          </New>
        </Arg>
      </New>

7. Copy the MSSQL JDBC connector, jtds-1.2.jar, to [maestro_home]/projectserver/lib.
8. Make sure the mapping in [[maestro_home]/projectserver/apps/continuum/WEB-INF/classes/META-INF/plexus/application.xml for the database ("Users and Builds role-hint connectors" table above) is present and not commented out.

   Search for the <component> that contains the <role-hint> for the users and builds (Continuum) databases in the application.xml file and add the mapping shown in the code snippet below. The mapping should be put in the <otherProperties> section for the users component and/or the Continuum component.

    <otherProperties>
      <property>
        <name>javax.jdo.option.Mapping</name>
        <value>mssql</value>
      </property>
      <property>
        <name>org.jpox.autoCreateSchema</name>
        <value>true</value>
      </property>
    [...]

9. Start Maestro. Please see Starting and Stopping Maestro for instructions.