Add support for MariaDB/MySQL or another DB engine for scalability
The weakest link seems to be the SQLite DB. Once the DB gets to 1GB size, the SC application becomes unstable.
Welcome to our community!
Duplicates 3
Actually Support MS SQL
MSSQL
Official support for MSSQL database instead of SQLite would make large on-prem deployments much more manageable.
Officially Support SQL Server
Right now MS SQL Server is not officially supported, but generally works with support in the forums. It would be nice to see full support for MS SQL including SQL Clustering and Always On HA.
Replies 46
Mike - I have to disagree. Extended auditing was operationally useless for us, and support have told us the reason for this is excessive SQLite DB size. When we restarted the server, it took over an hour for SC to become usable and even then it was slow for three more hours. IMO SQLite is not suited to anything like 200GB. It's suitable for more like 200MB. Regardless of the maximum size listed on https://www.sqlite.org/whentouse.html - that doesn't mean it's a good idea to use it at that scale. The lack of caching being one obvious problem (relying on OS FS cache is not as performant as a DB engine taking nGB of RAM away from the OS and dedicating it to the DB table cache). Another is the single writer limitation.
While certainly SQLite makes the system easy to deploy and work with, and while you may say you don't have issues running it at scale it's very clear that many customers do have issues running it at scale (or at least think they do). If you're SURE all these issues aren't related to the DB engine, perhaps you'd like to explain what is causing them and how to resolve them, because support have told us it's a DB scaling issue?
Alex, I had a semi-decent conversation with Sean White over at ConnectWise who is a senior Product Manager for ConnectWise Control but unfortunately I don't think it went anywhere. I'm mainly after high availability which is near impossible with SQLite. There are an abundance of code changes required for HA to be possible due to the fact that the DB is loaded into RAM and thus can't read updated information written to the DB by another node, but a standby node referencing the same DB could be started upon failure of the primary node and theoretically would work fine. I'm exploring this option independently with the current SQLite database but having a central database that both nodes could talk to would be ideal so we don't need to store the SQLite DB on shared/network storage, or implement some type of database syncing.
With the reporting, I basically gave up trying to run queries against the database file. The few times I've done this, it resulted in crashing of the application and in two cases, database loss.
Alex, I had a semi-decent conversation with Sean White over at ConnectWise who is a senior Product Manager for ConnectWise Control but unfortunately I don't think it went anywhere. I'm mainly after high availability which is near impossible with SQLite. There are an abundance of code changes required for HA to be possible due to the fact that the DB is loaded into RAM and thus can't read updated information written to the DB by another node, but a standby node referencing the same DB could be started upon failure of the primary node and theoretically would work fine. I'm exploring this option independently with the current SQLite database but having a central database that both nodes could talk to would be ideal so we don't need to store the SQLite DB on shared/network storage, or implement some type of database syncing.
With the reporting, I basically gave up trying to run queries against the database file. The few times I've done this, it resulted in crashing of the application and in two cases, database loss.
Max, I wish I had a suggestion for HA, but I don't have anything in my toolbox that I'd trust. Don't quote me on this - but I think I've seen it written that it's possible to change SC to another DB engine like PostgreSQL, but it's not supported. As you want HA, I'd assume you'll be wanting to stay within support from CW.
I also had a database die because I tried to export the auditing reports. Dead, server gone, toasted.
Insert Coin To Start Game. That server was allowing our support desk to service our clients. That was a fun couple of days - NOT! As I recall, support's response was to tell us to start again with a fresh install and not be stupid enough to try and export the auditing logs again.
I'm looking forward to hearing from Mike how querying the DB causing complete loss of the DB is not a DB engine issue. There seems to be a disconnect between how he thinks the DB engine behaves and what you, we, and SC support have seen in real life.
For the record - I'm not in favour of MS SQL due to license / limitations, and MySQL needs careful backup / restore handling due to lack of VSS support (as we found out on our LabTech DB) though I expect that is true of all / most of the open source options.
Max, I wish I had a suggestion for HA, but I don't have anything in my toolbox that I'd trust. Don't quote me on this - but I think I've seen it written that it's possible to change SC to another DB engine like PostgreSQL, but it's not supported. As you want HA, I'd assume you'll be wanting to stay within support from CW.
I also had a database die because I tried to export the auditing reports. Dead, server gone, toasted.
Insert Coin To Start Game. That server was allowing our support desk to service our clients. That was a fun couple of days - NOT! As I recall, support's response was to tell us to start again with a fresh install and not be stupid enough to try and export the auditing logs again.
I'm looking forward to hearing from Mike how querying the DB causing complete loss of the DB is not a DB engine issue. There seems to be a disconnect between how he thinks the DB engine behaves and what you, we, and SC support have seen in real life.
For the record - I'm not in favour of MS SQL due to license / limitations, and MySQL needs careful backup / restore handling due to lack of VSS support (as we found out on our LabTech DB) though I expect that is true of all / most of the open source options.
Alex, no worries. Yes there have been reports of successfully switching to MSSQL but as you and Sean noted, these are not supported configurations. The general response from support for any DB issue is to wipe and rebuild. I've been troubleshooting a memory leak issue and the FIRST suggestion was to dump the entire DB and start from scratch. Um.... how is that a solution, never mind the first suggestion.
I'm honestly in favor of any database technology that will get us the features we're looking for. I'm an Azure shop so being able to use Azure based SQL databases whether they be MSSQL or MySQL would be a game changer but I'm pretty much open to anything. As you noted, I believe most if not all open source database solutions are not VSS friendly, however MySQL also gives us options in the way of database HA such that we can take a database node down for backup every hour.
I'm curious to see if we can get some traction going on this. It's clearly something the community wants/needs and aside from the labor it will take to rewrite the necessary code, I see no drawbacks to a change in DB technology especially if the application defaults to SQLite and gives us the option of using a more resilient database engine.
Our solution to the DB issue was to every 2 hours copy the SQLite db and all Recording ( SC native ) to a S3 storage.
Then a secondary server transcodes all the videos and gather any relevant information from the SQLite DB into a mysql storage.
A interface for reporting, audit, searching ,viewing video sessions was then created.
This makes the DB on the SC server irrelevant as we max loose 2 hours of information on breakdown and we can can limit the amount of data stored in SQLite by only keeping a few days on information.
We handle about 10'000 - 15'000 videos a day in this way.
Obviously above should not be necessary but we had no faith in ConnectWise to create a similar solution as we seem to be one of the few that actually records ALL sessions.
Our solution to the DB issue was to every 2 hours copy the SQLite db and all Recording ( SC native ) to a S3 storage.
Then a secondary server transcodes all the videos and gather any relevant information from the SQLite DB into a mysql storage.
A interface for reporting, audit, searching ,viewing video sessions was then created.
This makes the DB on the SC server irrelevant as we max loose 2 hours of information on breakdown and we can can limit the amount of data stored in SQLite by only keeping a few days on information.
We handle about 10'000 - 15'000 videos a day in this way.
Obviously above should not be necessary but we had no faith in ConnectWise to create a similar solution as we seem to be one of the few that actually records ALL sessions.
Nikolaj, would it be possible for you to share a little more detail and possibly some code around how you're doing this? I'm very interested. We can leverage Azure SQL Databases, Azure Blob Storage, and Azure WebApps to accomplish the same but a starting point would be fantastic!
Lets all be honest here. ConnectWise is not going to fix this because they want you to use their cloud hosting which doesn't have this problem.
Lets all be honest here. ConnectWise is not going to fix this because they want you to use their cloud hosting which doesn't have this problem.
If that's true that their cloud hosting doesn't use SQLite I would be surprised.
If that's true that their cloud hosting doesn't use SQLite I would be surprised.
I mean, they claim they have customers with 30k endpoints and 200gb databases working just fine in their hosted cloud platform but the amount of I/O that would require even if on SSD would be insane. The best part is I've had multiple tickets open over the last few months to troubleshoot performance issues and they can't figure it out, they told me repeatedly "You need to have SC on Flash storage" and I moved it off of our Nimble to a full flash storage array and it made minimal difference and our DB is only 2.5gb
Otherwise their solution must be throw ridiculous amounts of money at it by buying/building an insane solution for their hosted platform to run on between compute, networking & storage. Or they have functions/features that exist up there that don't exist for the on premise product and aren't owning up to it.
When we had SC on our Nimble, out of the 30 VM's we have (including some that read/write into MSSQL DB's all day) ScreenConnect was our most I/O intensive VM by far.
I mean, they claim they have customers with 30k endpoints and 200gb databases working just fine in their hosted cloud platform but the amount of I/O that would require even if on SSD would be insane. The best part is I've had multiple tickets open over the last few months to troubleshoot performance issues and they can't figure it out, they told me repeatedly "You need to have SC on Flash storage" and I moved it off of our Nimble to a full flash storage array and it made minimal difference and our DB is only 2.5gb
Otherwise their solution must be throw ridiculous amounts of money at it by buying/building an insane solution for their hosted platform to run on between compute, networking & storage. Or they have functions/features that exist up there that don't exist for the on premise product and aren't owning up to it.
When we had SC on our Nimble, out of the 30 VM's we have (including some that read/write into MSSQL DB's all day) ScreenConnect was our most I/O intensive VM by far.
We are in a similar situation. We have about 15K endpoints and we've had to set the DB maintenance schedules to delete pretty much everything after two days otherwise we start feeling a lot of lag. We have an all flash Nimble but even tried moving the VM to a local PCIe SSD for testing. Made no difference.
We are in a similar situation. We have about 15K endpoints and we've had to set the DB maintenance schedules to delete pretty much everything after two days otherwise we start feeling a lot of lag. We have an all flash Nimble but even tried moving the VM to a local PCIe SSD for testing. Made no difference.
It's obvious that the limitations are with SQLite, and I just cannot believe them when they say they have instances with 200gb databases or with 30k endpoints and they run fine in their hosted platform on SQLite when we have so many people in this thread alone having the same issues and I've basically built out a brand new server with a new database and have the exact same issues we had before it was rebuilt and on flash storage.
I could go throw my SC instance on a PC that has a proc overclocked to 5ghz, and PCIe SSD's that push 6000mb/s read/write and I bet I'd still experience the SAME problems we have now.
Given the number of people that I've seen post around the internet and even those in this thread alone that have posted about having issues, it blows my mind that we have to resort to having basically no logging to have a functional product
And scrolling back up in this same thread we see various people talking about using unsupported methods of using MSSQL and the performance being way better than what the rest of us get with SQLite, the downside is when the product gets updated it generally breaks stuff.
We now have a thread here that's been open for 5-6 years, and because they are now owned by ConnectWi$e, I doubt they'll never add support for the on premise product to use MSSQL because ConnectWi$e wants to make money by getting people on the hosted platform.
When we piloted ScreenConnect several years ago, we worked with the SC team and installed using MS SQL Standard because of complications/limitations for our reporting and concurrent access needs with the built-in SQLite DB.
On two separate occasions, support has been utterly and completely useless in supporting the application because of the DB flavor. While I would not expect the support team to support the DB layer, I would still expect full support of the application.
Please either officially support MS SQL or provide reference architecture or whatever else is necessary so that it is a "supported configuration."
Given that we've been running it for going on 3 years, I wouldn't think much is necessary from a technical perspective.