Migration from Win2k3 32bit HSQLDB to Win2k8R2 64bit SQL Server 2K8R2

Hi,

As per the title of the discussion, i have Teamcity 5.1.3 installed on Windows Server 2003 (32bit) and running using the HSQLDB for evaluation purposes. I now need to set up Teamcity (5.1.5) for production on Windows Server 2008 R2 (64bit) with SQL Server 2008 R2 as a backing store. I've read through the documentation and scenarios under "Installation and upgrade" in the online documentation but i can't see exactly how i would achieve what i have to do. I don't think the maintainDB script would do a full automated migration (although i might have understood wrongly). Maybe the only way is the manual backup and restoration but again not sure exactly which steps i should follow and in which order. Could somebody please advise as there are many months worth of work set up in the evaluation version and i would hate to loose all the work.

Thanks

8 comments

Well, in fact maintain.db purpose is to help in such cases. What it basically does is exporting of the database and configuration in some format (mostly CSV) and then import it into other database supported by TeamCity.

0

Hi Pavel,

Thanks for your reply - I'm afraid i'm still not sure how to proceed. The closest to what i'm trying to achieve is potentially what is described under "Migrating to an external Database - Full Migration" (http://confluence.jetbrains.net/display/TCD5/Migrating+to+an+External+Database#MigratingtoanExternalDatabase-FullMigration) so i've set up a temporary database properties file as follows on the Win2K3 machine. The connectionUrl points to the SQL Server and database on the new Win2K8 R2 / SQL Server 2K8 R2 machine.

# This is a sample configuration file for configuring TeamCity to use external database.
# To make it effective, copy it to the "database.properties" file and modify settings
# according to your environment.
# Do not modify this file, it will be overwritten on TeamCity server startup.
# See documentation at http://www.jetbrains.net/confluence/display/TCD5/Setting+up+an+External+Database

# Database: Microsoft SQL server (via jtds driver)   

connectionUrl=jdbc:jtds:sqlserver://buildsrv01:1433/teamcity
connectionProperties.user=teamcity
connectionProperties.password=[password]

# The maximum number of connections TeamCity can open on database server.
maxConnections=50

# Specifies whether TeamCity should check each jdbc connection before use.
# It reduces the system performace but avoids cases when the system uses
# a connection that was closed by database server due to time out or this
# connection was broken at the previous operation.
testOnBorrow=true


The documentation says the following:

"Upon the successful completion of the database migration, a database.properties file will be replaced with the file specified via -T option.  The old database.properties file will be automatically re-named  in the following format: database.properties.before.<timestamp>."

This i guess will be on the Win2K3 machine so i could move it to the Win2K8 machine.

HOWEVER...

when i run "maintainDB.cmd migrate -T c:\.BuildServer\config\database.mssql_temp.properties"


i get

"java" is not recognised as an internal or external command.

Having looked at maintainDB.cmd and findJava.bat this seems to be because findJava.bat is trying to use %TEAMCITY_HOME_DIR% which has not been set in either maintainDB.cmd or findJava.bat.

Is this the way to be proceeding and if so should i have done something that would have set the %TEAMCITY_HOME_DIR% environment variable?

Thanks

0

Did you try to start maintaindb right from the TeamCity bin directory (when current directory is TeamCity bin)?

0

Yeap, that's where i'm running it from and i get the error about %TEAMCITY_HOME_DIR%

0

Please try to set environment variable JAVA_HOME to a directory where Java is installed.

0

OK, having manually set JAVA_HOME, when i run the maintainDB.cmd migrate -T c:\.BuildServer\config\database.mssql_temp.properties on the Win2K3 machine i now get the following error message:

TeamCity maintenance tool. Copyright 2010 JetBrains s.r.o. All Rights Reserved.

Command line arguments: migrate -T c:\.BuildServer\config\database.mssql_temp.properties
Using TeamCity data directory: C:\Documents and Settings\BSDUser\.BuildServer


Cannot proceed with 'migrate' command: Source database not specified and not found.
Done.


I couldn't find anything in the documentation about specifying a source database. As a reminder, the Teamcity installation on the Win2K3 machine from which i want to migrate is running on HSQLDB.

UPDATE: Turns out that as per the comments on the documentation page (http://confluence.jetbrains.net/display/TCD5/Migrating+to+an+External+Database#MigratingtoanExternalDatabase-FullMigration) the following need to be set:

TEAMCITY_DATA_PATH  Path to BuildServer directory
TEAMCITY_APP_DIR    Path to TeamCity application directory (default is  <TeamCity_home>\webapps\ROOT)


I think it would be a good idea if this was included in the actual documentation.

0

Hi

First of all, I would say that in TC 5.x upgrading and migrating are two separate steps. You can first upgrade your installation from 5.1.3 up to 5.1.5 and then migrate to MS SQL or first migrate to MS SQL and then upgrade to 5.1.5.

In order to upgared TeamCity installation from 5.1.3 to 5.1.5 just install the TC 5.1.5 version specifying the same TeamCity Data Directory and run it - the directory data (including the internal database) will be upgraded automatically. See the manual http://confluence.jetbrains.net/display/TCD5/Upgrade for details.

Then, if you're going to relocate your TeamCity installation into another computer and migrating to another RDBMS, the best way to do this is to backup the source data and then to restore them into the target installation.

So, backup your data using

maintainDB backup -C -D -L -P -F mytcdata
command. It will make a file with copy of all data (excluding artifacts). If maintainDB could not gind the TC data directory you can use the -A option:

maintainDB backup -A C:\myDir\.BuildServer -C -D -L -P -F mytcdata
After backup is done you can find a backup file (named mytcdata.zip) in the 'backup' subdirectory.

Note: always run the maintainDB tool being in the <TC_Home>/bin directory (where the maintainDB.cmd file is located).

Then, install TeamCity onto the other box you're going relocate TC to. Configure the 'database.properties' file (see http://confluence.jetbrains.net/display/TCD5/Setting+up+an+External+Database#SettingupanExternalDatabase-MicrosoftSQLServer for details).
You can start the new installation to ensure that all have been configuret perfrectly and TC does work with MS SQL database.

After TC shut down it is needed to clean data: (1) remove all files except the 'database.properties' and jdbc drivers from the TC data directory (on the new installation), (2) drop all tables from the new database.

Now you can restore data for the new installation.
Copy the mytcdata.zip file into the same directory where the maintainDB.cmd file is located and use the following command:

Use migrateDB for it:

migrateDB restore -F mytcdata -T <new_database_properties_file>

Now, if the restoring has finished successfull, you can start TeamCity.

Enjoy!

0

Hi Leonid,

What you very clearly describe here is exactly what i ended up having to implement (prior to reading your post) by working my way through the various error messages that the migration tool and startup of the server were giving me. I suppose it's encouraging to read that what i ended up having to do is actually the required/recommended way of doing it. Unfortunately though, the documentation is not as clear as it could be about these things. For example, it doesn't mention anywhere that having installed on the new server and set up a database, i would then have to clear certain filesystem folders and drop all database tables manually for the migration script to work. I ended up doing precisely that because i had exhausted all other options but for quite some time i assumed that i had done something wrong (which i hadn't!). I now have the server up and running with all the data successfully migrated.

I am now faced with the error "The SDK for the 'net-3.5' framework is not available or not configured." for pretty much all of my build configurations that used to work perfectly fine on the evaluation server. I will need to try a number of things to resolve that and maybe start a different discussion for the error if i can't get my head around it.

Thanks for your help!

0

Please sign in to leave a comment.