SQL Migration Fails

 
Author
Message
Former Community Manager
980
 
Hi mks,

Does the upgrade get pass the SQL 2008 R2 installation, or does it fail before even that time? Also, are you upgrading the free edition?

Thanks.

Michael
mks
SysAider
22
 
Hi

Sorry it fails at the point of migration.

So all the SQL stuff installs and then the moment it tries to start the migration it just comes up and says failed. There is no delay between the SQL installer finishing and the migration failing.

We have a full licensed copy.


Just tried a complete uninstall and then reinstalled up until 9.1 and restored our database.. Tried the upgrade again but come up saying Failed at the migration point again.
Also derbytomssqlmigration.txt does not exist.


This message was edited 3 times. Last update was at Apr. 30, 2014 11:05 AM

Elite SysAider
121
 
First post ever, I had similar issues to what others have encountered during testing, namely a stall/hang during the migration step for the free edition. I didnt want to impact our current help desk (v9.102) so I copied our derby db to test (very small db). I followed Michael's (usname: slabodnick) suggested steps, and I was able to complete the process as follows:

Virtualbox VM of MS Server 2008R2 x64
Started with 9.05 free edition derby embedded, upgraded to 9.102 free. Stopped the SA service so that I could replace our production derby db in the appropriate directory. Restarted the service, localhost to the portal to ensure I could see our tickets/config. Stopped the service once verified.
Proceeded with the 14.1 patch and like others it hung on migration.

At this point I installed SQL Studio, and then cancelled the patch. It rolled back the SQL install but left Studio intact. I brought up Studio and re-executed the patch and it gave me almost a 1-2minute window in which to create an "sysaid" db with owner "sa" (as Michael posted) under the newly created instance. The migration step proceeded and began migrating our Derby db to SQL successfully

The installer then began to patch SA up to 14.1 and actually hung installing other files. Could have been a variety of issues so I cancelled the installer (this actually prevented me from patching again on this VM) and copied the newly SQL db and placed it on a fresh install of 9.102 with SQL embedded on our Hyper-V dev server (others can use a similar setup in another Vbox instance). All I had to do was replace the default SQL "sysaid" and "sysaidlog" db's and I was able to bring up our help desk on v9.102 SQL embedded. I was able to see all of my tickets, and all the configuration came with it. Currently testing on a new VM to see if any issues appear with the 14.1 patch beyond the derby migration, but having a SQL db to play around with is miles ahead of where we were previously.

IMO, seems like the best step for R&D would be to split the steps into two different installers, one to migrate with a pause in between installing SQL and migrating the DB, and one to patch to 14.1.

The only other notable thing is what all IT admins should do, which is to make back-ups of everything (zips are priceless). SysAid makes it extremely easy to revert changes you make by just having a backup of the directory or changed files. Renaming default items to "_ORIG" while placing your test files can save you alot of time, and gives you the option to delete what you replaced and simply rename back to what you had to revert back to default.

TLDR; SysAid is an amazing product and while this migration might be initially daunting, what I have gotten out of the free version has convinced my superiors to push into buying the enterprise edition software not only for internal use, but external client and project use. Many thanks to Michael, and I hope others are able to have the same experience I had by following his steps.
Super SysAider
88
 
Hi slabodnick!

We are having a "Migration to MS SQL faild" message too. But the patch installer is not hanging at the begging of the migration process. As an important difference the migration process starts and goes ahead until "step or stage 15" where it finally stops hangs for a while and finally sends the error message.

We found that the derbyToMssqlmigration.log shows that the problem starts in migration "stage 16" (showProcess action = MigrationProgressEvent, value = 16) right after which comes the error: "Error in migration (6): java.sql.SQLException: Unknown page format at page Page(1118,Container(0, 5360)), page dump follows:...".

Do you think this could be a result of a corrupted Derby database? I hope not and hope it is an easy-solution-known-issue. In any case, how could this be fixed to be able to migrate to MS SQL and finally move to SysAid ver.14.1?

Thank you in advance for your help and ideas!


Here I include part of the derbyToMssqlmigration.log file (excluding most of the dump information) hoping this can help diagnose our problem:

Starting migration process.....
Adding custom column sr_cust_activity to sr entity.
Adding custom column task_cust_activity to task entity.
Delete not needed policy_id in computer & computer history

Failed to delete policy_id column in computer / computer_history - 'POLICY_ID' is not a column in table or VTI 'COMPUTER'.

