SQL Migration Error to MS SQL

Hi,  I am trying to migrate our teamcity server data to MS SQL.  I am running the MigrateDB batch file with migrate option.  I get an error connecting.


Using propeties file: dbMigration.properties
Connecting to the source database: jdbc:hsqldb:file:F:/BuildServer
Connecting to the target database: jdbc:jtds:sqlserver://tnw2s-build01/Teamcity
[2009-12-07 12:42:44,987]  ERROR -     jetbrains.buildServer.dbmt - Error connec
ting to the target database (URL:jdbc:jtds:sqlserver://tnw2s-build01/Teamcity, u
ser:teamcity, password:Te@mc1ty), make sure the specified values are correct and
modify them in the properties file if necessary

I am attaching my dbmigration.properties file along with the teamcity-dbmt.log

I am able to connect with those exact parameters via MS SQL Management Studio.

I am using jtds-1.2.4.jar.


what am I missing?



Attachment(s):
teamcity-dbmt.log.zip
dbMigration.properties.zip
12 comments
Comment actions Permalink

Lance,

how can I see from the log that the migration tool could not connect to MS SQL server. Could you please describe the case in details:
- what kind of Windows do you use,
- whether TeamCity is installed on the same host as MS SQL server was or not,
- have you tried to connect with MS SQL Management Studio from the host where TeamCity installed or where MS SQL server installed,
- have you got installed any additional firewalls or such programs?

Thanks

0
Comment actions Permalink

    I will try to answer your questions the best that I can.

We are definetly using MS SQL 2008.
TeamCity is installed on a different host than the build server. Both our build server and this build machine with MS SQL 2008 are in the same domain.  The MS SQL server is also our primary Build agent so these machines talk via port 80 and 9090 frequently.  No firewall between the two.

The MS SQL Server is the Default instance and is named  tnw2s-build01. The database name is teamcity


I did not connect from the build server to the tnw2s-build01 MS SQL instance since I did not have sql installed on the build server.   I am going to install and check that.

Also, I did not have the database.properties file in the buildserver config folder (buildserver/config) as the instructions said the migrate would create one and place it there.

0
Comment actions Permalink

Lance,

is MS SQL 2008 Express edition or not Express edition? In case of Express edition, the remote connection is defaultly disabled. Follow the links how to enable remote connection:

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

 
Then, try to check connection to SQL server from the host where TeamCity is installed (use MS SQL Management Studio, for instance).

Best regards!

0
Comment actions Permalink

We have MS SQL 2008 Enterprise edition on the box tnw2s-build01 which is the box we are trying to connect to.  I am also able to connect from the build server machine to the MS SQL box via SQL Management Studio using the exact same credentials.

0
Comment actions Permalink

Lance,

please ensure that you can connect to MS SQL server from the host where TeamCity server is installed (not build agents).

Thanks

0
Comment actions Permalink

Leonid,  

Thanks for the help.     Here is my scenario besides all the information I have already supplied.


Step 1:  I am NOT able to connect from the host where Team City Server is installed to the SQL Server box via the migrate tool and the jdts driver.   The errors have been supplied above.


Step 2: I am able to connect from the host where team city server is installed to the SQL Server box via MS SQL management studio using the exact same credentials used in step 1.


Is there any other thing I can do to help diagnose this down further.

0
Comment actions Permalink

Also this is our db connection specifics from the dbmigration.properties file

# Internal TeamCity database
     sourceDriver=org.hsqldb.jdbcDriver
     sourceURL=jdbc:hsqldb:file:F:/BuildServer
     sourceUser=sa
     sourcePassword=''



# MSSQL 2005 / 2008 via jTDS
     targetDriver=net.sourceforge.jtds.jdbc.Driver
     targetURL=jdbc:jtds:sqlserver://tnw2s-build01/Teamcity
     targetUser=teamcity
     targetPassword=Te@mc1ty


tnw2s-build01 is the build server / default sql instance name

Teamcity is the database

teamcity is the user
Te@mc1ty is the pasword

0
Comment actions Permalink

Lance,

could you please try to specify the MS SQL server port in the "database.properties" file?
The line should look like

targetURL=jdbc:jtds:sqlserver://tnw2s-build01:1433/Teamcity

The default port is 1433 (but the MS SQL server can be configured to listen another port).

P.S. Ensure that lines in the "database.properties" file have no tailing spaces.

Thanks

0
Comment actions Permalink

HiLeonid,    It was the port tha caused that problem.    Here is another error after getting past that one.

I also recreated the teamcity db with SQL_LATIN1_General_CP1_CI_AI  collation.   Looking at the teamcity db after the error and I can see that many db objects where created.

Any thoughts or ideas?   below is the error message.    The table action_history does exist in the Teamcity db.



