upgradedb error going from 4.0 to 4.7

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,

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