How do i manually upgrade SQL database from 5.0 to 5.1

Hi,

You need to install into your SQL instance the databases of SEC 5.1 first.  So in effect you'll end up with the following databases attached to the instance:

• SOPHOS50
• SOPHOSPATCH
   
• SOPHOS51
• SOPHOSPATCH51
• SOPHOSENC51

Where the new 5.1 databases are in effect empty.  Nothing has been migrated or upgraded at this stage.  Everying in the SEC 5.0 world remains the same and operational. 

http://www.sophos.com/en-us/support/knowledgebase/17323.aspx lists the database names per verison.

Typically these new databaes would be added to the instance by running setup.exe on the database server, if it already had the 5.0 database component it will just give you opportunity to upgrade, which will in effect uninstall database 5.0 and install 5.1.  At no time do previous databases get dropped, the MSIs, just copy the files to disk and ensure that the group "Sophos DB Admins" has the 'database' account (http://www.sophos.com/en-us/support/knowledgebase/113954.aspx) as a member.  Using the installer approach is obviously not every DB admins idea of a good time so you can just run the same commands the installer runs to create the databases.  You also have to manually create the "Sophos DB Admins" Windows group and add a 'database' user to it for the management service to use to connect but as this is an upgrade that should all be in place.

So that being said, did you install the SOPHOS50 and SOPHOSPATCH databases by running the batch files last time?  

Do you have in add/remove programs the 5.0 database component listed?

I assume you know your 'database' account and if your "Sophos DB admins" group is a domain group or local group to the SQL server?

If you're just installing the databases using the batch files (which is what the database MSI does), to install the new databases you can run:

C:\sec_51\ServerInstaller\DB\Core\InstalDB.bat server\instance [contectofsophosdbadminsgroup] SOPHOS51 core.txt

C:\sec_51\ServerInstaller\DB\Patch\CreatePatchDB.bat server\instance [contectofsophosdbadminsgroup] SOPHOSPATCH51 patch.txt

C:\sec_51\ServerInstaller\DB\Encryption\InstallEncryptionDB.bat server\instance [contectofsophosdbadminsgroup] SOPHOSENC51 Enc.txt

In all of the above commands, [contectofsophosdbadminsgroup] will depend on where your "Sophos DB Admins" group is.  If it's a local group on the SQL server it will be the NetBIOS name of the computer.  If it's a domain group it will be the short, unqualified domain name.

So for a SQL server called SQL1, with a SOPHOS named SQL instance, with a local "Sophos DB Admins" group the commands would be:
 

C:\sec_51\ServerInstaller\DB\Core\InstalDB.bat SQL1\SOPHOS SQL1 SOPHOS51 core.txt

C:\sec_51\ServerInstaller\DB\Patch\CreatePatchDB.bat SQL1\SOPHOS SQL1 SOPHOSPATCH51 patch.txt

C:\sec_51\ServerInstaller\DB\Encryption\InstallEncryptionDB.bat SQL1\SOPHOS SQL1 SOPHOSENC51 Enc.txt

So at this point you have new databases created.

Then on the management server (ServerA) you run the SEC 5.1 installer.  This will in effect, uninstall 5.0 for the server and console component and install version 5.1 of both.  

As part of the Server component install it will call a tool called UpgradeDB.exe.  This will essentially call a stored procedure in the SOPHOS51 database, that will in effect, move the data from SOPHOS50 to SOPHOS51.   The data in SOPHOSPATCH is not migrated to SOPHOSPATCH51 as the schema has changed to the point where I would imagine writing a migration script would be more effort than it's worth as the clients perform their next assessment, send back new results and the data will be populated.  So you may see an emtpy Patch event viewer for a while after upgrade until the clients start to re-asses.  If you're not using patch you don't need to worry.

Once you are happy that the upgrade is a success you can drop the database:

• SOPHOS50
• SOPHOSPATCH

Sophos never deletes previous databases and leaves it up to the admin to backup/drop when happy.

The following articles might be useful also:

http://www.sophos.com/en-us/support/knowledgebase/116768.aspx

http://www.sophos.com/en-us/support/knowledgebase/116465.aspx

Hope this helps.

Regards,

Jak

Hi,

Sorry to jump on someone elses post, but was wondering if this process would also work for upgrading EC 4.5 to 5.1

Thank you

Hello jamesn,

you mean you have SEC4.5 with a remote database (note that at least 2005 is required)? The procedure is the same (of course the database name for 4.5 is SOPHOS45 and there is no SOPHOSPATCH database) - if you prefer to do it manually instead of doing it using the installer which Jak has briefly mentioned. 

Christian

Hi Christian,

Thanks for your quick reply. My database is on a remote clustered SQL instance, so will try this approach.

Thanks

James

Dear All

I Need some help regarding the consol i cant open it after i move the Log file and am try to reistall it but still now working is there any way to fix it without uninstall it and reinstall again ..?

Hello osama_f_h,

did you check all the items on the list, specifically the services running? Please see also article "Management server connection failed. ....".

Christian