250k message 50Gb hMailServer migration

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
iprat
Normal user
Normal user
Posts: 247
Joined: 2005-05-20 16:50
Location: Barcelona, EU
Contact:

250k message 50Gb hMailServer migration

Post by iprat » 2015-03-25 12:00

Hi everyone:

I am old user of hMailserver, our installation of hMailserver has survived around 10 years without any serious problem, and has iterated over a wide range of versions, I have moved message directory to a new HD when the original became too small, and everything went fine over those years thanks to hMailserver being a great tool and reading thorough this forum.

But today I am facing a transition I am not sure in a couple of steps. I am migrating hMailserver from the old and rusty server to a brand new one, everything is setup and only hMailserver rests to be moved.

I've already read a lot and have arrived to this conclusion:

I have 250k messages and 50Gb, so I cannot use the internal backup tool (at least is is not recommended).
The old server was using the default MySQL installation of the ancient versions, and the new one has a MSSQL 2014 Express installed because we need it for our CRM.
I have already copied and sinced all message data (50Gb) to the new server with a similar to Robocopy tool (FileSync very useful).
I don't know how to translate MySQL to MSSQL without hMailserver internal tool.

What do you think it is a better aproach ?
Is it reasonable to install a MySQL only for hMailserver and a couple of small web databases ?

In any case, can anyone explain a procedure to move such installation ?

Best regards to anyone that reads this ;)

PD: Never in those 10 year hMailserver faulted, main issues were CLAMD dying a lot of years ago ;-) Of course we don't want to change anything :-)
My perfect combination:
hMailServer 5.6.1 (B2208), ASSP 1.3.3.8 (antispam), Clamav 0.98.6 (antivirus)

User avatar
jimimaseye
Moderator
Moderator
Posts: 8095
Joined: 2011-09-08 17:48

Re: 250k message 50Gb hMailServer migration

Post by jimimaseye » 2015-03-25 13:00

First, a discussion:

The issue of the internal backup not being suitable is still under some doubt in my mind and I think it is still possible. I will explain why:

When it is run, and you have selected messages (you must! for a migration), it writes out an XML file with all the details of the various records in the database (settings, domains, individual message details such as location, flags etc) and then Zips this XML file along with a data backup (copy) of the messages using 7Zip. Now, unless there is something hard-coded in the program that says "upon reaching a limit of X then stop/error" (which I doubt there is) I cant see why there should be an upper limit on anything other than OS-level filesize limits or disk space. The process is simply no different than if you were performing the option manually from OS level.

