upgradedb error going from 4.0 to 4.7

Hello pez,

just to make sure: Your SOPHOS4 database is on a remote server. You've used InstallDB on your management server to install SOPHOS47 on the same remote server and now UpgradeDB fails? 

Christian

HI,

Here is some information for you I hope will help:

Initially on an upgrade from SEC 4.x to SEC 4.7 you should have a SOPHOS4 database and a SOPHOS47 database side by side in the same instance.  You may well have a SOPHOS3 database as well if SEC3 was previously updated to SEC4. Which is fine, Sophos does not automatically drop databases once not required.

So:
SOPHOS45 contains all the previous SEC 4.5 data.

SOPHOS47 being the freshly created database awaiting the data to be transferred to. 

UpgradeDB.exe is executed as part of the management server upgrade part not the database install.  It will determine the version installed and use this as the sourceVersion switch, i.e.

3 for SEC3, SOPHOS3 database,
4 for SEC4, SOPHOS4 database, 
45 for SEC4.5, SOPHOS45 database

So for your example:
upgradedb -sourceVersion=4
looks correct.

Essentially UpgradeDB then calls the stored procedure:
dbo.[FromXTo4]
in the SOPHOS47 database, with the source version, in this case as one of the parameters.  This causes the stored procedure  to then call the relevant next stored procedure  of either:

dbo.[From4To47 ]

dbo.[From45To47 ]

dbo.[From3To47 ].

It is these stored procedures that essentally select from the old datbase and insert into the new database. So that being said, I would suggest:

1. Run installDB again to re-create the SOPHOS47 database. The re-create will drop the existing SOPHOS47 and re-create it.

This will ensure there is no data in there which might trip up the inserts.

2. Execute in the SOPHOS47 database:

exec dbo.[From4To47 ]

I.e. from SQL Management Studio when connected to the SOPHOS47 database or if you don't have that, run:
sqlcmd -S .\sophos -d sophos47 -Q "exec dbo.From4To47 "
where .\sophos represents a locally installed SOPHOS named instance.

Hopefully if it fails the SQL errors will be more specific.
 

If this does work and transfer the data to the new SOPHOS47 DB you will then need to update the Upgrade table in SOPHOS47 and set the UpgradeStatus of the database to 2 otherwise the Sophos Management Service will see the database as not ready and will not start.  To do so run:

sqlcmd -S .\sophos -d sophos47 -Q "UPDATE dbo.Upgrade SET UpgradeStatus =2"

I hope this helps and explains what is going on.

Regards,

Jak 

hi Christian,

i ran installdb on my database server and ran upgradedb on my management server

regards

andrew

thanks Jak,

i'll try what you say and get back to you with the results

regards

ok, at first it said it couldn't find the stored procedure. so i went and talked to one of our applications guys and they ran it for me

use [SOPHOS47]

exec From4To47

he said he puts in the "use [SOPHOS47] as he's run things in the wrong place before

in the exec command he wasn't sure why we were putting in square brackets, but it seems to have run ok

output:

Checking identity information: current identity value '4041', current column value '4057'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

however when i now try to start the sophos management service on the server with the enterprise console, it says unable to start error 0x80004005: unspecified error

the application log has an entry saying:

Initialization failed.

Step: Migrating data if necessary
Error: std::runtime_error
Data: ErrorUnexpected

however it's doing that if i try and connect to the local database as well now. i may have screwed up the blank local database though.

does this indicate it thinks it needs to upgrade the database?

regards

andrew

i ended up uninstalling sec 4.7 and reinstalling 4.0, connecting to a local database

then i tested that i could connect still to my old 4.0 database on the sql 2008 r2 server, which worked

then i connected back to my local database and upgraded to 4.7

after which i tried to reconnect to the sophos47 database onthe sql 2008 r2 server, it didn't work so i ran

upgradedb - reset

and now it connects ok

this was a test to see what i had to do to upgrade my live system. so next i'm going to put sec 4.0 back on this server and go through the same steps to upgrade my sophos4 database on the live server, though i could probably just detach it and copy it to my live server and make sure i can still connect with the test sec 4.7 server then upgrade my live sec 4.0 server to 4.7

it doesn't have a local database though so i may have to uninstall 4.0, install 4.7 with local database, then change the connection

thanks Jak,

that got me on the right path. i'm having issues upgrading my live server, but that's another story. the database is there ready and waiting for when i have a valid enterprise console 4.7 on it

regards

andrew

We experienced similar issues upgrading from version 4.5 to 4.7 whereby the initial upgrade failed and we had to completely re-install the package. After the full install was completed we found that the management console was failing to connect to the new database and threw up the following event log message:

Initialization failed.

Step: Migrating data if necessary

Error: std::runtime_error

Data: ErrorUnexpected

From this point we followed the database migration instructions identified by Jak and the management console now appears to be running successfully on version 4.7. Fingers crossed it stays that way.

Thanks for the help,

Andy.