changing SQL instance for Enterprise console

Question

I am moving my sophos database using the import and export wizard form Sql 2000 to sql 2008 r2.

If I change the regisrty value "DatabasConnectMS" data source to the new instance that was created. Will this cause any bad effacts to the enerpise console. Once I move to database I will be upgrading my EC form 5.0 to 5.1

Note sql server is on a different machine.

This thread was automatically locked due to age.

jak · Accepted Answer

HI,

I would suggest...stopping the following service on the management server if started:

"Sophos Management Service"
"Sophos Patch Server Communicator"

Then take a backup of the 2 databases of SEC 5.0 using databackuprestore.exe (http://www.sophos.com/en-us/support/knowledgebase/114299.aspx) by running:

DataBackupRestore.exe -action=backup -datasourcetype=database

This should create you a couple of bak files for each database in the default Backup directory for SQL, E.g.:

"C:\Program Files (x86)\Microsoft SQL Server\MSSQL.10.SOPHOS\MSSQL\Backup\".

Note: Info on the databases per version see: http://www.sophos.com/en-us/support/knowledgebase/17323.aspx.

Then, on the new SQL server either:

1. Run the SEC 5.0 installer (the install set is on your current management server I would think under: C:\sec_50\) you can copy that accross and add just the database role to create you the blank databases: SOPHOS50 and SOPHOSPATCH.

As part of the installation, the installer will create a local group called "Sophos DB admins" and add the 'database' account you choose during the installation to this. I assume that you chose a domain account when you installed initially for the database account, so you can re-use that?

OR

2.Manually create the new databases as per: http://www.sophos.com/en-us/support/knowledgebase/116768.aspx.

Either way you should now have the 2 databases for SEC 5.0 in your SQL instance, SOPHOS50 and SOPHOSPATCH51, you can then restore the databases backed up over the top. E.g with RestoreDB.bat (http://www.sophos.com/en-us/support/knowledgebase/110380.aspx).

To ensure that the Windows group is mapped to the logon correctly after moving the databsaes, it is suggested that a couple of commands are run, see: http://www.sophos.com/en-us/support/knowledgebase/116454.aspx for more info on these and the problem you'll get if there is a problem.

Note: If the "database" account is an administrator, you will not get a problem but the database account doesn't need to be an administrator, it gets all the rights it needs to the database by being a memeber of the "Sophos DB Admins" group which is given all the access it requires to the stored procedures, etc.. As a test, after restoring the database, it might be good to test that the database account has rights to it. To do so, create a commnand prompt running under the context of the 'database' account:

Runas /user:[domain]\SophosManagement cmd.exe

(or whatever your database account is)

In the new command prompt you can connect to the SQL instance and call a stored procedure on the SOPHOS50 database:

SQLCMD -E -S .\sophos -d sophos50 -Q "exec sddmbootstrapdataget"

Note: I assume you will need to change the SQL instance here from .\sophos. If this fails with login errors, check the KBA 116454 as mentioned above.

Once the databaes are in place, on the current management server you then uninstall the database component from add/remove programs.

Then re-run the SEC installer (C:\sec_50\ServerInstaller\setup.exe) on the management server, this will perform a modify, you can choose the new instance. Choose the same database account as that chosen on the database install. If they are both local accounts, rather than a domain account, then they should have the same password.

Optionally, remove the SQL instance from the current managment service in use if no longer required. Could just disable the service at startup.

To then upgrade to SEC 5.1, when you're happy SEC 5.0 is ok...

Run the SEC 5.1 installer on the new database server to create the new databases of SEC 5.1 (SOPHOS51, SOPHOSPATCH51, SOPHOSENC51). Or use the same article above 116768 to manually create them.

Then run the SEC 5.1 installer on the management server to upgrade the management server and console components. That should be it, until SEC 5.2 :)

Regards,

Jak

jak · Answer

HI,

These look to be unrelated messages, I've never seen the management service crash like that but it would be worth sorting the database computer out first as there is a chance it might be related.

Firstly, I'm not sure if -S .\ is addressing the instance correctly. If it's a no-named instance, you should be able to specify just the computer name, i.e.:

sqlcmd -E -S UCRXSQL01 -Q .....

as I assume UCRXSQL01 is the name of the SQL server. If it has an instance name then it would be: .\instancename or UCRXSQL01\instancename.

I assume that on UCRXSQL01 there is a local windows group called "Sophos DB Admins" and the database account (http://www.sophos.com/en-us/support/knowledgebase/113954.aspx) is a member of it? The database account is the one stored in the registry key on the management server:

64-bit server: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Sophos\EE\Management Tools\DatabaseUser\
34-bit server: HKEY_LOCAL_MACHINE\SOFTWARE\Sophos\EE\Management Tools\DatabaseUser\

I assume that you have a domain and that this is a domain account? If it isn't a domain account but a local account on the management server, just create the same account name and password on the SQL computer and add that to the "Sophos DB admins" group.

Regards,

Jak