Jump to content

Automation Theory

Members
  • Content Count

    11
  • Joined

  • Last visited

  • Days Won

    1

Automation Theory last won the day on March 14

Automation Theory had the most liked content!

Community Reputation

2 Neutral

My Information

  • Agent Count
    > 6000 Agents

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. @tlphipps I don't want to hijack this thread for IIS tuning, but I'd guess your server might benefit from that too. I normally advise increasing the threads in the application pool (CPU core count - 1 is the traditional wisdom) and setting the app pool to recycle regularly (I recycle every 90 minutes, but every 8 hours is where I'd start).
  2. @DNA3e8 No issues have been reported, and most users have reported noticeable improvements. The plugin doesn't make any persistent changes. Restarting the LabMySQL service or using the UI button to revert the changes will flush the changes. You can use that functionally to do short term testing (but please note it might take some time for the changes to show). Removing the license key will prevent the plugin from performing the initial tune when the server starts. Let me know if you have any other questions!
  3. That definitely sheds some light on the situation. Let me explain some order-of-operations items here: When the plugin initializes it checks if it's table plugin_databasetuner_general exists, and creates it if needed. The first line of the script creates the stored procedure. If the procedure already exists step one of the script will fail. The second line of the script sets a value of '1' to the column sp_created in the database table. If the plugin has not created the table this step will fail, and without this value the plugin assumes that the stored procedure is missing and won't run the tune. @chris_bb run the following script to see if the stored procedure has been created (the log should say "labtech"). If so just disable the first line of the creation script, and re-run it so line 2 will set the value in the plugin's table. [For on-premise servers this'd be a single query, but since we can only hit hosted DB's through the app, this is the clunky work around.] Show Database Tuner SP.xml
  4. @chris_bb I upgraded my test server to 2020.3 and I wasn't able to duplicate your error message. The first thing I'd check is permissions (must be a Super Admin). Otherwise it might be worth while to copy/paste the commands into a new script; it might be import strangeness between Automate versions. Let me know how it goes.
  5. The root password is something that is easy to forget about until you really need it. Then it becomes a wild goose chase looking through password managers, documentation systems, and any other place that password might be used. This guide is meant for the situation where database maintenance is required and the root password is unknown. Items worth noting Every super admin account in Automate has the same permissions in the database as the root user. If your server has been around for a while (pre 10.5) it might be plain text in the registry under the key HKLM\Software\WOW6432NODE\LabTech\Setup There's nothing special about the root account -- it's very similar to Administrator in Windows. Best security practice is to create a new account with the super permission and not use root (unfortunately in the CW Automate world this best practice is difficult to implement). IMPORTANT: This guide is actually going to instruct you how to create a new account with the super and grant permissions -- we won't actually be resetting the root password. The root password used at server install is encrypted and stored in the registry and is used by Automate patches and other support utilities. If you change the root password you're going to break things in Automate down the road. An example, the 2020.2 patch: Now, without further ado, the new super account creation process. To begin, here's a screenshot to show that we're locked out of a new MySQL 5.7 instance: [In case you're wondering, MySQL 5.7 randomly generates a root password if one is not specified, and it's recorded in the error file.] Create a text file with the following commands, and save it as a .sql file: CREATE USER 'rootbeer'@'localhost' IDENTIFIED BY 'abc123!'; GRANT ALL PRIVILEGES ON * . * TO 'rootbeer'@'localhost' WITH GRANT OPTION; GRANT PROXY ON ''@'' TO 'rootbeer'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; I'll be calling the account 'rootbeer' and using an insecure password for the sake of demonstration. Good security practice is strongly advised. Stop the service LabMySQL Open an administrative command prompt, and navigate to the bin folder of your MySQL install, wherever mysqld.exe is located. Start MySQL passing the script you created in step one as the init-file parameter. mysqld.exe --console --init-file C:\path\to\reset.sql When the server starts it should show in its output that it ran the script. Press CTRL+C to shutdown the server once you see this. As you can see in the other window, we're successfully able to login. Start LabMySQL again. Login as your new super account. As you can see here, ours has all global privileges and the DBA role. 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/
  6. Yes, it wouldn't hurt to stop the front end services -- but since we stop the DB you won't have data inconsistencies. As for your password reset -- the MySQL manual has a nice reference for this. You'll create a file with the SQL statement to reset the password and call it with the --init-file=C:\path\reset.sql switch. Please keep in mind that user accounts in MySQL are host specific (my prod Automate server has 3 root accounts). If you moved hosts you might need to create another account. Since your new instance still exists you can troubleshoot at your leisure and then re-migrate once you have the kinks worked out.
  7. 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/
  8. Update: The first patch has been released for the plugin. It includes a new tuning optimization (reducing overhead from row locking). This new feature brings the benchmarks to 21% faster than the stock config. The ZIP file in the KB article has been updated with the latest version of the plugin (version number should read 2020.0309).
  9. There are two issues at play (if I'm understanding you correctly): The tables were missing (known phenomenon) When you created the tables the permissions were absent (this is what mysql_upgrade fixes). mysql_upgrade is a utility that is meant to smooth out database instances after upgrades (it's not a reference to the mysql internal database). For what it's worth the folks at CW are wonderful application specialists, but they are not database administrators.
  10. Yes, you can definitely upgrade to 5.7. Here are a few thoughts: The MySQL docs advise that you install the latest patch of your current version before upgrading to the next one (that's 5.6.47 currently). The Automate documentation for server setup references the MSI installer flavor to install MySQL. The .zip file method is a lot easier. Per the docs, you'll want to run "mysql_upgrade" to make sure everything is smooth post-upgrade. This does update your grants (permissions), and if I had to guess that's the issue you were having with SQLyog. Docs: https://dev.mysql.com/doc/refman/5.7/en/windows-upgrading.html Also, please note that 5.6 is loosing support next year (and that Oracle patches MySQL ~ quarterly). I highly advise keeping the DB patched for a high security system like an RMM. I do this professionally, so if you have any hiccups just let me know (DM me if you want to farm this out).
  11. I think every Automate admin has experienced the frustration of how slow the desktop application is. My primary Automate server has ~10,000 agents, and my users were having a horrible experience on a daily basis (they named the agent context menu "the right click of death"). I was sick of the painful user experience and the administration headaches -- so I upgraded to MySQL Enterprise and did a deep dive into how the application interacts with the database. Using the management tools from Oracle I found lots of issues, and after correcting them it's a night-and-day difference (the right click of death is gone!). Each Automate deployment is unique, and there's no hard code-able one-size-fits-all approach. However, I created a plugin that will dynamically gather information from the current configuration and adjust MySQL on the fly to optimally perform for Automate. I published a blog post on my site explaining more of the details: https://automationtheory.org/the-mysql-performance-problem/ I wanted everyone to be able to have a properly setup database without having to buy MySQL Enterprise (or be a DBA on the side), so I created a plugin that does all the dirty work. I plan on releasing this plugin as a monthly subscription, but first I want to do some beta testing to verify results on different Automate deployments (to get a good mix of sizes and types). To get started go to https://automationtheory.org/support/kb/faq.php?id=1 The KB article has a download for the plugin and the setup script, along with instructions. Feel free to reach out with any questions or comments.
×
×
  • Create New...