17.06.2014 01:39 Migrating account
showProcess action = MigrationProgressEvent, value = 1
17.06.2014 01:39 1 records.
17.06.2014 01:39 Migrating agreement
showProcess action = MigrationProgressEvent, value = 2
17.06.2014 01:39 1 records.
17.06.2014 01:39 Migrating asset2ci
showProcess action = MigrationProgressEvent, value = 3
17.06.2014 01:39 5 records.
17.06.2014 01:39 Migrating asset_catalog
showProcess action = MigrationProgressEvent, value = 4
17.06.2014 01:39 6 records.
17.06.2014 01:39 Migrating asset_catalog_files
showProcess action = MigrationProgressEvent, value = 5
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_catalog_history
showProcess action = MigrationProgressEvent, value = 6
17.06.2014 01:39 6 records.
17.06.2014 01:39 Migrating asset_catalog_links
showProcess action = MigrationProgressEvent, value = 7
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_data_day_data
showProcess action = MigrationProgressEvent, value = 8
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_data_month_data
showProcess action = MigrationProgressEvent, value = 9
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_data_week_data
showProcess action = MigrationProgressEvent, value = 10
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_data_year_data
showProcess action = MigrationProgressEvent, value = 11
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_notif_events
showProcess action = MigrationProgressEvent, value = 12
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_offline_log
showProcess action = MigrationProgressEvent, value = 13
17.06.2014 01:39 0 records.
17.06.2014 01:39 Migrating asset_types
showProcess action = MigrationProgressEvent, value = 14
17.06.2014 01:39 12 records.
17.06.2014 01:39 Migrating audit_log
showProcess action = MigrationProgressEvent, value = 15
17.06.2014 01:40 5508 records.
17.06.2014 01:40 Migrating audit_log_lines
showProcess action = MigrationProgressEvent, value = 16
Error in migration (6): java.sql.SQLException: Unknown page format at page Page(1118,Container(0, 5360)), page dump follows: Hex dump:
....
.... (Excluded dump information)
....
Error in main - java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
showProcess action = MigrationProgressEvent, value = 11004
Migration failed: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.checkIfClosed(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.checkExecIfClosed(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)
at com.sysaid.util.MigrateData.mainReal(Unknown Source)
at com.sysaid.util.MigrateData.main(Unknown Source)
at com.sysaid.util.MigrateDerbyToMssql.main(Unknown Source)
Caused by: java.sql.SQLException: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 13 more
Process started at: 17.06.2014 01:39, Process ended at: 17.06.2014 01:43


Thank you for your time spent reading this post!
Former Community Manager
980
 
Hi simental,

A few searches on Google has results that the error could be related to corruption, but it's not definitive and not something we've seen before. I'm checking with our customer relations team on the best course of action.

Which edition are you upgrading? It may be better to have our support team handle the migration since they can work with the database and determine the best way to either repair, or work around, any corruption in the database. I'll update the thread when I hear back on suggestions.

Thank you!

Michael
SysAider
44
 
I have had the issue of a migration failing. I can install SQL, it builds a sysaid DB but then fails to migrate. I made sure there are no SQL files installed, deleted the folders SysAidMsSql and still no luck. I do not have the derby migration log file as indicated.

Desperately looking for help.

I contacted support but their solution is to shutdown my server for the weekend and they will migrate the data, THAT IS NOT ACCEPTABLE.

Thanks
Former Community Manager
980
 
Hi Dan,

Do you know the size of the database? It could be large enough that a conversion could take a while, which may be why it's preferable to complete it over the weekend.

Are you upgrading directly to version 14.3 from version 9, or using the 14.1 upgrade file?

Michael
SysAider
44
 
the folder in c:\program files\sysaidserver\root\web-inf\db\default\seg0 is 1.29GB not sure if that is the database though.

I am trying to update from v9.1.02 to v14.1.10

the migration doesn't even start, it is an immediately fail after SQL is installed. If I go into SQL Studio manager I can see an sysaid DB is created but has no real data within.

I have tried to stop the sysaidserver service and use the stopberby.bat in the util folder.

No luck.

Looking through SQL Server Logs I found several entries where SQL was trying to access database sysaid using account sa and the result was invalid password.

Please help I cannot be without monitoring my exec fell it is absolutely critical to know 24/7 what is going on and have within a few minutes notice any outages.

This message was edited 1 time. Last update was at Aug. 07, 2014 09:49 PM

Former Community Manager
980
 
Hi Dan,

You already have SQL Studio Manager installed? Are you certain there's been no SQL server installed on that server? It could be that the default account of "sa" has a different password, and the migration utility is trying to authenticate to that account but failing. I'll PM you some information to try, but if you can logon using account "sa" and with the default password, then there's an issue with the installer. If not, then you'd have to reset the password for sa, but before doing so, be 100% certain nothing is using that server for SQL. I'd have for there to be an issue with another application.

Michael
SysAider
44
 
Thanks Michael, you have been very helpful.

I did find out we used this machine for a different product that required SQL. That product was removed but there is some traces left so I will work on a complete clean and password.

I will let you know how it goes.
SysAider
44
 
Sorry to say but I can not get version 14.1 to install correctly. SQL installs fine and with the correct sa password but when it tries to migrate the database it fails instantly.
I have gone though ever log on the server and nothing indicates why it fails.
I don't know where to go from here.
Former Community Manager
980
 
Do you have administrator access under the account you're running the install, or run the installation as administrator? That's about the last thing I can think as to ensure the installer has all the permissions to connect to the database.

I did send an email to our VP of customer relations asking him to check on your Service Request. If being offline with your SysAid server is not possible, then maybe there's a way they could copy your DB and convert it, especially if it's during a slow period to minimize any data loss between the time of copy and when the SQL DB goes into production.

Michael
SysAider
44
 
I am doing the installation with the domain admin account.

I can see that an sysaid DB SQL is created and all services are running as they should, the SysAid service does shut down etc.
SysAider
3
 
I also have the same problem...

Database migration faild...

it gives message at step 136...out of 190

log file cantain following message.

014-11-14 18:27:12 Info: Event type: 0 Exist process code: 127
2014-11-14 18:27:13 Info: Migration Progress report : 128
2014-11-14 18:27:13 Info: Event type: 0 Exist process code: 128
2014-11-14 18:27:13 Info: UI Event has been fired elapsed time: 2058.8
2014-11-14 18:27:13 Info: Installer fetch the pramters event id: 0 Additional Information: 128
2014-11-14 18:27:13 Info: Migration Progress report : 129
2014-11-14 18:27:13 Info: Event type: 0 Exist process code: 129
2014-11-14 18:27:14 Info: Migration Progress report : 130
2014-11-14 18:27:14 Info: Event type: 0 Exist process code: 130
2014-11-14 18:27:14 Info: Migration Progress report : 131
2014-11-14 18:27:14 Info: Event type: 0 Exist process code: 131
2014-11-14 18:27:15 Info: Migration Progress report : 132
2014-11-14 18:27:15 Info: Event type: 0 Exist process code: 132
2014-11-14 18:27:15 Info: UI Event has been fired elapsed time: 2059.07
2014-11-14 18:27:15 Info: Installer fetch the pramters event id: 0 Additional Information: 132
2014-11-14 18:27:15 Info: Migration Progress report : 133
2014-11-14 18:27:15 Info: Event type: 0 Exist process code: 133
2014-11-14 18:27:16 Info: Migration Progress report : 134
2014-11-14 18:27:16 Info: Event type: 0 Exist process code: 134
2014-11-14 18:27:16 Info: Migration Progress report : 135
2014-11-14 18:27:16 Info: Event type: 0 Exist process code: 135
2014-11-14 18:27:17 Info: Migration Progress report : 136
2014-11-14 18:27:17 Info: Event type: 0 Exist process code: 136
2014-11-14 18:27:17 Info: UI Event has been fired elapsed time: 2059.63
2014-11-14 18:27:17 Info: Installer fetch the pramters event id: 0 Additional Information: 136
2014-11-14 18:27:20 Info: Migration report failure completion: 11004
2014-11-14 18:27:20 Info: Event type: 2 Exist process code: 4
2014-11-14 18:27:20 Info: UI Event has been fired elapsed time: 3008.6
2014-11-14 18:27:20 Info: Installer fetch the pramters event id: 2 Additional Information: 4
2014-11-14 18:27:20 Info: Received Stop Event
2014-11-14 18:27:20 Info: Stop event Occurred
SysAider
2
 
I have a different problem, the migration tool does not even come up for me when I start the 9.1 patch again. It never asks to migrate. What do I do here? I have followed all the guides but they don't seem detailed enough. HELP!