Migrate to new DB

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
rgutery
Normal user
Normal user
Posts: 69
Joined: 2009-12-18 08:55

Migrate to new DB

Post by rgutery » 2009-12-18 09:00

Hello all, I'm new here and still somewhat new to hMailServer.

I've installed and configured it using the defaults, builtin db etc.

We're pretty comfortable with it at this point and are looking at now putting it into production for our small corporate lan. My question would be:

can we migrate our settings from the builtin db to MS SQL? If so, how would one do so?

I'm suspecting some form of Export (but can't see one). We are using SQL2K (yup, reliable and stable), so I don't think we can load the db into SQL2K.

Thanks in advance.

Richard

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Migrate to new DB

Post by ^DooM^ » 2009-12-18 10:15

If at first you don't succeed, bomb disposal probably isn't for you! ヅ

rgutery
Normal user
Normal user
Posts: 69
Joined: 2009-12-18 08:55

Re: Migrate to new DB

Post by rgutery » 2009-12-18 14:24

As stated in my post, I'm new here so forgive me for being thick like brck.

But I am amazed as to the "completeness" of resplies...

I thougth I was clear, I had checked the documents in detail I am quite susinct about doing this before i waste bandwidth on a new (or any) forum.

If you could tell me where in this "magical document" it tells me how to load the hMailServer.sdf in SQL 2K? OR where is theer a discussion regarding Export.

There is absolutely no specific discussion regarding either of these methods.

I sure hope that this is not a foreboading of an example of complete and accurate of an answer one can expect from this "Community Support Forum".

Thanks.

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

Re: Migrate to new DB

Post by mattg » 2009-12-18 14:34

rgutery wrote:
As stated in my post, I'm new here so forgive me for being thick like brck.

But I am amazed as to the "completeness" of resplies...

I thougth I was clear, I had checked the documents in detail I am quite susinct about doing this before i waste bandwidth on a new (or any) forum.

If you could tell me where in this "magical document" it tells me how to load the hMailServer.sdf in SQL 2K? OR where is theer a discussion regarding Export.

There is absolutely no specific discussion regarding either of these methods.

I sure hope that this is not a foreboading of an example of complete and accurate of an answer one can expect from this "Community Support Forum".

Thanks.
Whoa Dude. Manners back in please. We are volunteers here you know and this is a free (as in zero cost) product.

You are making this way too hard for yourself. Why are you exporting the tables and relaoding the hMailserverv.sdf??

Because you are very likely to have a small installation (ie only used for testing)
Backup your system
Create a new database on your SQL2000 using dbsetup.exe in your C:\program files\hmailserver\bin\ folder
Restore your backup to new database.

This is in that document that Doom linked to...
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Migrate to new DB

Post by ^DooM^ » 2009-12-18 16:41

That document gives you instructions on how to migrate to a new server. It's not my fault you didn't read it properly and decided you wanted to copy the SDF files instead.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

rgutery
Normal user
Normal user
Posts: 69
Joined: 2009-12-18 08:55

Re: Migrate to new DB

Post by rgutery » 2009-12-18 17:21

Nothing to do with Manners. The answer was Obtuse and not helpful at all.

I understand what Zero Cost is as well.

Here is an exact extraction from the document that I was pointed to:

<<<
SQL Server

If you want to move your hMailServer database to a new Microsoft SQL Server installation one method is to backup the database and restore it on the new server. If you have created users and logins specifically for the hMailServer, you need to ensure that they still exists and are set up properly on the new installation

>>>

So if you all could tell me how this works, I would be absolutely amazed. SQL2K does not allow one to attach to and SDF file. Nor does it permit one to Import data from an SDF file.

Perhaps the universe did not read my entire post regarding the db I am using - MS SQL2K...

So back to my question: HOW???

User avatar
sheffters
Senior user
Senior user
Posts: 453
Joined: 2009-07-01 20:46
Contact:

Re: Migrate to new DB

Post by sheffters » 2009-12-18 17:41

Nothing to do with Manners. The answer was Obtuse and not helpful at all.
You need to be a bit more polite or people won't even bother replying to your posts. It makes no difference to anyone on this forum if your server works or not; maybe worth remembering people are trying to help you for nil benefit to themselves.

Have you tried using the backup function in the admin console?
backup.jpg
It does restore too ...

Backup existing HMS ... connect it to a new DB ... restore.

Don't know if that will work, but seems the most obvious method to try.

S.

goomba
New user
New user
Posts: 10
Joined: 2009-12-18 18:36

Re: Migrate to new DB

Post by goomba » 2009-12-18 18:56

Well, after reading and using the posts in this forum for our use (and we found it to be quite useful), I have to say that this thread sure seems to have a lot of hostility.

Anyway, I think I understand the original problem as follows:

1) The user wants to migrate the data from the builtin DB to SQL 2K,
2) There is no intrinsic method of importing the data form the buitin DB to SQL 2K (you need at least SQL 2K5),
3) There is no true Export (backup yes) from the builtin DB to a format that one can directly import.

I don't think that the contributors are not quite understanding the above, just my read on this thread.

Here is my two cents worth,

!) The backup utility creates an XML file of the builtin DB. You can't simply back up and restore to SQL2K (or SQK2K5/2K8). The data is not in DB format. The help file suggest "Copying/Moving" to the new system. That is not the case here (at least my understanding). I believe the poster wants to IMPORT the data into SQL2K,
2) To import it into SQL2K, one would have to flat file the data (or use an XML parser to extract the data),
3) Import the flat file into SQL2K.