[2009-12-09 10:37:07,129]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing source database connection...
[2009-12-09 10:37:07,129]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing connection...
[2009-12-09 10:37:07,160]   INFO [           main] -     jetbrains.buildServer.dbmt - Done.
[2009-12-09 10:38:05,489]   INFO [           main] -     jetbrains.buildServer.dbmt - Using propeties file: dbMigration.properties
[2009-12-09 10:38:05,505]   INFO [           main] -     jetbrains.buildServer.dbmt - Connecting to the source database: jdbc:hsqldb:file:F:/BuildServer
[2009-12-09 10:38:05,739]   INFO [           main] -     jetbrains.buildServer.dbmt - Connecting to the target database: jdbc:jtds:sqlserver://TNW2S-BUILD01:54539/TEAMCITY
[2009-12-09 10:38:06,146]   INFO [           main] -     jetbrains.buildServer.dbmt - Initializing schema...
[2009-12-09 10:38:07,583]   INFO [           main] -     jetbrains.buildServer.dbmt - Saving C:\Program Files\TeamCity\bin\TC-export-1260376686458\targetSchema.sql
[2009-12-09 10:38:07,614]   INFO [           main] -     jetbrains.buildServer.dbmt - Tables to be converted (58): [[action_history, agent, agent_build_type_access, agent_sources_version, build_checkout_rules, build_data_storage, build_dependency, build_labels, build_queue, build_revisions, build_runtime_info, build_state, build_type_vcs_change, canceled_info, comments, compiler_output, default_build_parameters, deleted_build_types, downloaded_artifacts, failed_tests_output, history, ignored_tests, light_history, paused_build_types, personal_build_relative_path, personal_vcs_changes, personal_vcs_history, projects, remember_me, remote_run_build_types, run_configuration_policy, running, server, server_property, stats, tc_build_tags, test_failure_rate, test_info, test_info_trunk, test_names, user_blocks, user_build_parameters, user_notification_events, user_projects_order, user_projects_visibility, user_property, user_roles, user_watch_type, usergroup_notification_events, usergroup_roles, usergroup_subgroups, usergroup_users, usergroup_watch_type, usergroups, users, vcs_changes, vcs_history, vcs_root_versions]]
[2009-12-09 10:38:07,614]   INFO [           main] -     jetbrains.buildServer.dbmt - Exporting to C:\Program Files\TeamCity\bin\TC-export-1260376686458
[2009-12-09 10:38:07,708]   INFO [           main] -     jetbrains.buildServer.dbmt - Saving C:\Program Files\TeamCity\bin\TC-export-1260376686458\sourceSchema.sql
[2009-12-09 10:38:07,724]  ERROR [           main] -     jetbrains.buildServer.dbmt - Failed to export source database

[2009-12-09 10:38:07,724]  DEBUG [           main] -     jetbrains.buildServer.dbmt - java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
java.sql.SQLException: Table not found in statement [SELECT COUNT(*) FROM action_history]
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
    at jetbrains.buildServer.serverSide.DBMTool.dumpSizes(DBMTool.java:237)
    at jetbrains.buildServer.serverSide.DBMTool.exportSourceDatabase(DBMTool.java:275)
    at jetbrains.buildServer.serverSide.DBMTool.main(DBMTool.java:90)
[2009-12-09 10:38:07,724]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing source database connection...
[2009-12-09 10:38:07,724]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing connection...
[2009-12-09 10:38:07,755]   INFO [           main] -     jetbrains.buildServer.dbmt - Closing target database connection...
[2009-12-09 10:38:07,755]   INFO [           main] -     jetbrains.buildServer.dbmt - Done.

0
Comment actions Permalink

Hi Lance,

I'm glad the first problem has been solved.

The second problem seems is in the following string of the "migrationDB.properties" file:

sourceURL=jdbc:hsqldb:file:F:/BuildServer

The last part of this property value must be the fully qualified path to the HSQL datafile
with name of the file but without file extension. With unix-style slashes.

For example, if the HSQL data file path is

F:\BuildServer\DATA\system\buildserver.data

the property should look like

sourceURL=jdbc:hsqldb:file:F:/BuildServer/DATA/system/buildserver

Try to specify it carefully.

Best regards!

0
Comment actions Permalink

Hi Leonid,   I get the same error.   I am attaching my database.properties and the log file.

My buildserver.data file is in f:\buildserver\system\ folder

i have tried

sourceURL=jdbc:hsqldb:file:F:/BuildServer/system
sourceURL=jdbc:hsqldb:file:F:/BuildServer/system/buildserver.data
sourceURL=jdbc:hsqldb:file:F:\BuildServer\system


none have worked.   should my password for the hsqldb database be blank  for SA?


what next... we are close.



Attachment(s):
teamcity-dbmt.log.zip
dbMigration.properties.zip
0
Comment actions Permalink

Hi Lance,

please try

sourceURL=jdbc:hsqldb:file:F:/BuildServer/system/buildserver

As of password - yes, it should be blank.

Best regards

0

Please sign in to leave a comment.