Change to a different database?

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
pjbeee
New user
New user
Posts: 4
Joined: 2014-08-22 13:44

Change to a different database?

Post by pjbeee » 2017-11-27 22:35

I would like to change to MySQL from the default MSSQL CE database. hMailServer is otherwise running fine.

V5.4.2-B1964

Can this be done, and the existing data converted (if necessary)? And if so, I could certainly use some instruction in this.

Thanks!
Last edited by pjbeee on 2017-11-27 23:04, edited 1 time in total.

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

Re: Change to a different database?

Post by jimimaseye » 2017-11-27 23:03

Update hms to latest version.

Run an internal backup with ALL OPTIONS ticked.

Install mysql if not already done.

Then run dbsetup.exe from the hmailserver bin folder. Select mysql.

Restore from backup.

Done.
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

pjbeee
New user
New user
Posts: 4
Joined: 2014-08-22 13:44

Re: Change to a different database?

Post by pjbeee » 2017-11-27 23:47

The database file itself is around 150K, but the email datastore is just under 50GB.

Will the built-in backup run with that much data? And what is the purpose of the restore, if the database change is done in-place? Not doubting you, but I'd like to have an idea of what the "mechanism" is.

And I have lots of spare hardware around, so I would very likely test this entire operation offline.

Thanks!

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

Re: Change to a different database?

Post by jimimaseye » 2017-11-28 00:11

And what is the purpose of the restore, if the database change is done in-place?
The purpose is because the database is initialised (in this case in MySQL) and therefore empty - the restore then reimports the contents of it when you do the restore.

Changing database is never an easy step and it the reason to make careful considerations when first implementing. The procedure outlined above it the only way to be sure of a trouble free operation........ provided that you dont have too much data. Unfortunately your mail store (50GB) will now be seen as being too large and will prevent the internal database from working.

HOWEVER..... version 5.7(alpha) version doesnt have this restriction. So the following procedure can do it for you. It has been done before and is safe but of course you will be testing first anyway.

1, Upgrade HMS to latest 5.6.5

2, Check it by following this: viewtopic.php?f=21&t=28914

3, Check functionality and that all is still ok.

4, Upgrade HMS to 5.7 from here: https://build.hmailserver.com/viewType. ... ilServer57

5, Run an internal backup with ALL OPTIONS ticked (including messages + compress!). This will take a long time! Be patient (monitor task manager).

6, Install mysql if not already done.

7, Then run dbsetup.exe from the hmailserver bin folder. Select mysql.

8, Restore from backup. (More help here: viewtopic.php?f=21&t=29726)

To resort back to production (recommended):

9, uninstall Hmailserver completely

10, Re-install Hmailserver using latest production (https://www.hmailserver.com/download)

11, Restore from backup

Done.


EDIT:

Oh dear. The build server that the alpha products are built on seems to be offline. Your stuffed then. (Hope its temporary - we have never known it to be offline before).

EDIT2: I gave a copy of 5.7 install here but it is too large to attach to this forum. If you want it then let me by PM and I will arrange a private download link
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

pjbeee
New user
New user
Posts: 4
Joined: 2014-08-22 13:44

Re: Change to a different database?

Post by pjbeee » 2017-11-28 00:15

Many thanks! I will indeed be testing first. If you or anyone else has anything additional to offer, bring it on.

Cheers!

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

Re: Change to a different database?

Post by jimimaseye » 2017-11-28 00:19

See my EDIT's above.
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: 19894
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Change to a different database?

Post by mattg » 2017-11-28 01:19

RvdH's latest build here also has that backup limit removed >> viewtopic.php?f=10&t=30193#p192583
jimimaseye wrote:EDIT:Oh dear. The build server that the alpha products are built on seems to be offline. ... (Hope its temporary - we have never known it to be offline before).
Has been for a few weeks. Looks like the DNS record has been removed...
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

pjbeee
New user
New user
Posts: 4
Joined: 2014-08-22 13:44

Re: Change to a different database?

Post by pjbeee » 2017-11-28 01:23

Guess there's a silver lining, in that you now know about it. No big rush here.

BUT I have downloaded the file. Is this issue relevant to me?

Just informed another company (telephone hardware supplier) that their toll-free number was down. They had no clue. Stuff happens.

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

Re: Change to a different database?

Post by jimimaseye » 2017-11-28 01:33

mattg wrote:RvdH's latest build here also has that backup limit removed >> viewtopic.php?f=10&t=30193#p192583
Ah good to know. And from a trustworthy unofficial version of hmailserver.
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
jimimaseye
Moderator
Moderator
Posts: 8017
Joined: 2011-09-08 17:48

Re: Change to a different database?

Post by jimimaseye » 2017-11-28 01:37

pjbeee wrote:.
BUT I have downloaded the file. Is this issue relevant to me?.
You're ok to carry on with what I have given you and the instructions. But you may wish to simply do the first upgrade to the unofficial version on the thread quoted above and use that as your main system and not bother with going to 566 then 57 and back to 566 again.
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
Dravion
Senior user
Senior user
Posts: 1402
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Change to a different database?

Post by Dravion » 2017-11-28 03:42

Hmm, in theory the only thing what takes a hugh amount of time durning backup/retore to a new Database is to handle the data\*.eml files and its mime encoded multipart attachements.Exporting the plain MSSQL CE tables and re-importing the tables to MySQL should be a matter of seconds.

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

Re: Change to a different database?

Post by mattg » 2017-11-28 03:55

Sure, if you write a script that exports the tables into say XML and re-imports them into the new database

The built-in backup will drop all messages if you select to backup with the built-in backup, and not backup messages as well.

Can you write SQL to dump the database from MS SQL CE and then re-import to MySQL? I probably could, but I'd need to test it and it would take quite a few hours of my time to complete. The backup solution backing up everything works, and is tried and tested...
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: 8017
Joined: 2011-09-08 17:48

Re: Change to a different database?

Post by jimimaseye » 2017-12-01 15:23

Interesting.

See viewtopic.php?f=10&t=30900 and look at the backupmessagesdbonly (3rd from last) option.
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: 19894
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Change to a different database?

Post by mattg » 2017-12-01 22:30

Especially read this bit 'Understand even set to 1 doing a restore WILL ERASE DATA TREE! '

This is likely to erase your data, and was experimental at best.
Bill was more interested in archiving mail than doing backups, and had a completely different interpretation of what a backup actually achieves than I do.
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
Dravion
Senior user
Senior user
Posts: 1402
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Change to a different database?

Post by Dravion » 2017-12-01 22:53

Imho its not about backup in this topic rather then switching to a diffrent db type, using hmsbackup to do so.

Bottomline:
This is just a matter of running a few SQL-Queries on the Target DB (Create Tables, 2x Stored Procedures for Postgres and a lot of Data records inserted via Standard SQL INSERT commands).
There is no need to backup/restore the entire DATA Folder for just switching to a diffrent db.

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

Re: Change to a different database?

Post by mattg » 2017-12-02 01:05

I agree, if you know your way around SQL databases, but for someone who doesn't have the necessary database skills, then the EASY way is to use the built in backup and restore

Not all of those who read this forum have the required level of skill to manipulate databases
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: 8017
Joined: 2011-09-08 17:48

Re: Change to a different database?

Post by jimimaseye » 2017-12-02 01:07

mattg wrote:Not all of those who read this forum have the required level of skill to manipulate databases
..... or even run a mail server. :roll:
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

Post Reply