Migrating to SQL Server

To migrate FishEye/Crucible to an SQL Server database, install SQL Server and follow the steps below. When they are used together, FishEye and Crucible share the same external database.

Before you begin

Check that you are using version of SQL Server that is supported for use with FishEye. See Supported platforms.

An existing Java bug prevents connection with Java 1.6.0_29 and above (including Java 1.7.0). Read more about the issue and possible workarounds here.

Step 1. Install and create an SQL Server database

See the SQL Server Online resources (MSDN) for instructions on how to install and create an SQL Server database.

Please note the following FishEye/Crucible-specific information when installing and creating an SQL Server database:

The JDBC jtds drivers for SQLServer are bundled with FishEye/Crucible. We do not support using the Microsoft distributed jdbc driver.
The FishEye database user must have permission to connect to the database and to create and populate tables.
The database user should not be the database owner, but should be in the db_owner role. (See SQL Server Startup Errors for details.)
Your database server must be configured to use the Latin1_General_CS_AS collation set.
Your database server should be configured to use snapshot mode for the transaction isolation level. To enable snapshot mode, run:
```
ALTER DATABASE crucible
   SET READ_COMMITTED_SNAPSHOT ON;
```
See this and this Microsoft MSDN articles for more information.
Note that it is preferable to run the above command after stopping FishEye/Crucible (and with no other applications connected to the SQL Server database), especially if you find that the alter statement does not complete quickly.

On this page:

Related pages:

Step 2. Configure FishEye/Crucible to use SQL Server and migrate data

In order to migrate to a different database backend, you must create a backup of your SQL data, configure the database and finally import the data via a backup restoration process. This can be done from either the Crucible administration console, which streamlines the process, or via the command line tool which Crucible provides. These two methods are described below. The following resources may be of interest:

Option 1: Migrate using the UI (FishEye/Crucible Administration)

Before you begin:

Note, during the migration process (which will take several minutes, depending on the size of your database and network throughput), the FishEye/Crucible instance will be inaccessible to users and external API clients. Users will see a maintenance screen that informs them of the process.
If you are attempting a migration after a previous migration has failed, you must drop all tables, indexes and constraints before attempting a new migration. This is because the destination database may contain data from the previous migration attempt.
Verify that you have the jtds JDBC driver .jar file in the classpath (by placing the .jar file in FISHEYE_INST/lib).
Ensure that the database user can log in to the database from the machine that FishEye/Crucible is running on and that all the required privileges are present.

Please ensure that you use a SQL Server user account to log into your database, not a Windows user account.

To configure FishEye/Crucible to use SQL Server and migrate data using the administration console:

Navigate to the Database page (under 'System Settings') in FishEye/Crucible's Administration console.
To log in to the Admin area, you can either:
- click Administration at the foot of the page.
- navigate to http://HOSTNAME:8060/admin/, where HOSTNAME is the name of the server on which you installed Fisheye.
Once logged in as an administrator you can also get to the Admin area by clicking the 'cog' menu in the FishEye/Crucible header, and choosing Administration.
Configure FishEye/Crucible to use SQL Server, as follows:
- Choose Edit > Test Connection to verify that FishEye/Crucible can log in to the database.
- Select SQLServer from the Type dropdown (choose SQLServer 2008 even if you are using SQL Server 2012).
- Complete the appropriate fields, replacing the URL (host, port and database name), User Name and Password as required, using the same connection details as used when creating the SQL Server database in Step 1 above.
  NOTE: The default SQL server instance listens on port 1433. If your instance is not the default, use the port number that is associated with your particular instance.
  e.g. URL:jdbc:jtds:sqlserver://localhost:1433;databaseName=your database name here;
Click Test Connection to verify that FishEye/Crucible can log in to the database (see 'Testing the Connection' screenshot below).
Click Save & Migrate Data to start the migration process (see 'Migrating the Database' screenshot below). If the migration fails, FishEye/Crucible will not switch to the new database and will report the problems encountered.

Screenshots: Configuring FishEye/Crucible to use SQL Server and migrating data (click to view full-size images)


2. Migrating Data	1. Configuring the Database

Option 2: Migrate using the command line

To configure FishEye/Crucible to use SQL Server and migrate data using the command line:

Create a backup of the sql data from the FishEye/Crucible instance. Information on how to create a backup can be found at Backing up and restoring FishEye data \ Backing up and restoring Crucible data

Run the following command from the <FishEye installation directory>/bin directory:

$ ./fisheyectl.sh restore --sql \
        --file /path/to/backup.zip \
        --dbtype sqlserver2008 \
        --jdbcurl "jdbc:jtds:sqlserver://hostname:port;databaseName=dbName;" \
        --username crucible \
        --password password

When using SQL Server 2012, use the same parameters as in the code snippet above, so --dbtype should be set to sqlserver2008, even if you are using SQL Server 2012.

When the import is complete, FishEye/Crucible can be started and will use SQLServer.

Child pages