Our internal DB is growing in size (36GB +). Searching through the KB and opening a ticket I was told we can transfer to a external MySQL DB using HeidiSQL but the same cannot be done to a external MS SQL DB. Has anyone tried to move from the internal DB to a MS SQL DB?
I've never heard of anybody doing it and unless you are really comfortable with SQL, you may not be successful.
I suspect the only way to do it would be to do a mysqldump, which dumps all the data in the form of SQL commands (create table blah, insert into table blah, etc.). Then you would need a program to remove everything except insert statements. In Web Reporter point your DB config to the SQL Server which will create the tables. Then you would need to use a bulk loader to load the data into SQL server. I could imagine there could be a problem with constraints, so the insert order would matter.
But in my opinion, MySQL will work just fine for your DB size. If you need to move it to an external MySQL server, you don't need to use Heidi SQL. In my experience, it is actually faster to reimport all the logs. You can move the DB much faster by copying the data files.
1) Install your new MySQL server. It is recommended that you have a minimum free disk equal to your DB size.
2) Log into your new MySQL server using a mysql client.
3) Make note of your MySQL server's data directory (show variables like 'datadir';) Modify your mysql.ini file and restart the service if you need to update the directory.
4) Create an empty database called "reporting".
5) In Web Reporter, take the DB offline on the DB setup screen.
6) Stop the Web Reporter Internal DB service, and stop the new MySQL server service
7) Copy the data files from your internal DB (.../reporting/mysql/var/reporting/*) to the data directory of the new DB (<data_dir>/reporting/*)
8) Start the new MysQL server. You data has been moved and you should see it in your mysql client.
9) Go into the Web Reporter GUI and modify your DB config to use your new server.
It is important that you take the DB offline. If you stop the Web Reporter service for step 5 instead, you still need to start the service to point it to the new DB. But when it starts, it will still try to use the internal DB.
I was planning on installing WebReporter 5.2.1 (64-bit) on a new server and copying all the config and database to the new server. If I install MySQL on the WebReporter server, will the above steps apply in importing the internal database from the old WebReporter server (having WebReporter 5.1 - 32-bit) to the new one?
Do you currently have a support ticket open? Could you send me your ticket number in a message.
If you are moving Web Reporter to a new server, and doing an Upgrade, I recommend you do them as separate steps. First move to the new server on the same version, then do your upgrade.
You could move the internal DB to a newer version, and Web Reporter would be able to update the schema, but it would be tricky to get the timing right for moving your config if you don't know what your doing.
I have PMed the support ticket number. Thanks.
Following the steps mentioned in the KB, will the steps you mentioned apply in moving the internal DB to MySQL DB?
The instructions for backing up data for a disaster recover scenario, or upgrading, or moving to a new server are 80% the same. There are only small differences to the order of steps, or which files need to be copied, etc. You should just try to do the upgrade spearately from moving the server and/or the database. If carefully planned, they could be done together, but it's not worth the effort.
The instructions I typed above cover moving the internal DB to an external DB only. The KB above covers moving Web Reporter to a new server or directory. Obviously, if you already moved the internal DB to a separete server, you can skip copying the internal DB.
So I know you are currently using version 5.1 with the internal DB, and you want to upgrade to 5.2.1, but you haven't been clear if you are only moving the DB or if you are moving the Web Reporter server too. Give me a clear picture of how you want to move the services and I can give you percise instrctions.
1) What is your motivation for moving Web Reporter? Only disk space, or better hardware, etc?
2) Is Web Reporting moving to a new server?
3) Is Web Reporter database moving to an external MySQL server? It doesn't matter if it is on the same machine of Web Reporter in regards to instructions.
I want to move both the WebReporter and DB to a new server. I have not done anything as of now.
1) Yes it is for disk space and newer hardware
3) As for this, the product guide and the support suggests to move from internal DB to an external DB so yes.
We usually recommend an external MySQL DB because it gives you control over the performance tuning, and you can use a x64 bit version. Although Web Reporter is 64-bit, the internal DB still uses 32-bit process.
The only dis-advantage of using an external MySQL DB is that our installer cannot check the DB size and prevent upgrade if you do not have enough free disk space.
Web Reporter upgrades could require a schema upgrade, depending on from-to versions. Due to the way MySQL moves data to the new schema, it requires enough free disk space to copy the data from existing tables to new data files. So if you have a 30GB database you should have at least 30 GB free for the schema upgrade. After the upgrade, the total db size will only increase 10% at most. Our installer checks for free disk space based on the current size of the internal DB. If you use an external DB, you are not protected, so you just need to check the requirements before you upgrade.
You should always keep enough free disk space equal the the size of your DB with MySQL. make sure you plan accordingly.
Regarding the size of your DB, don't be offended, but 35GB is very small compared to some companies. To put that into perspective, I know of at least one company using MySQL on very expensive Solaris hardware with a DB size of around 2TB. I would say under 100GB is most common, but 200GB isn't unusual either. You should have no problems with performance. I suspect any performance issues you are having now are due to 2 things.
1) The version you are running is missing several optimizations in the current release not related to the DB, such as query optimizations.
2) You have not run DB maintenance in a while (KB68894). This is done automatically since 5.2.0 release as part of the "Index Maintence" feature. Keep in mind that MySQL could copy data to new tables, so you need enough free disk space to copy your DB.
My recommendation is to do this.
1) Move Web Reporter to the new hardware, keeping with the internal DB. If you haven't already provided us the build number of your current version of Web Reporter, let us know and we can get you the same version you need for moving.
2) Move the DB to an external MySQL server as I described above.
3) Upgrade Web Reporter to the latest version (5.2.1.00). You will need to perform a schema upgrade which may take a couple hours. Send us your server.log if it doesn't. I will upload the Web Reporter Migration guide which gives you all the information regarding the upgrade.
Message was edited by: sroering on 2/1/13 12:22:07 PM CST
Thanks sroering, I will check and let you know how it turns out.
Thanks for all the support. Was able to successfully migrate from internal DB to external MS SQL 2012 DB.