SQL Migration Error to MS SQL

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

    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.

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!

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.

Lance,

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

Thanks

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.

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

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

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.

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!

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.

Hi Lance,

please try

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

As of password - yes, it should be blank.

Best regards