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:
- Shut down IIS on server
- Take backup using mysqldump, turn off local mysql service
- Restore backup on new server box
- Change config files to reflect new DB server - this part I'm unsure of, not sure which files will need to be edited
- Start up IIS again
Let me know what I'm missing.
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.
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?
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.
Just checking to see if this is even possible or if I should revert back to MariaDB.
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.
Sounds good, thanks for the confirmation!
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.
Hitting up :8888/Provision/VerifyDb in a browser returns "45".
It's possible your sqldump didn't grab everything. mysqldump doesn't get everything by default, you need
--events --routines --triggers
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!