Transfer Sophos Database

Hello Ted,

there was an article (22468) describing the procedure but it seems to have disappeared.

Jak described how it can be done for 5.0 here. Basically the procedure is the same, for the script mentioned either change the values a appropriate for 4.7 or refer to the SEC 4 Migration Guide chapter 11, items  17. and 18.

One addition though: IIRC (and the short tests I made just now as well as a post of mine I digged up suggest I do) at least from SEC5.0 on as next to last step you have to uninstall the Sophos Management Database from the management server (of course after you have backed up the database) via Add/Remove Programs as re-running setup.exe doesn't present the Modify dialogue on a full install (can't test with 4.7 right now and also am not sure if Modify lets you change the database to a remote instance at this point). Don't worry, your data is safe (you have the backup and it's already on the new server). Then just start the console and it should prompt you for the database location.    

HTH

Christian

HI,

You sure can.

You run the 4.7 installer on the new database server, and choose to just add the database. If you don't have an existing SQL instance to use it will create you one called Sophos again. I assume however you're moving this to an existing SQL server?

Either way, say this SQL server is a member server for your domain, you'll end up with a new local group on that computer called "Sophos DB Admins". You will then need to create an account, domain account is fine and call it "SophosManagement" for example and add it to the local "SOphos DB admins" group on the new database server.

Note: This account needs to be able to log on to the existing manageemnt server and will be used by the management service to access the database.

You'll also end up with the SOPHOS47 database but it will be blank at this stage.

I would then Close SEC on your existing management server and stop the services:
"Sophos management server"
"Sophos Message Router"
Then take a backup of SOPHOS47 using backupdb.bat (http://www.sophos.com/en-us/support/knowledgebase/110380.aspx). So essentially you are going to be running:

backupdb.bat C:\Windows\temp\sophos47db.bak .\sophos sophos47

You then need to copy this database backup file: "sophos47db.bak" to the new SQL server (C:\windows\temp\) and then restore it to the database on there using restoredb.bat:. E.g.

restoredb.bat C:\Windows\temp\sophos47db.bak .\sophos sophos47

I'm assuming a SOPHOS named instance, ajust as required.

Then to ensure the SQL to Windows mapping is correct after the restore run;

sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"
sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"

Then download:
http://sophserv.sophos.com/repo_kb/111898/file/ResetUserMappings.sql.txt
and then run:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql.txt

You now have the database restored and corrected on the new SQL server , you just need to point the existing management server at it.

Then you modify the install on the existing managament server to remove the database component, this will then prompt for where the database is, you then choose the new SQL server and instance name, you then chose the "SophosManagement" account as this has access to the database.

You can remove the local SQL instance as requried once happy it's all working.

Regards,
Jak