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.

Hello Roy Carlo Bago,

do you mean windows 2016
yes, SEC 5.4.1 is not officially supported on Windows Server 2016, doesn't mean it won't work though.

restore at new server [with DataBackupRestore.exe]
as you want the databases on a remote server you'd not restore them on the management server. DataBackupRestore.exe is in the Enterprise Console program directory - of course you (or your DB Admin) could copy the directory to the SQL server, DBR.exe simply calls TRS.bat which is in principle the same as RestoreDB.bat - the latter is part of the Database component.

To repeat more clearly arranged:

• remove Encryption from the old server
• run DataBackupRestore.exe -action=backup
• install the Database Component on the SQL server (optional but recommended, otherwise the databases must be manually created)
• restore the 5.2 databases on the SQL server
• run the SEC installer on the new management server deselecting the Database Component; point to the remote SQL server/instance when prompted
• the Management Service will detect the old databases and migrate them to the new ones

I've left out the detailed migration steps for the management server (e.g. restoring certificates). Note while the Guide describes a same-version migration an upgrade-migration is possible and at least semi-official.
It sounds more complicated than it actually is.

Christian

Thank you Christian, i have an appointment tomorrow to this task.

i will follow your steps and get back to you.

Christian..

i ran the tool backupdb.bat and got these error!, can you clarify what is wrong!

Hello Roy Carlo Bago,

backing up the new database? Is it local?

Christian

yes.. it is a test lab. even the udl file works!

yes.. it is a test lab. even the udl file works!

Hello Roy Carlo Bago,

the first attempt fails as it gets an Access denied trying to write to the volume root. The backup location must be writable for the SQL Server.
The second fails as you didn't specify the instance, just the server (and it would have failed with the Access denied as well).

Christian

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