moving local sophos DB to new extrnal DB Server

Hello Roy Carlo Bago,

first of all, the associated database names don't always contain the current version. Furthermore Encryption (SDE) has been withdrawn and SOPHOSENC52 is no longer used.

Why 5.4.1 and not 5.5.0? Even 5.5.0 can't fully manage the current SAV 10.7 (5.5.1 should be out soon) but more important 5.4.1 doesn't support Server 2016.

Whether you've actually used SDE or not you should follow Sophos Enterprise Console 5.4.0 - How to remove Encryption to prepare your old server for migration. Afterwards run DataBackupRestore.exe to back up everything. Install the new (5.5.0) Database component (or the databases manually) on the remote SQL server. Copy the database backups to the SQL server and use RestoreDB.bat to restore the old databases (specify the correct database names). On the new management server follow the migration guide, skip the database component install and restore. Install just the Sever and Console components of SEC, point to the remote database when prompted. Make sure the Database User has the necessary rights on the SQL server.

Christian

Hi Christian..

thank you very much for your reply,

i hope i can install 5.5.0 but they asked me they made a plan and tried to upgrade to 541!, i searched here and found 541 supports SQL2016, so do you mean windows 2016 insted?, i ran it on win2016 in my testing lab and it works.

and for backup and restore DB, if i'm using the the tool DataBackupRestore.exe for backup at old server and restore at new server, then why do i need RestoreDB.bat?.

and i understood that: i will be giving the 3 DBs (SOPHOS540, SOPHOSPATCH52, SOPHOSSECURITY) to the DB Admin guy, and get from him DB instance and user account sothat i can point to them during installing (console and management) on new server, so i'm expecting that the db user are in the same domain as the new server is so that it can be shown in domain or groups when prompt.

i gave the db admin the full control.. it fails too!, where can i backup, i have only one volume, C:\

Hello Roy Carlo Bago,

AFAIK the backup task is executed with the rights of the SQL Server - not the user requesting the BACKUP DATABASE.

Christian

Hi Christian..

i'm trying to restore dbs as a test before moving forward and make change release, but receive this error at the new server!

even i enabled tcp/ip from sql configuration server, gave full control for network service, local service to sophos directories

i'm really getting frustrated :(

Hello Roy Carlo Bago,

I'm a little bit confused, yesterday it was WIN2008 today it's UAESAVPOP04. You're running the DataBackupRestore on the latter?

BTW: Yesterday's screenshot suggests that you've used Administrator as SEC's Database User - you shouldn't do this.

Christian

yesterday it was my lab.. today i'm at customer site!, any way i solve the connectivity problem, i found sql server agent disabled, i ran it then UDL test succeeded :)

