Hi,
Need advice to transfer my sophosDB in a central console to remote server.
Running SEC 4.7
updrage to SEC 5.1
what is the steps to do this?
Regards,
tedz
This thread was automatically locked due to age.
Ok,
It's best to split and then upgrade.
I would start by creating a domain account called 'SophosManagement' if the computers are in a domain, if they are not, create a local account called 'SophosManagement' on each with the same password. This can be a regular user as long as it can log onto the computers. This user will end up being a member of the local group 'Sophos DB Admins' on the new SQL Server.
1. Install SQL Express 2008R2, creating a SOPHOS instance.
Note: ensure that TCP is enabled as a protocol on the SQL Server instance and that the firewall is not blocking the access to SQL for later.
2. Run the SEC 4.7 installer on the new SQL server, and choose the database component only. Check at the end that 'SophosManagement' user has been made a member of a new local group on the SQL Server called 'Sophos DB Admins'
3. On the current management server close open consoles and stop the management service and Sophos Message Router.
4. On the new existing management server run BackupDB.bat to backup SOPHOS47 database, so you have a file such as SOPHOS47.BAK.
5. Copy SOPHOS47.bak to the new SQL Server and restore the backed up SOPHOS47 database over the top of the new installed blank SOPHOS47 database using RestoreDB.bat.
6. On the new SQL Server Re-map the login of the Windows group "Sophos DB Admins" to the SQL Login. to do so:
6.1 Download the file: ResetUserMappings.sql.txt to 'C:\'
6.2 Remove the .txt from the file name so the file name is C:\ResetUserMappings.sql
6.3 Run the following commands:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql
If running this command returns the error:
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
...the problem is the mapping between the Windows group 'Sophos DB Admins' and the SQL Login. To fix this issue, run the following commands, substituting SERVERNAME for your domain name if 'Sophos DB Admins' is a domain group; otherwise enter the computer name where the 'Sophos DB Admins' group resides.
Note: The square brackets are required.
sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"
sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"
Once complete, re-run the previous commands, i.e.:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql
7. Re-run the SEC 4.7 installer on the existing management server and uncheck the database component, the installation with then prompt you where the database is, you can select the new server. Use The SophosManagement account.
8. Ensure the Management Service and Router can start and are started.
9. Check SEC 4.7 is all OK.
To upgrade to SEC 5.1
10.Run the SEC 5.1 installer on the new database computer (It should just offer an upgrade)
11.Run the SEC 5.1 installer on the management server.
That should be it.
Regards,
Jak
Ok,
It's best to split and then upgrade.
I would start by creating a domain account called 'SophosManagement' if the computers are in a domain, if they are not, create a local account called 'SophosManagement' on each with the same password. This can be a regular user as long as it can log onto the computers. This user will end up being a member of the local group 'Sophos DB Admins' on the new SQL Server.
1. Install SQL Express 2008R2, creating a SOPHOS instance.
Note: ensure that TCP is enabled as a protocol on the SQL Server instance and that the firewall is not blocking the access to SQL for later.
2. Run the SEC 4.7 installer on the new SQL server, and choose the database component only. Check at the end that 'SophosManagement' user has been made a member of a new local group on the SQL Server called 'Sophos DB Admins'
3. On the current management server close open consoles and stop the management service and Sophos Message Router.
4. On the new existing management server run BackupDB.bat to backup SOPHOS47 database, so you have a file such as SOPHOS47.BAK.
5. Copy SOPHOS47.bak to the new SQL Server and restore the backed up SOPHOS47 database over the top of the new installed blank SOPHOS47 database using RestoreDB.bat.
6. On the new SQL Server Re-map the login of the Windows group "Sophos DB Admins" to the SQL Login. to do so:
6.1 Download the file: ResetUserMappings.sql.txt to 'C:\'
6.2 Remove the .txt from the file name so the file name is C:\ResetUserMappings.sql
6.3 Run the following commands:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql
If running this command returns the error:
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.
...the problem is the mapping between the Windows group 'Sophos DB Admins' and the SQL Login. To fix this issue, run the following commands, substituting SERVERNAME for your domain name if 'Sophos DB Admins' is a domain group; otherwise enter the computer name where the 'Sophos DB Admins' group resides.
Note: The square brackets are required.
sqlcmd -E -S .\sophos -Q "DROP LOGIN [SERVERNAME\Sophos DB Admins]"
sqlcmd -E -S .\sophos -Q "CREATE LOGIN [SERVERNAME\Sophos DB Admins] FROM WINDOWS"
Once complete, re-run the previous commands, i.e.:
sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql
7. Re-run the SEC 4.7 installer on the existing management server and uncheck the database component, the installation with then prompt you where the database is, you can select the new server. Use The SophosManagement account.
8. Ensure the Management Service and Router can start and are started.
9. Check SEC 4.7 is all OK.
To upgrade to SEC 5.1
10.Run the SEC 5.1 installer on the new database computer (It should just offer an upgrade)
11.Run the SEC 5.1 installer on the management server.
That should be it.
Regards,
Jak
