Migrate database MySql to MS SQL External Engine

Use this forum if you want to suggest a new feature to hMailServer. Before posting, please search the forum to confirm that it has not already been suggested.
Post Reply
Stefanos
New user
New user
Posts: 4
Joined: 2021-11-03 20:16
Location: Cyprus

Migrate database MySql to MS SQL External Engine

Post by Stefanos » 2021-11-03 21:03

Hi,

I need your help please..

I have heavy database in mysql which is very slow to send and receive emails and search indexing as well, and i need to migrate from MySQL 5.1 to MS SQL 2019 "12 Cores" without any failure or damage any database..

I have try with ODBC Connector and Microsoft SQL Server Migration Assistant for MySQL i have convert schema, synchronize the data --> migrate the data as well to the new sql server..
use the "hmailserver database setup" and at the end "no database found" :cry: :cry: :cry:

Any one to help me please?

Much appriciated.

Thank you.
S.P

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

Re: Migrate database MySql to MS SQL External Engine

Post by mattg » 2021-11-04 00:21

Best option is to do a FULL backup (selecting all options including compression) using internal backup tools, then restore to new hMailserver setup with the new database

All of the transfer between database types is just handled
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Stefanos
New user
New user
Posts: 4
Joined: 2021-11-03 20:16
Location: Cyprus

Re: Migrate database MySql to MS SQL External Engine

Post by Stefanos » 2021-11-04 00:34

The database is 728GB with all domains and mailboxes..

Do you think that will be okay with the above instructions?

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

Re: Migrate database MySql to MS SQL External Engine

Post by mattg » 2021-11-04 01:40

Surely you mean the datastore is 728 GB not the database

Depending on which version of hMailserver you are running this may or may not be possible - that is a lot of data. All you can do is try a backup, and see if it works.

Have you considered simply updating the MySQL to a newer version? 5.1 is pretty outdated
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

tunis
Senior user
Senior user
Posts: 297
Joined: 2015-01-05 20:22
Location: Sweden

Re: Migrate database MySql to MS SQL External Engine

Post by tunis » 2021-11-04 10:06

I think you only need to run backup from hmailadmin and deselect messages.
Run DBsetup.exe and setup MS SQL.
Restore backup in hmailadmin and select settings and domains.

I have not test it.
HMS 5.6.8 B2534.28 on Windows Server 2019 Core VM.
HMS 5.6.8 B2538.30 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

Stefanos
New user
New user
Posts: 4
Joined: 2021-11-03 20:16
Location: Cyprus

Re: Migrate database MySql to MS SQL External Engine

Post by Stefanos » 2021-11-04 10:54

Dear All,

Database still not found.!!!

But when go to create new database and setup by default MS SQL 2019 with the Hmailserver with the latest version and after restore only the domains and settings because the messages is not available. Is OK..

the messages to the inbox everything is empty.. i have go to the data directory synchronizer.exe and start to sync all domains and inbox.. BUT ALL SEND ITEMS AND SOME SUB-FOLDERS IS NOT AVAILABLE STILL EMPTY..

Any advise?

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

Re: Migrate database MySql to MS SQL External Engine

Post by jimimaseye » 2021-11-04 13:24

You MUST include messages in the backup and restore (with the 'compress' feature) for it to work. If you back only domains and accounts you will only get domains and accounts. And running Syncroniser will simply put ALL meassage in to the approriate account INBOX.
5.7 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

Stefanos
New user
New user
Posts: 4
Joined: 2021-11-03 20:16
Location: Cyprus

Re: Migrate database MySql to MS SQL External Engine

Post by Stefanos » 2021-11-04 13:27

When im going to restore with Full Backup it will show "message" not availbe to "tick" in order to restore the messages. maybe because the database is 728GB ?

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

Re: Migrate database MySql to MS SQL External Engine

Post by jimimaseye » 2021-11-04 16:25

jimimaseye wrote:
2021-11-04 13:24
You MUST include messages in the backup...(with the 'compress' feature)
If you don't then they won't be offered to be restored.

(And for something that large i expect you might not be waiting long enough for the backup to complete)

[Entered by mobile. Excuse my spelling.]
5.7 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