Moving Database

Support
Log in to reply
  • B

    Hello-
    Currently using toem with a very simple infrastructure, and really only for toec (not imaging... yet.) The entire application and all components runs on one cloud based windows server.

    Looking to offload the DB to a different box to help performance as we add more and more machines to the toec environment.

    Does anyone have a high-level process for how I should go about this? I'm assuming something like this:

    1. Shut down IIS on server
    2. Take backup using mysqldump, turn off local mysql service
    3. Restore backup on new server box
    4. Change config files to reflect new DB server - this part I'm unsure of, not sure which files will need to be edited
    5. Start up IIS again

    Let me know what I'm missing.

    Thanks!
    -Bryan

    0
  • T

    That should do it. The config files are the 2 web.config files for the toec-api and toems-api, they are pointing to localhost, just update the ip and username / password.

    B 1 Reply 0
  • B

    @theopenem_admin
    Thanks!
    Making it a bit trickier- I'm trying now to move from the mysql server to MS SQL server. I can connect from my toem server via HeidiDB to the on-prem SQL server no problem, but when I try to log in via TheOpenEM, I get an error: Unknown error obtaining token.

    Here is my connection string.

    <add name="toems" connectionString="Data Source=[server ip],[port number];Initial Catalog=theopenem;Integrated Security=False;User Id=toemuser;Password=[password];MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

    Do I need to change anything else to switch from MySQL to MS SQL Server?

    Any suggestions?

    B 1 Reply 0
  • B

    @brywhi
    I originally also tried adding Encrypt=True;TrustServerCertificate=True in my constr but it wasn't working so I removed those parts. I'd prefer to have encryption enabled though, as the DB is not on the same LAN.

    B 1 Reply 0
  • B

    Hi All,
    Just checking to see if this is even possible or if I should revert back to MariaDB.

    0
  • T

    I would stick with mariadb. In the beginning I used both, but no one uses ms sql, so I rarely test things with it, let alone trying to move from mariadb.

    B 2 Replies 0
  • B

    @theopenem_admin
    Sounds good, thanks for the confirmation! 🙂

    0
  • B

    @theopenem_admin
    Still no luck.

    Here's my connection string in Toems-API\Web.config and Toec-API\Web.config:

    <add name="toems" connectionString="server=10.5.96.3;port=3306;database=theopenem;Uid=theopenemuser;Pwd=[password here];Allow User Variables=True;" providerName="MySql.Data.MySqlClient" />

    I can log in successfully using HeidiDB from TheOpenEM server, using this same connection info.

    I can query tables in the new server and they appear to be identical to the old server. I can see salts and hashes in toem_users.

    The error is still "Unknown Error Obtaining Token" when trying to log in.

    Any ideas?

    Hitting up :8888/Provision/VerifyDb in a browser returns "45".

    0
  • T

    It's possible your sqldump didn't grab everything. mysqldump doesn't get everything by default, you need

    --events --routines --triggers
    0
  • B

    @theopenem_admin
    Thanks for the hint- you lead me in the right direction.

    I fixed it. My fault- I had created some triggers on a few tables for some additional reporting I wanted to do, and they had a definer user specified in the mysqldump backup that didn't exist on the new server. After changing the definer to root in the mysqldump prior to recovering, everything is working!

    0