You have to be aware that for diskspace you will need at least enough space to make exactly one full replication of your current data directory (on whatever backup destination disk you are choosing) due to the 'copy' of the data prior to it being compressed PLUS enough space to then hold a compress of it (I guess the safest estimation would be to allow double of the current data directory.

eg,

DATA = 17gb
BACKUP DESTINATION will need:
copy = 17gb
+ Zip = (no more than) 17gb
TOTAL 34GB

(i reality perhaps the compress could be 20% less therefore only 13.6 (= 30.6gb total)

Now why do I mention all this above? Well because there is no other way of doing the migration. In order for a data restore WITH message details to be performed on the new system you WILL NEED that XML file with the message details in it. And unfortunately there is a contentious issue about this on the forum because it seems (suggested by one of the developers) that without the messages included it sill simply wipe out your current data directory (on your destination install) viewtopic.php?p=173859#p173859

SUMMARY:

You only have 2 options.

(A)
Your method would be:

1, BACKUP settings + account + Messages WITH COMPRESSION
2, copy the backup file over to new server
3, Fresh install of HMS
4, do FULL RESTORE from the copied backup file.

nb: you wonderful rsync of data across Im afraid was a waste of time because upon doing a restore it will be deleted automatically.

I repeat MAKE SURE you do the the backup WITH MESSAGES and WITH COMPRESSION. Otherwise the whole restore of messages will fail.

I see no reason why this method should not be successful although the time taken is quite lengthy (mainly due to the 7Z compression period of the messages).

(B)
The other alternatives:

Of course you could install MySQL on the new server for the sole purpose of running HMS and then you could simply use a MySQLdump and restore of the database and use you wonderfully rsynced message copy. Any quicker from the other method? Well, you do have to download and install MySQL, configure, and make sure it doesnt conflict with your MS SQL (would it??) :\

IN ALL CASES, irrespective of what method you choose... make sure you are installing the SAME VERSION OF HMS on the new server for restore as was used on the old server for backup. (You can do HMS upgrade of programs after restore if you wish)

(The good thing is that because you are migrating to a new server you have the ability to abort and try a different method from scratch if it goes wrong without losing your original installation)
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

iprat
Normal user
Normal user
Posts: 247
Joined: 2005-05-20 16:50
Location: Barcelona, EU
Contact:

Re: 250k message 50Gb hMailServer migration

Post by iprat » 2015-03-25 13:38

Thanks for your fast answer !

I had already thought in this solution, but I was cautions with the 1GB limit (I am at 50GB now).

Anyway I'm going to give it a try, and in fact I'll make the backup in the new server, in that way I'll have more than enough (+800GB free).

My only question is... I value more time than disk space at this moment, is there any technical problem with making the backup without compression ?

I'll give it a try and comment here the results anyway.

As for the data synced... don't worry, it was an automated copy that lasted a couple of ours while I was out, I wasn't a big loss.
My perfect combination:
hMailServer 5.6.1 (B2208), ASSP 1.3.3.8 (antispam), Clamav 0.98.6 (antivirus)

User avatar
mattg
Moderator
Moderator
Posts: 20028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: 250k message 50Gb hMailServer migration

Post by mattg » 2015-03-25 14:07

jimimaseye wrote:(B)
The other alternatives:

Of course you could install MySQL on the new server for the sole purpose of running HMS and then you could simply use a MySQLdump and restore of the database and use you wonderfully rsynced message copy. Any quicker from the other method? Well, you do have to download and install MySQL, configure, and make sure it doesnt conflict with your MS SQL (would it??) :\

IN ALL CASES, irrespective of what method you choose... make sure you are installing the SAME VERSION OF HMS on the new server for restore as was used on the old server for backup. (You can do HMS upgrade of programs after restore if you wish)
This is what I would do...

1. Install MySQL (Not last few versions though, there are issues with expiry of passwords. I'm on MySQL 5.5 and it works well. You could use latest versions but mitigate against auto expiry of passwords)
2. Install same version of hmailserver to same path, only use the external MySQL
3. robocopy your data directory in preparation
4. Stop both servers (use pause button in GUI, keep service running) - this will stop new mail coming in and changing stuff
5. robocopy again, while...
6. dump MySQL using MySQL backup tools (command line is fine, as is any MySQL Management utility that you already have, including workbench).
7. restore MySQL to new server using MySQL restore tools (as above)
8. Test, test and test some more
9. change router / mx records to point to new server
10. Start New Server

steps 5 - 8 could be done a few times to test test test without affecting live mail flow if you like.

Robocopy is great, MySQL dump using tools is quick and easy, as is restore. You can dump from a live system if you are keen.

Can MySQL and MS SQL Express co-exist? you bet!! MS does things differently than everyone else, and I have many machines with multiple types of databases installed.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
jimimaseye
Moderator
Moderator
Posts: 8095
Joined: 2011-09-08 17:48

Re: 250k message 50Gb hMailServer migration

Post by jimimaseye » 2015-03-25 14:14

iprat wrote:is there any technical problem with making the backup without compression ?
YES. Absolutely. if you do not use compression then the restore of the message data WILL NOT work (you will not have the messages restored). See the link I posted to discussion on this fact. Strange, I know, but it is what it is (for the moment anyway).

The backup and compression of your messages will take about 1.5 hours with the internal backup procedure I reckon - it took mine 50 minutes to do 9gb of data (no comparisons to SPEED of machine hardware made)

As time is your priority and you are willing to have MySQL and MS SQL coexisting, then for sure I would follow Matts suggestion. (If you think downloading, installing etc will be quicker than the couple of hours using internal backup)

But it would be REALLY nice for information and a test case for you to do the internal backup method as a proof of concept. :-D
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

iprat
Normal user
Normal user
Posts: 247
Joined: 2005-05-20 16:50
Location: Barcelona, EU
Contact:

Re: 250k message 50Gb hMailServer migration

Post by iprat » 2015-03-25 14:20

Welll the answer was quite fast, here's the log of the backup that terminated very soon:

2015-03-25 12:59:03.337 Backup started
2015-03-25 12:59:03.368 Loading backup settings....
2015-03-25 12:59:08.649 The size of the data directory exceeds the maximum RECOMMENDED size for the built in backup (1.5GB). Please consult the backup documentation
2015-03-25 12:59:10.321 BACKUP ERROR: The size of the data directory exceeds the maximum size for the built in backup (15GB). Please consult the backup documentation.

The 1.5GB seems a warning, the 15GB seems the absolute limit.

Wouldn't it be a good idea to modify backup option to make internal database backup and external files independent (for example Robocopy), and in that way the internal backup only has to do database backup tasks ?

Having found this stopper, I'll try the mattg way ;-)

Let's install latest MySQL, try to disable auto expiry passwords, and I have read something about disable INNODB to consume less resources... any comments about this ?
My perfect combination:
hMailServer 5.6.1 (B2208), ASSP 1.3.3.8 (antispam), Clamav 0.98.6 (antivirus)

User avatar
jimimaseye
Moderator
Moderator
Posts: 8095
Joined: 2011-09-08 17:48

Re: 250k message 50Gb hMailServer migration

Post by jimimaseye » 2015-03-25 14:48

iprat wrote:The size of the data directory exceeds the maximum size for the built in backup (15GB). Please consult the backup documentation.
I find this shocking.

I knew there was the RECOMMENDED limit (which I have discussed before and doubt why it should be so) but I had no idea they have ACTUALLY HARD CODED a stop on it. It just doesnt make sense. And I simply cannot find a comment from martin as to why this should be the way.

(BTW, I personally have no comment on the MySQL questions other than the password expiry issue. That said I am using 5.5 and it seems pretty solid to me and doesnt have the password expiry feature).
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
mattg
Moderator
Moderator
Posts: 20028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: 250k message 50Gb hMailServer migration

Post by mattg » 2015-03-25 15:11

jimimaseye wrote:
iprat wrote:The size of the data directory exceeds the maximum size for the built in backup (15GB). Please consult the backup documentation.
I find this shocking.

I knew there was the RECOMMENDED limit (which I have discussed before and doubt why it should be so) but I had no idea they have ACTUALLY HARD CODED a stop on it. It just doesnt make sense.
The GUI says maximum of 1GB - which it used to be

It now uses 7Zip which allows a larger ZIP file size, hence now the 16 GB hard coded limit.
32 Bit systems also have file size restrictions....
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
jimimaseye
Moderator
Moderator
Posts: 8095
Joined: 2011-09-08 17:48

Re: 250k message 50Gb hMailServer migration

Post by jimimaseye » 2015-03-25 16:04

Indeed, I understand it uses 7zip, but 7zip doesnt have a filesize limit. So why should it be hard-coded into the program? Neither 8Zip or NTFS has 16GB as a file size limit. (as for limits to file sizes due the filesystem, well that is just for the admin bod to know and understand his own system of choice). Anyway, I would really like this discussed and explained by martin as it is a perfect example of how it limits a user to do what is otherwise a perfectly reasonable manoeuvre (migrate to new server) despite being given the freedom to easily end up with data well in excess of this limit.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

iprat
Normal user
Normal user
Posts: 247
Joined: 2005-05-20 16:50
Location: Barcelona, EU
Contact:

Re: 250k message 50Gb hMailServer migration

Post by iprat » 2015-03-26 15:26

Hi everyone:

At the end, I just installed MySQL last version and in a few minutes with robocopy and MySQL dumps it was all on the new server. I have cleared the old paths to messages on the database also.

The procedure Mattg should be stick somewhere because it is very easy to follow. I already knew how to do all steps, but having those steps confirmed by someone with experience is very valuable.

My only problem has been to figure out that Windows Server 2012 R2 integrated Firewall was leaving hMailServer in the dark XD XD XD, coming from a Windows Server 2003 I had not thought about that feature.

Thanks to everyone in this thread ;-)

In special thank you very much Mattg :-)
My perfect combination:
hMailServer 5.6.1 (B2208), ASSP 1.3.3.8 (antispam), Clamav 0.98.6 (antivirus)

User avatar
mattg
Moderator
Moderator
Posts: 20028
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: 250k message 50Gb hMailServer migration

Post by mattg » 2015-03-26 23:25

You're welcome
Glad that it worked out for you

Thanks for the feedback.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Post Reply