The above works as these are the steps we took a month ago to migrate form the builtin DB after our initial test.

Hope I didn't offend anyone, just my thoughts.

I like this product and I think this forum is great. However, and in all fairness, I beleive the poster just wanted an answer as to how to IMPORT the data. I don't think that anyone here addressed that issue at all. The last post doesn't help, it still only points out how to backup the db - as I stated above, it's in XML format. So no/little help to the original poster - me thinks.

:)
Goomba.

ps. to the original poster, I have a utility that I wrote sometime ago that will parse, extract and save the data in text/csv file for import (it will also automatically insert into a DB with the appropriate conenction info). Please email me directly and I can send it to you if you like.

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Migrate to new DB

Post by ^DooM^ » 2009-12-18 19:11

There is a link in the doc i posted to this http://www.hmailserver.com/documentatio ... up_restore which tells you howto backup your data :roll:

As this guy has been nothing but rude on this forum I am officially giving him a warning. He either starts treating people with respect or he will be removed to fend for himself.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

goomba
New user
New user
Posts: 10
Joined: 2009-12-18 18:36

Re: Migrate to new DB

Post by goomba » 2009-12-18 19:25

^DooM^ wrote:There is a link in the doc i posted to this http://www.hmailserver.com/documentatio ... up_restore which tells you howto backup your data :roll:

As this guy has been nothing but rude on this forum I am officially giving him a warning. He either starts treating people with respect or he will be removed to fend for himself.
Fair enuff and I certainly don't want to get in the mix here, but I read the info in your link and I think that the point is still being missed.

This tells one how to backup and restore but there is no discussion regarding the "Import" of the data to a foreign db system. I think that is the case here. The backup and restore only allows on to restore to the internal (builtin???) db.

Maybe I'm missing something here with your post, if so I appologize.

In any case, onward and forward. Keep up the good work with this product.

Goomba

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Migrate to new DB

Post by ^DooM^ » 2009-12-18 19:27

Ok heres the quick version

Backup with hMails built in backup tool
Change database to new database.
Run the db installer from the utilitils directory
Restore with hMails backup tool
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

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

Re: Migrate to new DB

Post by mattg » 2009-12-19 00:39

:roll:
Glad I went to bed when I did !!

I'm still not sure that they get it though.

So people, once you have worked out that this isn't about importing but that it is about restoring, and you are still having problems then fell free to ask politely for more help.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Shelby
New user
New user
Posts: 22
Joined: 2007-07-31 18:29

Re: Migrate to new DB

Post by Shelby » 2009-12-19 13:57

Hi guys. I also are looking for how to migrate Hmailserver from one DB engine to another and stumbled over this post. Although I want to migrate from MySQL to the internal database, I'ld guess that the steps of Doom's explanation still apply.

Nevertheless this doesn't help me here because the internal backup tool complains about the size of my database (it's above 1.5 GB) and denies its service. What now? Throwing away mails to shrink the size isn't a way I want to go.

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

Re: Migrate to new DB

Post by mattg » 2009-12-19 16:37

You would need to find a converter somewhere on the net and convert the tables one by one. I think that the steps are

Backup (just in case)
Create new databases, and connect to them
convert tables from MYSQL to MSSQL compact

There are some reasons why I wouldn't want to move an existing MYSQL database to the internal MSSQL Compact edition database that ships with the current release. One is the limitations of that version, another is the seemingly lack of tools to administer that version, things like backup. MSSQL Compact really is intended for smaller databases.

By all means, I'd agree with a trade to a full (paid for)version of MS SQL from MySQL if you had some good reason to do so...

Why do you want to change from MySQL?
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Shelby
New user
New user
Posts: 22
Joined: 2007-07-31 18:29

Re: Migrate to new DB

Post by Shelby » 2009-12-20 00:35

Currently I am using XAMPP on the server. The Hmailserver installation is accessing XAMPPs MySQL daemon and all the Hmailserver data is stored inside the XAMPP installation folders. Although this is working on my systems for years I want to have the XAMPP and Hmailserver installtion independend of each other.

I thought that using Hmailservers "built in" DB engine would be a good alternative. But now hearing of "limitations" makes me uncertain. Probably I am just installing a separte MySQL daemon. Migration would be much easier. Installing a full (and costly) version of MSSQL isn't an option here. It's just my private email on this server so I would be the only one who would have to pay for this upgrade ;-)

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

Re: Migrate to new DB

Post by mattg » 2009-12-20 02:19

Standalone MYSQL is much better idea I reckon.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Re: Migrate to new DB

Post by ^DooM^ » 2009-12-20 12:48

Hopefully when Martin adds support to export/import domains and accounts to the backup tool this issue will no longer apply. I also agree with Matt though. Go for MySQL, There is so much support out there for it and a stack of tools to use with it most that are free. HeidiSQL, Navicat, PHPMyAdmin to name a few. I have also been using MySQL for years with hMail with no issues that weren't of my own fault.
If at first you don't succeed, bomb disposal probably isn't for you! ヅ

Shelby
New user
New user
Posts: 22
Joined: 2007-07-31 18:29

Re: Migrate to new DB

Post by Shelby » 2009-12-20 13:53

Great, thanks for the advises. You've convinced me - I will stay with MySQL. Merry christmas to all of you.

Post Reply