but when try to restore.. got new Error :(

hens i'm trying to restore db after install db component on new server (normal server to server migration just to ensure db can be restored correctly) 

Hello Roy Carlo Bago,

I see, I see.
Unfortunately the lines shown are more reverberations and don't show the actual error. Could you provide the complete output of the command?

Christian

this is regarding the error:

i spoke to them, they agreed for upgrading to 550, they already now have 5.4.1 on their old server with local db, but these snapshots while i was restoring db at new server after running 5.4.1 and selecting db component only(db here is local). just to to try make the migration from win2008 to win2016 and see if it will success!.

also now i have another question.. if i will migrate and upgrade to 550 at the same time!, then the steps will be:

- backup databases on old server 5.4.1 which are (SOPHOS540, SOPHOSPATCH52, SophosSecurity)

- on sql remote server, create SOPHOS instance, db user account, db admin group, and 3 databases (SOPHOS540, SOPHOSPATCH52, SophosSecurity)

- restore databases on sql remote server.. here, these databases were restored under 5.4.1 version too, right ?

- run SEC 550 on new win 2016 server and deselect db component, point to remote instance SQLSERVER\SOPHPS and its user account.

the question now: SEC 550 have SOPHOS550, whereas remote SQL have SOPHOS540, how will the old one change to the new name!, how will the console detect this?

Hello Roy Carlo Bago,

I'll look at the screenshots tomorrow.

on sql remote server
You'd have to create the 550 databases with either the installer or manually with the scripts. As for the 540/541 databases - a RESTORE might be possible even if they did not exist before. Personally I'd create the 550, then try the restore. If it fails create the 540 versions with the 540 scripts.

Christian

Hello Roy Carlo Bago,

should have seen this yesterday: CREATE DATABASE permission denied in database 'master'. The logged on user doesn't have the necessary permissions on the SQL server. Usually if you run the installer it creates the Windows Security Group Sophos DB Admins, adds administrators and the "database account" to the group and creates the required login in SQL.

As for databases, database names and migration:

• when the Management Service starts it checks for the existence of its associated databases
• if it can't find them it terminates, otherwise it checks for a certain value (UpgradeStatus on table Upgrade)
• if the status is 2 then the databases are "in production", otherwise it searches for the "newest" previous (supported) database
• if it can't find one it assumes a fresh install, sets the status and starts using the database, otherwise it call UpgradeDB.exe with the applicable switches
• UpgradeDB.exe copies the data from the previous database, if necessary applying the appropriate transforms; the old database is not modified in any way (neither taken offline nor dropped - if you want to get rid of it you have to do it manually)

I hope it is clearer now.

Christian

i have an appointment the next Sunday there but i'm afraid of facing errors after upgrade/migrate process because they told me they tried migration to 5.4.1 two months ago and it succeeded, the db was remote, they faced error while trying to connect to sophos servers online using their sophos account!!, were not able to connect to sophos online!. then they had told  there is something wrong during configuration, have a fresh installation and try again!.

i was thinking about upgrading 5.4.1 to 5.5.0 at old server first before backup the databases from it!. make scene?

i have an appointment the next Sunday there but i'm afraid of facing errors after upgrade/migrate process because they told me they tried migration to 5.4.1 two months ago and it succeeded, the db was remote, they faced error while trying to connect to sophos servers online using their sophos account!!, were not able to connect to sophos online!. then they had told  there is something wrong during configuration, have a fresh installation and try again!.

i was thinking about upgrading 5.4.1 to 5.5.0 at old server first before backup the databases from it!. make scene?

Hello Roy Carlo Bago,

error while trying to connect to sophos servers online
in the SUM (Update Manager) configuration, or?

While an upgrade never modifies the old database it's best practice to back up before an upgrade. And a true backup's requirements are only met when it can be restored [:)].
I've worked with SEC since version 1.0 (including several Betas), I've skipped some versions (and not the same on the different servers - I have more than one) and never had problems due to the fact that I didn't upgrade version by version. OTOH an upgrade takes less than half an hour (ok, I've done it more than once so it has become second nature) and if you migrate the advantage is that you know the new version works and any issue you encounter on the new server is likely caused by the install.

Christian

Hi Christian,

i faced some errors during upgrade, it didn't upgrade the console component.. i left the old server and started then doing normal migrate  to save time (since i have 5.4.1 db backup already), i opened the new server, and finally it succeeded (5.4.1 on win2016). but when i opened the console i faced this msg:

"Enterprise Console cannot protect your network fully. This is because the update manager installed on the same computer as Enterprise Console is not configured. For instructions, see http://www.sophos.com/en-us/support/knowledgebase/53965.aspx"

hence i received an error "unable to install update manager" during installation mgmt server and console components, i viewed article 114627, then i opened appwiz.cpl and found Management DB and Management Server components only. also found programs like "Sophos remote management system" and "Sophos autoupdate", i uninstalled the last two programs and reinstall SEC and it succeeded!.

and for moving db with upgrade.. in my testing lab, i got errors when following method of: backup 541 db then restoring it with 550 wizard at new server!.. instead i upgraded 541 to 550 first at old server, then back db up, then restore it at new server and continue the routine.

Christian.. where are you :)

i need your advice

Hello Roy Carlo Bago,

the next to last paragraph ended with it succeeded! And the last one sounded more like a report and not a description of an insurmountable problem - so, for what issue do you need advice? [:)]

Christian

lol :)

Hi again brother..

i mean it succeeded to install the three components, but failed to connect to Sophos online to get updates!.

still getting this msgbox each time i run the Console!

"Enterprise Console cannot protect your network fully. This is because the update manager installed on the same computer as Enterprise Console is not configured"

so the complete migration process wasn't successful.

and for SUM i used the same user and password that was used at old sever!. 

Hello Roy Carlo Bago,

can you acknowledge the box and continue to work with the console or does it exit at this point?

Christian

it works locally, i can see my DB and policies. but the update bottom is invisible! anything leads to update is invisible.

Hello Roy Carlo Nago,

by anything you mean for example the Update managers button or?

From the message I'd assume that it "just" doesn't find the configuration for the local SUM and the Update managers view is the place to configure it.

Christian

I really hope it will be as simple as this!, will try to take remote session with them tomorrow to see it.

but what if that button "update managers" is invisible!, then what could be happened wrong and cause that?, at that time should i uninstall update manager component and reinstall it again?

Hello Roy Carlo Bago,

the message suggests that a local SUM has been found, not that it's missing. I don't think that the view would be inaccessible even if it is.
You shouldn't uninstall a SUM (as it takes RMS with it) unless you intend to uninstall all other (management) components as well.

Christian