I am in exactly the same boat you are in. In my experience, latency to the nearest Microsoft data center location is acceptable to move servers out and setup site-to-site VPN between your on-premises subnet and Azure subnet. 

However, I was not able to install Connection Manager on a server core - it was citing that it needs MDAC to work. I don’t believe this is supported. Though you probably will be able to make it work on a GUI instance, but that will require a costlier instance with maybe 8gb of RAM. Connection manager does not require a lot of cpu capacity. I would like to be able to run it on the server core though. 

Speaking of OneDrive - do not bet on that. Essentially, a shared Sage 50 file is a MySQL database exposed to a client app via their Connection Manager, having very little to do with a file on a share. And this is how you need to treat it. 

Speaking of migration - I would still keep some services on premises. First, your users will need DNS - increased latency between data center and local network will hinder their user experience. They also need print services. Good news - you can get away with just cheap Intel Atom or Intel Celeron J based cheap thing consuming 15w of power, and not keeping anything important locally  

Speaking of connection manager - you may consider to mount a drive living on an Azure server to your local server via iSCSI and expose that via Connection Manager to your users. I am planning to try this with my clients. 

Al Doman said:

Cloud server configured as an RDS server. All Sage components installed on the cloud server.

2. I would imagine most Sage 50 Canadian service providers are running it on hosted VMs for both the server and the client + MS-Office, and using something like RDP to only transmit screens, user input, and print jobs. 

Al Doman said:

This should work fine as long as full product installation for multi-user on an RDS server is supported - can someone confirm?

I believe that is a supported configuration, if you mean that in the sense that it will run.  

https://www.sage.com/ca/sage-50-accounting/lp/sysreqs

We've been doing it since 2009. 

Because it is client-server, it might run a little better if the host and the server are running on separate physical machines - otherwise the client and DB server might fight over disk, CPU, RAM, and bus - the client will be shovelling data into a temp file and formatting it while the DB is collecting bits and pieces from all over.  

If you are running third-party applications or using ODBC for reports, connecting reliably could be challenging.

Apologies for the delayed reply.

According to Sage Support Link the Connection Manager is installed on all computers, workstation or server. Is that basically a rebranded MySQL database engine that runs only on Windows? If so that's not a usual client-server architecture; normally only one database engine (on the server that hosts the shared database's files) would access those files and workstations/clients would all use ODBC or similar to connect to and make requests of that DB server. In that scenario there would be no need for a full MySQL DB server installation on the clients.

Regardless of the exact implementation, there must be a MySQL DB server instance running somewhere. If we are trying to get rid of on-premise servers then it would need to run on a cloud-based Windows virtual server. At first glance I think I'd prefer to have the data files local to the DB server instance. iSCSI etc (or OneDrive for that matter) look technically interesting but I'd be concerned about link reliability and whether the DB engine would complain about latencies.

In any case, thanks for the thought-provoking feedback.

Apologies for the delayed reply.

Thanks for the link confirming that Sage 50 is supported in an RDS environment. I think that's probably the cleanest way for us to do it (on a cloud-based virtual server), if we want to do away with any on-premise server.

Good point about hosted Sage 50 (vs Sage 50cloud), for due diligence we should check their pricing. Can you recommend any providers?

Thanks. Al

Al Doman said:

there must be a MySQL DB server instance running somewhere

There is, the Connection Manager starts it and... manages connections to it. 

Sage used to support a few specific Linux distros for the Connection Manager + RDBMS piece, it was dropped years ago.  Sage 50 is always client-server over TCP/IP, and the only supported server is Windows.

Al Doman said:

If we are trying to get rid of on-premise servers then it would need to run on a cloud-based Windows virtual server. At first glance I think I'd prefer to have the data files local to the DB server instance. iSCSI etc

That is the only supported configuration, as far as I know.  Some offices were running linux based NAS up to the last file-based version of 2007, but any SAN through a Windows server should work.

I can't, personally.   We talked to a few with the aim of simplifying connections to multiple offices, but never moved to offsite. 

I would stick to Canadian provider so as to avoid cross-border tax privacy complication, provided they meet the performance criteria.

Hmm, I'm still not 100% clear on the nuts and bolts.

AFAIK MySQL only allows one database server instance to open and have control of a database's files (i.e. exclusive access). If an instance on a server opens a database located on that server, another instance running on a client can't open those same data files.

So, what actually happens when a client goes to open a database/set of books stored on a server computer? Does Connection Manager not actually start a local MySQL instance, but instead revert to making ODBC queries to the MySQL instance on the server computer?

Come to think of it, how is the MySQL instance on the server computer "told" that it has "ownership" of a given set of books (i.e. database) on that server?

Al Doman said:

AFAIK MySQL only allows one database server instance to open and have control of a database's files (i.e. exclusive access). If an instance on a server opens a database located on that server, another instance running on a client can't open those same data files.

I don't work for Sage, so I can only see how the thing behaves, rather than how it's actually assembled. 

Right, there is exclusive access.  The raw DB file is never accessed across a network, only by a DB instance running on the server with the disk.

A process.pid text file is created in the .SAJ (data) folder when the first connection is made.  It contains the process ID of the MySQL database daemon.  If it isn't there, the Connection Manager knows there are no existing connections so it must first start an instance of the MySQL daemon.  One OS is in full control the disk, therefore no other copy of MySQL can access the data.  There is absolutely never a direct client read/write connection to the MySQL INNODB file (ibdata1).

Each workstation's default installation gets all the software - both client and server, but the workstation's MySQL daemon only starts and runs if accessing data on the local workstation's filesystem - even if there were no other computers in the world, the Sage 50 client would still communicate from the local client to the local server via specific TCP/IP ports.  

There are no 'local' connections via shared memory or direct to disk.  If the local MySQL daemon is disabled, for example, the client will be unable to open a local 'company file' but can access data on a server with no problems.

The Connection Manager literally manages the connections but it doesn't handle company data itself- it seems aware of all the existing connections (check File | Properties in multi-user) and the status of company data on the server, probably in a temporary table in the database itself, I can't say.

Whether the first connection or not, it will pass the an available port to the client on log-in (assuming the database isn't in 'single-user' mode, and there are licenses available, etc)

There is only one type of client connection to the INNODB data file via MySQL, all the data is fetched using ODBC queries against the MySQL DBMS instance.  The requests and data get to the right place because they are made from different IP addresses and on different ports.

I hope that helps, It could be better organized and maybe have a flowchart.   The important thing is that Sage 50 always, only, runs in client-server mode, never in a direct file-access mode.

Most helpful, thanks! I'm pretty sure that gives me what I need to be able to evaluate virtual/cloud-based platforms for running Sage.

I've marked your last post as the answer, I imagine it may also help some folks if they need to troubleshoot.

Thanks again. Al