Transfer SophosDB to remote server

HI,

Are you saying that you have upgraded from 4.7 to 5.1 in the past but are now at 5.1 and want to move the database of 5.1?

Or

You are currently running 4.7 (all components on the same computer), want to upgrade to 5.1 and at the same time split the database role on to a remote server.

It would be best to get to 5.1 and then move the database component off onto the remote server.

Regards,

Jak

Also see: How to install the Sophos Management Database component on to a different (remote) computer

The two options are either (1) upgrade the existing installation to v5.1 then separate out the combined installation OR (2) separate out the existing SOPHOS47 database to the new DB server, upgrade the recently moved SOPHOS47 database to SOPHOS51 and then upgrade the original Sophos Management Server to v5.1.

Hi jak,

Thanks for your reply. I'm currently running 4.7 (all components on the same computer), want to upgrade to 5.1 and at the same time split the database role on to a remote server and I upgrade the sql 2008 express to standard.

Thanks,

tedz

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

Hi Sir Jak,

Thanks for the guide. i have a question:

would this apply in a workgroup mode?

already installed the sql server in the new database server and restore db. im already in the process of reinstalling the old server for installing SEC and management server only but im stock in what on database details username and password for database access though it already detected sql instance from my new sql server. My problem now is what account should use since im just using local administrator for workgroup.

Thanks,

Teddy

Hi,

Typically in a workgroup mode it would be setup with:

• a dedicated account, e.g. 'SophosManagement' on both computers, i.e. the management server and database computer.  This would have the same password.
• This 'SophosManagement' user account, would be a member of a local Windows group called "Sophos DB Admins" on the database computer.
• The SophosManagement account doesn't need to be an admin account as it obtains the rights to access the stored procedures in the database by being a member of the Sophos DB Admins group.

Also.. I would suggest, checking that the collation of the instance to ensure both instances are the same.  The collation might be something like: "SQL_Latin1_General_CP1_CI_AS".

The command to run on both computers to check the collation would be:

sqlcmd -E -S (local)\sophos -Q "select SERVERPROPERTY('Collation')"

These should be the same.  If not, you should remove and re-create the new instance, with the same collation as the old instance.

After restoring the database over  the top of a database (in the case below SOPHOS47), you should 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"

sqlcmd -E -S .\SOPHOS -d SOPHOS47 -i C:\ResetUserMappings.sql

Where, SERVERNAME is the name of your database computer as "Sophos DB ADmins" is a local group.

The above commands are to ensure that the "SOPHOS DB Admins" group is mapped correctly and therefore gives a non admin user on the computer access to the SQL objects required.  Of course, if you run the SophosMangement account as an admin, this problem is not seen as typically members of Administrators are SQL admins but it's not good practice.

Regards,

Jak

Hi Jak,

Thanks for the advice. i encountered and error when i install SEC 5.1. i attached the logs on this link below.

http://ge.tt/6i2YIlM/v/0

Regards,

tedz

Hi QC\Jak,

i tried your instruction above. however i encounter some issues. here's what i did:

1.backup db from the old SEC server with sql

2. installed a new sql server in a separate server

3. installed SQL r2 in new sql server with SOPHOS instance using newly created "SophosManagement"account

4. installed SQL in the new sql server using sec_47 installer, installing only database

5. restore the db to the new sql server

6. initiate resetuserremappings on new sql server

7. rerun sec 4.7 on the old server and remove database

8. cannot start Management service***

Please help. thanks in advance

HI,

What's the error in the app event log when you try to start the Management Service?

Maybe see:

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

with the error.

Regards,

Jak

Hi Jak,

thanks for the reply. based on the logs im getting this error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

i checked the link you have given me for simulation purposes.

Perform the following tests:

1. Check that the SQL Server service referenced in the connection string established above is started.  Typically on a default installation this service is called 'SQL Server (SOPHOS)'. (yes)
2. Ensure that the SQL server referenced in the connection string can be resolved by the management server (ping and nslookup). yes, can ping the sql server from management server
3. Check that the SQL Server instance hosts the database name referenced in the connection string.
   sqlcmd -E -S .\sophos -Q "select name from sysdatabases" error encounter+++++

C:\>sqlcmd -E -S .\sophos -Q "SOPHOS47"
Msg 2812, Level 16, State 62, Server SOPHOSSEC\SOPHOS, Line 1
Could not find stored procedure 'SOPHOS47'.

4. Create a UDL test file to test connectivity to the SQL database and appropriate database. successful

5. If the SQL Server instance is remote to the management server, check that SQL Server is accepting remote TCP/IP connections.  This can be checked using the 'SQL Server Configuration Manager', accessible from the Start menu. enabled