Search the Community
Showing results for tags 'migration'.
Found 2 results
Hi Everyone, Since ConnectWise no longer supports MariaDB I've created a guide to help partners migrate to MySQL. The vendor provided documentation involves a long and convoluted logical backup process that can be avoided. This guide is the fastest method of upgrading to MySQL while allowing 100% ability to revert back in case of failure (since we'll be leaving MariaDB intact). Before we go any further please note that you can farm out this task if you want to focus on automation and not database administration: https://automationtheory.org/product/mysql-patching/ Now, onto the upgrade process (with pictures!)! Please note that this is a development server with four different versions of SQL installed -- you may see items in screenshots that are not applicable to a normal server (like LabMySQL not running when restarting the Database Agent). Here's a screenshot showing the existing version of MariaDB installed: Before we get started make sure you have your root password and enough disk space to hold twice your DB (we're going to duplicate it in order to do a "fail-safe" upgrade). This guide assumes that the DB upgrade is taking place on the same server and does not cover any application-level changes required for moving the database to a new host. Download and extract the ZIP file installer for MySQL 5.7 (link). Standard deployments extract to C:\MySQL, but it's not all that important (just remember where you put it). MySQL 5.7 does not include a data directory -- this is good, as we're going to do what's known as a physical backup where we move the existing MariaDB data directory to our new install. If you have downloaded MySQL 5.6 for whatever reason it DOES have this directory and it should be deleted. Open up SQLyog (or your editor of choice) and run the command "SET GLOBAL innodb_fast_shutdown=0" (not case sensitive). This will force data in the buffer files to be written to the actual tables (the buffer file formats aren't compatible, so we'll be deleting them). WARNING: Failure to do this will lead to potential data loss in three steps! Stop the service LabMySQL. Once the service has stopped copy the entire data folder from the MariaDB base directory to the extracted MySQL base directory. Also, your my.ini file can be moved there (MySQL will automatically apply it on startup without special instructions if it's in this location). Delete the files ib_logfile0 and ib_logfile1 (and if you're running an exotic configuration and you have more than two ib_logfileX files delete any others). Open an administrative command prompt in the bin directory of the new MySQL server and run the command "mysqld.exe --console". Scary error messages are to be expected, but the application should stay running. If it closes something has gone wrong (feel free to troubleshoot with the provided console messages -- but please note that this would be a good time to get a second opinion if the issue seems exotic). Open a second administrative command prompt in the bin directory (leaving the other one open). This time run the command "mysql_upgrade.exe -u root -p --force". This command will produce lots of crunchy messages; it's MySQL seeing the table files in a different format and doing all the under the hood conversions to make them match MySQL 5.7 formatting (note: the screenshot shows the wrong syntax). Once the mysql_upgrade has finished, go back to the command window where the server (mysqld) is running. Press CTRL+C to stop it, then up arrow to get the start command and re-launch it. When the server starts up it should have fewer error messages, but it will likely have errors about the mysql.user table. If you do not see errors about the mysql.user table, skip this step. Otherwise, open SQLyog and connect to the DB (note: if you can't get logged in this would be another good stopping point for a second opinion). Run the following commands to remove the extra columns that MariaDB has that MySQL doesn't. alter table mysql.user drop column default_role; alter table mysql.user drop column max_statement_time; alter table mysql.user drop column password_expired; At this point, it's worth repeating steps 8 and 9 to make sure that the application will be happy when connecting. Restart the Database Agent service. In the console you'll see some failed login attempts; this is normal. After 1-2 minutes try logging into the application. As shown here, success! We have upgraded to MySQL 5.7. Important: We're not done yet! The service LabMySQL is still pointed at the old database instance. If the console closes or the server reboots you'll lose MySQL. To correct this close the application, stop the server, delete the old service (sc delete LabMySQL), and run the following command "mysqld --install LabMySQL". Please note that you can add the option " --defaults-file=C:\path\my.ini" but as previously mentioned this is not required if the my.ini file is in the base directory of the MySQL server. At this point, MySQL should be the persistent database for the Automate server. Start the service LabMySQL, restart the Database Agent, and verify the application launches. To be extra sure you can restart the server to make sure that the service starts on boot. Please note that the old database is 100% intact at this point. Hopefully this guide has been useful to you. If you ever need support for the Automate database and want a professional DBA opinion feel free to reach out to us: https://automationtheory.org/
CW Automate Database Migration Guide I recently performed a successful database migration for our org to a new server with higher specs. I spent some time with CW consulting before the migration, reviewing the new server base install and working through some of the initial issues there (like making sure all our connectors really ARE the right version, and dropping the default anonymous MySQL user). They also gave me some pointers for the actual database migration process which are not in the CW documentation. This guide is not a replacement for proper consulting. If you don't know what the stuff below means, you should probably stop and have a talk with CW about getting assistance . In our case we were migrating the database to a single server, but I would expect some of these same steps could be utilized for someone going from a single server to a split server as well. ASSUMPTIONS: This guide does NOT cover the Automate 12 base install on the target server. It assumes the target server has all firewall ports opened, Automate base install completed, trial license and services are all working. Complete these steps ahead of time, and the steps below can be used as a checklist for performing the actual cutover. Server Installation Reference: https://docs.connectwise.com/ConnectWise_Automate/ConnectWise_Automate_Documentation/020/010 CUTOVER CHECKLIST • Firewall ○ Check and confirm prerequisite access rules are in place (should mirror current Automate server) • DNS ○ 1 day prior, drop TTL for A record to low value (1800) ○ Start time, update A record to new destination, keep TTL low • Source server ○ Set maintenance mode at all agents level ○ Stop/disable Automate services ○ Export database structure and data ○ Reference: https://docs.connectwise.com/ConnectWise_Automate/ConnectWise_Automate_Documentation/020/070 ○ Ignore history tables, eventlogs, windowsupdateetlfiles tables from data export ○ Copy export to new server • Target server ○ Install all current Windows Updates ○ Disable automatic restart outside of active hours https://docs.microsoft.com/en-us/windows/deployment/update/waas-restart#registry-keys-used-to-manage-restart ○ Stop Automate services ○ Export lt_servers and lt_servers_sec ○ Drop labtech ○ Create labtech ○ Import old server exported structure, then data ○ Discuss: Observed some duplicate primary key errors on import - had to uncheck box "abort on error" ○ Copy LTSHARE data from old server ○ Truncate lt_servers and lt_servers_sec ○ Import from new server data export ○ Set Labtech Redirector Service to delayed startup ○ Start services ○ Discuss: Start/stop errors on Solution Center service. Required. Initial errors logging in to Control Center / high CPU usage on mysqld. Waiting for initial read/hashing of Transfer folder items to complete. ○ Change license key ○ Run latest monthly patch again ○ Restart ○ Update Automate server to computerid 1 ○ Check plugins, agent status ○ Check for agent check-ins, command execution ○ Take agents out of maintenance mode ○ Set up azure blob backup job scheduled task